I have a large set of data in one column (K), containing random values of 1, 2, 3, and 4. Within that, I would like to locate and highlight sets of rows that match the values from a much smaller column (G) of data.

I tried using Conditional Formatting but was only able to set rules for single rows of data, not matching groups.

Within one Column (K), I expect to locate and highlight all instances of the data I put into another Column (G) (example 1, 2, 1, 1, 2).

1 Answers

Courtney Krueger On Best Solutions

In order to do this, you need two helper columns.

A has the pattern you are looking for (=randbetween(1|4)).

B has the random numbers 1 through 4 (=randbetween(1|4)) but is a longer list.

The first helper column D has =IF(AND(B3=$A$2|B2=$A$1)|"match"|"no") which will put "match" on the last row of the pattern.

Then, helper column E has =IF(D3="match"|"match"|"no") to put a "match" in the cell if the one below it has it.

Then, in the conditional formatting for the column is =OR($D1="match"|$E1="match").

shows helper columns and conditional formatting

This can be expanded to a pattern of 3.

The first helper column K has =IF(AND(I2=$H$2|I1=$H$1|I3=$H$3)|"match"|"no").

The second helper column L has =IF(OR(K3="match"|K4="match")|"match"|"no").

The conditional formatting formula is the same, just transposed over a few rows.

shows helper columns and conditional formatting

If your pattern is a fixed size, this works great. If your pattern varies in length, you'll have to find a way to generalize these formulas.