More Favorite Excel Formulas
Making Headings
Quite often I would have columns of headings but when I went to export the headings, I would only want the first instance to appear in the text I was using. For example if I was doing a list of recipes and they were divided up into Chicken, Beef and Seafood, I would like the heading “Chicken” to appear only before the first entry—think Table of Contents.
Thus:
| Chicken | Recipe 1 |
| Chicken | Recipe 2 |
| Chicken | Recipe 3 |
| Beef | Recipe 1 |
| Beef | Recipe 2 |
| Seafood | Recipe 1 |
| Seafood | Recipe 2 |
| Seafood | Recipe 3 |
Should be:
| Chicken | Recipe 1 |
| Recipe 2 | |
| Recipe 3 | |
| Beef | Recipe 1 |
| Recipe 2 | |
| Seafood | Recipe 1 |
| Recipe 2 | |
| Recipe 3 |
Again the first thing you ned to do is visualize the problem and then express it logically. If the heading is the same as the heading above then don’t show it. If it is not the same then do show it. I also threw in a bit to accommodate the space between Beef and Seafood in case it was necessary.
The first thing you need to do is insert a new column between the headings (column A) and the recipes (column B). Actually you can make the new column for the calculation anywhere you want but it is much easier to keep alongside the original.
| A | B | C |
| Chicken | Recipe 1 | |
| Chicken | Recipe 2 | |
| Chicken | Recipe 3 | |
| Beef | Recipe 1 | |
| Beef | Recipe 2 | |
| Seafood | Recipe 1 | |
| Seafood | Recipe 2 | |
| Seafood | Recipe 3 |
Then you fill column B with the following formula:
=IF(A2=””,””,(IF(A2=A1,””,A2)))
This is constructed from basic if statements. You will use them a lot if you spend any amount of time working with formulas and calculations. The syntax for an if statement is IF(logical test, is true then do this…, else do this…) So this =IF(a=b,”A”,”B”) simply put, is read as if a is equal to b then write A, if not then write B. My formula above uses two nested if statements, one that checks to see if the current row is blank or not and one that determines whether it is the same as the row above.
Is the row empty? If so then leave it blank; if not then go on to the rest of the calculation. Empty (blank) is indicated by two quotes with nothing (not even a space) between them.
=IF(A2=””, “”, next if statement)
The next if statement then decided if the current cell matches the one above or not. If it does, if leaves it blank. If it doesn’t then it put in the text from the current cell.
=IF(A2=A1,””,A2)
All together:
=IF(A2=””,””,(IF(A2=A1,””,A2)))
If cell A2 is blank then leave blank space, if not then see if A2 is equal to the cell above (A1). If it is, then again leave a blank space. If not, then write in the contents of A2.
One thing to remember about this sort of thing is the calculations are all related to the original list. If you delete column A—which you will likely have to if you are going to export it to a text file—then the text in your column B will also disappear. The easiest away around this is, just before you delete the column, you copy column C and then paste it right back using Edit: Paste Special, then select Text. This will paste not the column of formulae you cut, but the results in text form—perfect for exporting!
But I need to get rid of Headers
Just as often I would find that to manipulate the text, it would be easier to not have these headers, but rather the filled in columns (think sorting by header) so how do you do this the other way? Well, there’s a formula for that!
Turning:
| apple | Recipe 1 |
| Recipe 2 | |
| Recipe 3 | |
| Recipe 4 | |
| oranges | Recipe 1 |
| Recipe 2 | |
| Recipe 3 | |
| bananas | Recipe 1 |
| Recipe 2 | |
| Recipe 3 | |
| Recipe 4 | |
| Recipe 5 |
Into:
| apple | Recipe 1 |
| apple | Recipe 2 |
| apple | Recipe 3 |
| apple | Recipe 4 |
| oranges | Recipe 1 |
| oranges | Recipe 2 |
| oranges | Recipe 3 |
| bananas | Recipe 1 |
| bananas | Recipe 2 |
| bananas | Recipe 3 |
| bananas | Recipe 4 |
| bananas | Recipe 5 |
You begin by once again adding in a new column for the calculation and then filling it with the following:
=(IF(A2=””,B1,A2))
| A | B | C | |
| 1 | |||
| 2 | apple | =(IF(A2=””,B1,A2) | Recipe 1 |
| 3 | =(IF(A3=””,B2,A3) | Recipe 2 | |
| 4 | =(IF(A4=””,B3,A4) | Recipe 3 | |
| 5 | =IF(A5=””,”B4,A4) | Recipe 4 |
In order to make this work you need to have a blank row above your first row so the calc can always reference the row above. So this calculation says if the row to the left is empty, then make this cell the same as the one above it. If it isn’t empty then make this cell the same as the one to the left. Again remember that to do any sorting or manipulation you first need to change column C from a set of calculations to actual text data (by Paste Special) otherwise changing the order will just make it recalculate based on the new data in the column to the left.