Excel - How can I locate a pattern(s) of matching rows within a large set of numerical data?

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`).

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")`.

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.

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.