I have a alphabetized dataset of:
D47058V212B 9/23/2024 20:56
D47058V214 9/23/2024 20:56
D47058V29 9/23/2024 20:57
D47065V24 9/23/2024 23:02
D47089V215A 9/23/2024 23:37
D47091V214 9/23/2024 23:40
D4712426 9/24/2024 0:29
D47138V28 9/23/2024 22:52
D5485911 9/24/2024 3:39
I’d like to have each group by the first 6 matching characters. So all the values that start with D47089 are blue highlighted. all the D47091 are green highlighted ect. Having it rotate between 3 different colors all the way down the column. (colors aren’t important, just that they rotate for easy reading).
Is there a way to have excel do this automatically without me having to create separate conditional rules for each possible iteration of the first 6 characters?
You will need 3 rules.
=MOD(MATCH(LEFT($A1,6),UNIQUE(LEFT($A$1:$A$9,6)),0)-1,3)+1=1
=MOD(MATCH(LEFT($A1,6),UNIQUE(LEFT($A$1:$A$9,6)),0)-1,3)+1=2
=MOD(MATCH(LEFT($A1,6),UNIQUE(LEFT($A$1:$A$9,6)),0)-1,3)+1=3
Then associate the color desired to each:
Basic logic is the same as Scot’s, but using a named formula:
-
To accommodate future entries easily, I would recommend inserting table for the data
-
In Table tab clear “Banded Rows” (and other options if needed)
-
With a named formula you need to modify it only in one place – so create a named formula, for example,
colIndexwith=LAMBDA(a, MOD(XMATCH(LEFT(a,6), UNIQUE(LEFT(Table1[Column1],6))), 3)) -
Select all data rows in the table with top left cell being the active cell, add conditional formatting as shown below (with your colors 🙂
-
Formula for formatting is
=colIndex($A2)=0