Formatting rows by groups in Excel

Wearb

I am recording the names on all the headstones in a graveyard and recording the details of each person on the headstone in a row. I would like to know if there is any way I can automatically group each grave (some will have multiple names and therefore multiple rows) by colour. I do realise that if I had put more thought into naming my images, I would have made it easier to do this. See image for manual formatting.

Yakuza

You could write a VBA macro to loop through each row, and if the value of the last number were odd, (using say MOD(VALUE(MID(A1,5,10)),2)), then set the background colour of that row to that light brown, otherwise set it to cream.

That's the gist of it. Excel 2007 is what I'm using, it seems only to allow you to format cells rather than rows by a value in that cell. 2010 may be more sophisticated. If it isn't, then I think a macro is your best bet.

Wearb

Thanks for getting back to me. I have excel 2003. I do not know how to write VBA files, but I will play around with the info you posted and see if I can get something to work

MathsManiac

You could try "conditional formatting". Search the help documentation for conditional formatting and see if that might provide an answer for you.

darjeeling

Here's a way to do it without using VBA.

For this, I'll assume you have columns A to D, with surnames in column D, and that the data is sorted by surname.

In column E, then, put a 1 in the first data row (row 2). In the row below (row 3), type this formula: =IF(D3<>D2,-1*E2,E2)
Select and copy the cell with the formula, then paste to all empty cells in column E until you reach the end of your data.

Click on column E and turn on data filtering: in the menu bar, click Data -> Filter -> Autofilter (or Data -> filter in Excel 2007).

Click the arrow on the top of column E, and use the check boxes to select only rows containing either 1 or -1. Select the rows and colour them. NB - I can't remember if in Excel 2003 you need specifically to select just the visible rows, but you can do this by typing Ctrl G, clicking 'Special', then selecting 'Visible cells only' and clicking OK.

.

MathsManiac

Here's a variant on Darjeeling's idea, using conditional formatting:


First do this bit as described by Darjeeling:

In column E, then, put a 1 in the first data row (row 2). In the row below (row 3), type this formula: =IF(D3<>D2,-1*E2,E2)
Select and copy the cell with the formula, then paste to all empty cells in column E until you reach the end of your data.

(Note, by the way, that you can "fill down" rather than copy and paste that.)

Then, highlight the entire worksheet by clicking the square in the top left corner (i.e., to the left of "A" and above "1"). Apply conditional formatting to the cells using the following formula, and the formatting of your choice.
=($E1=1)

I can't quite recall how you enter the details for conditional formatting in Excel 2003, but it's under the "format" menu, and you want to select the option of basing the formatting on a formula.

Once it's all working, you can hide column E.

darjeeling

The ways I and MathsManiac have been using can be used for lots of different data. However, in the present case there's a shortcut way, using conditional formatting as used by MathsManiac .

If all the grave names (in column A) are numbered sequentially as in the OP's image, then a very quick way to colour every alternate grave is to select the block of data (click cell A2, then drag / use 'Ctrl Shift 8' or Ctrl Shift & cursors), go to conditional formatting and use a formula as a rule.
The formula could be:
=ISODD(RIGHT($A2,1))
Select a colour for these rows, click OK and you're done!

Wearb

I think messages #5 and #6 assume the surname on each headstone to be the same. in fact some of them have lots of different surnames on them. The only unique data to each headstone is in column A.

I haven't managed to understand message #7 yet. I will play around more with it this evening.

Thank you all for your help.

darjeeling

Wearb wrote: »

I think messages #5 and #6 assume the surname on each headstone to be the same. in fact some of them have lots of different surnames on them. The only unique data to each headstone is in column A.

I haven't managed to understand message #7 yet. I will play around more with it this evening.

Thank you all for your help.

The methods described in posts 5 and 6 will work equally well when applied to headstone IDs, if that's what you're choosing to group the data by.

In your case you're using column A, so the formula in posts 5 and 6 would become: =IF(A3<>A2,-1*E2,E2)

This is assuming you're using column E for generating a list of '1's and '-1's for flagging each new headstone. If you're doing this in, say, column F, then the formula becomes:
=IF(A3<>A2,-1*F2,F2)

Another way is to extract a unique (i.e. non-redundant) list of headstone IDs using a pivot table, the advanced filter, or the 'remove duplicates' tool (not sure if that's in Excel 2003). Apply some kind of coding in an adjacent column to your non-redundant list (e.g. alternating 1s and -1s) and then use the vlookup function to apply this coding to your original data, then use filtering or conditional formatting to colour the data rows.

Wearb

Brilliant. Thanks a lot. I have managed to get it to work. I have another problem that I didn't foresee. Some of the graves had a second marker that I named the same as the main marker, but with a letter at the end. -see attachment-. Is there a handy way around this. I can manually put the letters before the last set of numbers and run the formula and then go back and put the letters back at the end, but it would be easier to do it automatically.

darjeeling

Wearb wrote: »

Brilliant. Thanks a lot. I have managed to get it to work. I have another problem that I didn't foresee. Some of the graves had a second marker that I named the same as the main marker, but with a letter at the end. -see attachment-. Is there a handy way around this. I can manually put the letters before the last set of numbers and run the formula and then go back and put the letters back at the end, but it would be easier to do it automatically.

Now the plot thickens! We need to find a way to trim off the qualifying letter from the end, then use the trimmed headstone IDs for grouping and colouring the rows.

This can be done by using another formula to determine if the last character in the headstone ID is a letter, returning the trimmed ID if it is or the full ID if it isn't.

A workable formula would (for the 2nd row) be:
=IF(ISNUMBER(ABS(RIGHT(A2,1))),A2,LEFT(A2,LEN(A2)-1))

[NB - this only works if you've just used a single letter to sub-divide your headstone IDs]

Put this formula into the 2nd row of a column to the right of your data, then fill down to the end of the data. Use this new column for your grouping, and it should work.

Wearb

Thanks a lot darjeeling and everyone who helped. I now have a well organised worksheet, and a LITTLE more knowledge.