{"id":7822,"date":"2014-12-16T12:18:53","date_gmt":"2014-12-16T19:18:53","guid":{"rendered":"http:\/\/macblaze.ca\/?p=7822"},"modified":"2014-12-15T13:31:30","modified_gmt":"2014-12-15T20:31:30","slug":"more-favorite-excel-formulas","status":"publish","type":"post","link":"https:\/\/macblaze.ca\/?p=7822","title":{"rendered":"More Favorite Excel Formulas"},"content":{"rendered":"

Making Headings<\/h4>\n

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\u2014think Table of Contents.<\/p>\n

Thus:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n
Chicken<\/td>\nRecipe 1<\/td>\n<\/tr>\n
Chicken<\/td>\nRecipe 2<\/td>\n<\/tr>\n
Chicken<\/td>\nRecipe 3<\/td>\n<\/tr>\n
Beef<\/td>\nRecipe 1<\/td>\n<\/tr>\n
Beef<\/td>\nRecipe 2<\/td>\n<\/tr>\n
<\/td>\n<\/td>\n<\/tr>\n
Seafood<\/td>\nRecipe 1<\/td>\n<\/tr>\n
Seafood<\/td>\nRecipe 2<\/td>\n<\/tr>\n
Seafood<\/td>\nRecipe 3<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

Should be:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n
Chicken<\/td>\nRecipe 1<\/td>\n<\/tr>\n
<\/td>\nRecipe 2<\/td>\n<\/tr>\n
<\/td>\nRecipe 3<\/td>\n<\/tr>\n
Beef<\/td>\nRecipe 1<\/td>\n<\/tr>\n
<\/td>\nRecipe 2<\/td>\n<\/tr>\n
<\/td>\n<\/td>\n<\/tr>\n
Seafood<\/td>\nRecipe 1<\/td>\n<\/tr>\n
<\/td>\nRecipe 2<\/td>\n<\/tr>\n
<\/td>\nRecipe 3<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Again the first thing you ned to do is visualize the problem and then express it logically. If the heading is the same as\u00a0the\u00a0heading 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.<\/p>\n

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.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
A<\/strong><\/td>\nB<\/strong><\/td>\nC<\/strong><\/td>\n<\/tr>\n
Chicken<\/td>\n<\/td>\nRecipe 1<\/td>\n<\/tr>\n
Chicken<\/td>\n<\/td>\nRecipe 2<\/td>\n<\/tr>\n
Chicken<\/td>\n<\/td>\nRecipe 3<\/td>\n<\/tr>\n
Beef<\/td>\n<\/td>\nRecipe 1<\/td>\n<\/tr>\n
Beef<\/td>\n<\/td>\nRecipe 2<\/td>\n<\/tr>\n
<\/td>\n<\/tr>\n
Seafood<\/td>\n<\/td>\nRecipe 1<\/td>\n<\/tr>\n
Seafood<\/td>\n<\/td>\nRecipe 2<\/td>\n<\/tr>\n
Seafood<\/td>\n<\/td>\nRecipe 3<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Then you fill column B with the following formula:<\/p>\n

=IF(A2<\/span>=””,””,(<\/span>IF(<\/span>A2<\/span>=A1<\/span>,””,A2<\/span>)<\/span>)<\/span>)<\/p>\n

This is constructed from\u00a0basic if statements<\/em>. 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 \u00a0IF(logical test, is true then do this…, else do this…) So this =IF(a=b,”A”,”B”) simply put, is \u00a0read 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.<\/p>\n

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.<\/p>\n

=IF(A2=””, “”,\u00a0next if statement)<\/p>\n

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.<\/p>\n

=IF(A2=A1,””,A2)<\/p>\n

All together:<\/p>\n

=IF(A2<\/span>=””,””,(<\/span>IF(<\/span>A2<\/span>=A1<\/span>,””,A2<\/span>)<\/span>)<\/span>)<\/p>\n

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.<\/p>\n

One thing to remember about this sort of thing is the calculations are all related to the original list. If you delete column A\u2014which you will likely have to if you are going to export it to a text file\u2014then 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\u2014perfect for exporting!<\/p>\n

But I need to get rid of Headers<\/h4>\n

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!<\/p>\n

Turning:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
apple<\/td>\nRecipe 1<\/td>\n<\/tr>\n
<\/td>\nRecipe 2<\/td>\n<\/tr>\n
<\/td>\nRecipe 3<\/td>\n<\/tr>\n
<\/td>\nRecipe 4<\/td>\n<\/tr>\n
oranges<\/td>\nRecipe 1<\/td>\n<\/tr>\n
<\/td>\nRecipe 2<\/td>\n<\/tr>\n
<\/td>\nRecipe 3<\/td>\n<\/tr>\n
bananas<\/td>\nRecipe 1<\/td>\n<\/tr>\n
<\/td>\nRecipe 2<\/td>\n<\/tr>\n
<\/td>\nRecipe 3<\/td>\n<\/tr>\n
<\/td>\nRecipe 4<\/td>\n<\/tr>\n
<\/td>\nRecipe 5<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Into:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
apple<\/td>\nRecipe 1<\/td>\n<\/tr>\n
apple<\/td>\nRecipe 2<\/td>\n<\/tr>\n
apple<\/td>\nRecipe 3<\/td>\n<\/tr>\n
apple<\/td>\nRecipe 4<\/td>\n<\/tr>\n
oranges<\/td>\nRecipe 1<\/td>\n<\/tr>\n
oranges<\/td>\nRecipe 2<\/td>\n<\/tr>\n
oranges<\/td>\nRecipe 3<\/td>\n<\/tr>\n
bananas<\/td>\nRecipe 1<\/td>\n<\/tr>\n
bananas<\/td>\nRecipe 2<\/td>\n<\/tr>\n
bananas<\/td>\nRecipe 3<\/td>\n<\/tr>\n
bananas<\/td>\nRecipe 4<\/td>\n<\/tr>\n
bananas<\/td>\nRecipe 5<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

You begin by once again adding in a new column for the calculation and then filling it with the following:<\/p>\n

=(IF(<\/span>A2<\/span>=””,B1<\/span>,A2<\/span>)<\/span>)<\/p>\n\n\n\n\n\n\n\n\n
<\/td>\nA<\/strong><\/td>\nB<\/strong><\/td>\nC<\/strong><\/td>\n<\/tr>\n
1<\/strong><\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/tr>\n
2<\/strong><\/td>\napple<\/td>\n=(IF(A2=””,B1,A2)<\/td>\nRecipe 1<\/td>\n<\/tr>\n
3<\/strong><\/td>\n<\/td>\n=(IF(A3=””,B2,A3)<\/td>\nRecipe\u00a02<\/td>\n<\/tr>\n
4<\/strong><\/td>\n<\/td>\n=(IF(A4=””,B3,A4)<\/td>\nRecipe\u00a03<\/td>\n<\/tr>\n
5<\/strong><\/td>\n<\/td>\n=IF(A5=””,”B4,A4)<\/td>\nRecipe\u00a04<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

In order to make this work you need to have a blank row above your first row so \u00a0the calc can always reference the row above. So this calculation says if the row to the left is empty, then make\u00a0this 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.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false},"categories":[1],"tags":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/macblaze.ca\/index.php?rest_route=\/wp\/v2\/posts\/7822"}],"collection":[{"href":"https:\/\/macblaze.ca\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/macblaze.ca\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/macblaze.ca\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/macblaze.ca\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=7822"}],"version-history":[{"count":0,"href":"https:\/\/macblaze.ca\/index.php?rest_route=\/wp\/v2\/posts\/7822\/revisions"}],"wp:attachment":[{"href":"https:\/\/macblaze.ca\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=7822"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/macblaze.ca\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=7822"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/macblaze.ca\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=7822"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}