Automating your frequently used lists

​Do you frequently use a list of items that you have to type in every now and then? For example, a list of your department staff names in a certain order, where every time you need to either look them up in another sheet here or there to copy them from, or more painstakingly type them in one by one; or a list of departments names in your organization sorted in a certain way.

A common list used by almost every Excel user is the months (Jan, Feb...etc.). Have you ever wondered how MS Excel is smart enough to complete that list for you?

If you happen to be doing such repetitive tasks every now and then, this post provides you with the simple solution.

In Excel, you can create custom lists that you use frequently as follows:

1. Go to File --> Options --> Advanced --> Edit Custom Lists (Under General)

You should end up with the following dialogue box:


You will notice that there are preexisting lists for days and months, which explains how Excel is able to complete a list automatically.

2. Make sure that the selection under Custom lists is NEW LIST, then under List entries you can start entering the elements of your list separated by commas, then press Add.

If you have already entered the list directly in an open sheet, you may import the list entries directly from there without the need to re-type them. Under Import list from cells, select the cells that contain the list elements then press Import. You will notice that a new list has been created (with the name of the first element in the selected list) with all list entries as per your selection.

Press OK.

3. Try the new list by entering the first entry in an empty cell then drag the cell down. 
 
Voila! No more need to repeat the list! Excel should do it for you!

If you like this post, please do not hesitate to share!



on No comments:

Conditional MIN/MAX

​
Most of Excel users deal with conditional sum (SUMIF/S) and conditional count (COUNTIF/S), but did you know that you can also use conditional min/max?

For example, you have an employee database and you want to figure out the salary ranges per level (min/max).

For illustrative purposes, we will use the following hypothetical database*:


A  B   C 
1 Employee Name  Title   Salary 
2 Kyra Martino Engineer 2                     650
3 Leonor Bernardini Engineer 2                     650
4 Doris Steed Engineer 2                     650
5 Tameka Lichty  Technician 2                      550
6 Dawn Studstill  Technician 2                      550
7 Brittany Cauley  Technician 2                      580
8 Brad Lo Engineer 2                     650
9 Glen Laberge  Technician 2                      350
10 Vernetta Castiglione Engineer 2                     685
11 Marylouise Brookover Engineer 2                     685
12 Tammi Davilla Engineer 2                     685
13 Oretha Henrich  Technician 2                      585
14 Alaina Sattler Engineer 2                     690
15 Mya Ewell Engineer 2                     690
16 Albina Wimbush  Senior Supervisor                  1,115
17 Letha Korte Engineer 2                     695
18 Byron Fetty  Technician 2                      350
19 Helene Gutierres Engineer 2                     695
20 Keturah Koopman  Technician 2                      700
21 Neida Mcclaran  Technician 2                      705
22 Kylie Reineke  Technician 2                      345
23 Laine Schlagel  Technician 2                      345
24 Kathlyn Ramey  Technician 2                      600
25 Pandora Sautter Engineer 2                     725
26 Karena Strang  Technician 2                      833
27 Melonie Frankel  Technician 2                      835
28 Tresa Renfroe  Technician 2                      880
29 Mitsuko Midyett Engineer 2                     740
30 Arletta Dauenhauer  Technician 2                      880
31 Lael Powe  Technician 2                      880
32 Oren Salem  Technician 2                      880
33 Meridith Burrill  Technician 2                      880
34 Yuko Mikus  Senior Technician                      980
35 Jamison Mealey  Technician 2                      880
36 Kasie Bartlett  Technician 2                      885
37 Jina Fillers  Technician 2                      350
38 Vida Bran  Technician 2                      725
39 Ronda Rothenberger  Technician 2                      750
40 Kory Hambleton  Senior Engineer                      790
41 Lachelle Neifert  Technician 2                      885
42 Clement Julien  Technician 2                      895
43 Agueda Trantham  Senior Engineer                      800
44 Janie Veitch  Engineer 1                      735
45 Edyth Kriger  Technician 2                      800
46 Lashell Kaczor  Engineer 1                      745
47 Flora Hooser  Technician 2                      800
48 Myra Bera  Technician 2                      800
49 Nicolasa Forand  Technician 2                      800
50 Eleanora Cantero  Engineer 1                      760
51 Violeta Ackerman  Technician 1                      905
52 Essie Theriot    Technician 1                      930
53 Clementine Casanova    Technician 2                      760
54 Bethel Hadsell    Technician 2                      350
55 Ambrose Ryce    Technician 2                      345
56 Merlin Rossbach    Technician 1                      935
57 Jeanne Hasty    Technician 2                      755
58 Dione Steinhauser    Technician 2                      350
59 Clay Bury    Technician 1                      965
60 Tasia Schumann    Technician 2                      350
61 Marianela Sharlow    Technician 2                      350
62 Yetta Starcher    Technician 2                      750
63 Sherly Brownfield    Technician 2                      810
64 Awilda Vanzile    Senior Engineer                      820
65 Luciana Levert    Senior Engineer                      845
66 Fairy Flippin    Senior Engineer                      850
67 Nicky Mallard    Senior Engineer                      855
68 Tamela Samson    Senior Engineer                      870
69 Otelia Wene    Senior Engineer                      865
70 Jannie Henze    Senior Engineer                      870
* All names and other information in the shown table are imaginary and do not intend to represent or refer to actual individuals. If any of the names happens to be the name of a real person, it is pure coincidence.
Tools used: MIN, MAX, IF, Arrays, VLOOKUP, and Advanced Filter.

