Colouring batches of identical cells

[kalabe]

I have a table of data. For ease, say there are four columns, titled division (e.g. Africa, Europe), country, region (north/south), and city. The data is all sorted from column to column. I would like, in each column, to have any of the same batch of data one colour and the next batch a different colour. The effect is similar to the alternate row effect that word or excel can apply to a table automatically but instead I want it done by batches of the same data and for each column to be done separately.

I hope this is semi-understandable. For some reason I'm told I can't post attachments, perhaps because I hardly ever post?

Does anyone have any ideas please?

 

[Eric W]

You can use Conditional Formatting. Let's say your table looks like:

	B	C	D	E
Division	Country	Region	City
Africa	Egypt	North	Cairo
Africa	Egypt	North	Thebes
Europe	England	North	Leeds
Europe	England	North	London
Europe	England	South	Bath
Europe	France	North	Paris
Europe	France	South	Cannes

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet5​

and your batches are defined when the Division, Country, and Region are all the same. Then you can select columns B:E, click Conditional Formatting > New Rule > Use a formula > and enter

=MOD(SUM(SIGN(FREQUENCY(MATCH($B$1:$B1&"|"&$C$1:$C1&"|"&$D$1:$D1,$B$1:$B1&"|"&$C$1:$C1&"|"&$D$1:$D1,0),ROW($D$1:$D1)-ROW($D$1)+1))),2)*(B1<>"")

Click Format... and choose a fill color. In this example, rows 4 5 and 7 will be highlighted.

 

[kalabe]

Eric, thanks for replying but I've not explained very well what I'm trying to achieve. To use your example, in column B, I would want Europe filled with grey, and Africa white. But in column C, I would want Egypt grey, England white, France grey. In other words each column is separately formatted so that the change of data in that particular column prompts a change in fill. But many thanks for having a look. It's not urgent so when I get home later I'll see if I'm able to attach an example from my home pc as I think I'm still not explaining myself well. Thanks again.

 

[Eric W]

That requires a somewhat easier rule. Select B2:D9999 (or however far down you want), click Conditional Formatting > New Rule > Use a formula > and enter:

=(1-MOD(SUMPRODUCT(--(B$1:B1<>B$2:B2)),2))*(B2<>"")

 

[kalabe]

Thanks Eric, works fine - appreciate you taking the time

 

[Eric W]

Glad it works for you.