Steps:

1. Working on column D in the example, you need to instruct Excel to return the minimum salary per Title. So the argument should say: If the title is X then search through salaries where the title is X and return the lowest salary. To translate into a formula:

MIN(IF(D:D=D2,C:C)

2. However, this formula works only as an Array. (For more information about Arrays, please refer to a previous post on Excelarium named "Working with Arrays:.

To convert the formula into an Array, once you type the formula, press CTRL+SHIFT+ENTER instead of ENTER. You will notice to more parentheses indicating that it has become an Array:

{MIN(IF(D:D=D2,C:C)}

3. Drag the formula to the end of the table on column E.

Note: You may notice a slow down in the processing of operations on the sheet; this is normal since Arrays consume a lot of processing power. So you need to be patient while Array results are being calculated or refreshed.

4. For max. salary, apply the same steps but replace the MIN function with MAX as follows:

MAX(IF(D:D=D2,C:C)

Then press CTRL+SHIFT+ENTER.

5. Now you have 2 lists on columns E and F showing salary ranges per title, however, for presentation purposes they'd better be summarized in one table to arrive at one line per title.

Go to Data --> Sort & Filter --> Advanced


Select Copy to another location

List range: Select the Title column (B1:B70).
Cirteria range: Leave empty
Copy to: Select an empty cell away from the original table
Select Unique records only.
Click OK.

You should have the following table:

G
 Title 
Engineer 2
 Technician 2 
 Senior Supervisor 
 Senior Technician 
 Senior Engineer 
 Engineer 1 
 Technician 1 

6. Next to the new column, use VLOOKUP to get the minimum salary that was arrived at under column D:

=VLOOKUP(G2,B:D,3,FALSE)

You should get the following result:


G H
1  Title   Min 
2 Engineer 2                         650
3  Technician 2                          345
4  Senior Supervisor                      1,115
5  Senior Technician                          980
6  Senior Engineer                          790
7  Engineer 1                          735
8  Technician 1                          905

You should do the same for max. salary, this time returning the values in the Max. column in the VLOOKUP. The result should look as follows:


G H I
1  Title   Min   Max 
2 Engineer 2                         650                         740
3  Technician 2                          345                         895
4  Senior Supervisor                      1,115                     1,115
5  Senior Technician                          980                         980
6  Senior Engineer                          790                         870
7  Engineer 1                          735                         760
8  Technician 1                          905                         965

Voila! You have done a summary of the salary scale, effectively utilizing conditional MIN/MAX.

If you like this post, please do not hesitate to share!
on No comments:
Subscribe to: Posts (Atom)