Highlighting duplicate content of multiple rows across a set number of columns+rows

[AssistanceIsFutile]

I'm sorry if this question becomes wordy, however I'm having trouble identifying a way to communicate the problem I am trying to solve.

I am trying to find a way to highlight duplicate contents within a range of cells, however the "duplicate" condition depends on the results of three consecutive cells independent of the column. For illustrative purposes, here is an example table showing how my sheet looks (I'm sorry, I cannot install external applications at work so I hope this is sufficient).

Ex 1:

	A	B	C
1	A-I	F-I
2	A-II	F-II
3	A-III
4	A-I	B-I	C-I
5	A-II	B-II	C-II
6	A-III	B-III	C-III
7	D-I	E-I	F-I
8	D-II	E-II	F-II
9	D-III	E-III	F-III

<tbody>
</tbody>

What you see in this table is two things, an area where data will be entered (A1:C3) and a pool of data (A4:C9). What I'm trying to do is create a formula that checks the pool of data against the entered data and highlights the cells in the pool if they match exactly. As you see in my example, A4:A6 matches A1:A3 so it is highlighted, but C7:C8 is not highlighted because it doesn't completely match B1:B3 (B3 is not yet entered).

The next two example tables shows progression of the sheet and how the conditional formatting should respond.

Ex 2 (although B1, B2, and C3 have exact matches, they are in different columns thus the individual matching cells do not get highlighted):

	A	B	C
1	A-I	E-I
2	A-II	E-II
3	A-III	F-III	E-III
4	A-I	B-I	C-I
5	A-II	B-II	C-II
6	A-III	B-III	C-III
7	D-I	E-I	F-I
8	D-II	E-II	F-II
9	D-III	E-III	F-III

<tbody>
</tbody>

Ex 3 (C1:C3 have an exact match with B7:B9, so the duplicates are highlighted):

	A	B	C
1	A-I	E-I	E-I
2	A-II	E-II	E-II
3	A-III	F-III	E-III
4	A-I	B-I	C-I
5	A-II	B-II	C-II
6	A-III	B-III	C-III
7	D-I	E-I	F-I
8	D-II	E-II	F-II
9	D-III	E-III	F-III

<tbody>
</tbody>

The closest I've come with my tinkering is the conditional formatting formula "=COUNTIF($A$4:$C$9;A1:A3)>1" ... however this is insufficient because it highlights the individual duplicate cells, thus in example 2 all matches would be highlighted.

I hope my explanation and examples are clear enough. Any advice would be appreciated.

Edit: I'm using Excel 2010

 

[Jerry Sullivan]

Hi and Welcome to the Board,

Since Conditional Formatting is volatile, consider using some helper cells to concatenate the combinations of 3 cells.

There might be a better way, but here is one combination of helper cells and CF formula that seems to work.

Paste the formula in E1 and copy across. Paste the formula in E4 and copy to range E4:G7.

Excel Workbook
	A	B	C	D	E	F	G
1	A-I	F-I			A-I|A-II|A-III	F-I|F-II|	||
2	A-II	F-II
3	A-III
4	A-I	B-I	C-I		A-I|A-II|A-III	B-I|B-II|B-III	C-I|C-II|C-III
5	A-II	B-II	C-II		A-II|A-III|D-I	B-II|B-III|E-I	C-II|C-III|F-I
6	A-III	B-III	C-III		A-III|D-I|D-II	B-III|E-I|E-II	C-III|F-I|F-II
7	D-I	E-I	F-I		D-I|D-II|D-III	E-I|E-II|E-III	F-I|F-II|F-III
8	D-II	E-II	F-II
9	D-III	E-III	F-III
Sheet

Using these helper cells, try the CF formula:
=SUM(IF(E2:E4=$E$1:$G$1,1,0))
Applies to: =$A$4:$C$9

 

[AssistanceIsFutile]

Thank you for this response, it's a solution for what I'm looking for. However, I cannot seem to use the recommendation for a larger pool of data (or larger empty cells for Data Entry). Please see below:

Excel Workbook
	A	B	C	D	E	F	G
1	Data Entry				Helper Cells for Data Entry
2	A-I	D-I			A-I|A-II|A-III	D-I|D-II|D-III	||
3	A-II	D-II
4	A-III	D-III
5	B-I				B-I|B-II|B-III	||	||
6	B-II
7	B-III
8	C-I				C-I|C-II|C-III	||	||
9	C-II
10	C-III
11	Data Pool				Helper Cells for Data Pool
12	A-I	B-I	C-I		A-I|A-II|A-III	B-I|B-II|B-III	C-I|C-II|C-III
13	A-II	B-II	C-II
14	A-III	B-III	C-III
15	D-I	E-I	F-I		D-I|D-II|D-III	E-I|E-II|E-III	F-I|F-II|F-III
16	D-II	E-II	F-II
17	D-III	E-III	F-III
18	G-I	H-I	I-I		G-I|G-II|G-III	H-I|H-II|H-III	I-I|I-II|F-III
19	G-II	H-II	I-II
20	G-III	H-III	F-III
Sheet1

I tried with the following CF formula:
=SUM(IF(E3:E5=$E$2:$G$2;1;0))
Applies to: =$A$12:$C$20

You can see that the table is all sorts of messed up. A12:A14 is not highlighted despite matching A2:A4. B12:B14 and C12:C14 are highlighted correctly, however B15:B17, C15:C17, and strangely A19:A20 are highlighting despite the intended conditions not being met.

I assume it's in the conditions, perhaps perhaps I'm screwing up the formula when trying to adapt it to my actual sheet. Would you be able to advise a little bit about what your recommended formula is looking at? Hopefully this will help me understand how to apply it to my sheet of data.

Thanks for your help.

 

[Jerry Sullivan]

I'm not sure I understand how the larger (9 row) Data Entry range is to be used.

My suggestion based on the Original Post took this approach (cell references to image in Post #2):
List every possible combination of 3 stacked Data Entries in a helper row list.
Only 3 cells are needed (E1:G1) to hold all the combinations for Data Entry Range A1:C3.

For the Data Pool, list every possible combination of 3 stacked Data Pool items in a 3 column by n Row table.
The combinations are formed using the same formula as (E1:G1) but they are only copied from Rows 4:7.
There is no need to copy to Rows 8:9, as these would just concatenate combinations of 2 and 1 item.
The formulas shouldn't be copied to Rows 2:3 because this would start to mix combinations of items from Data Entry and Data Pool ranges.

Once these helper values are in place, the Conditional Formatting Formula takes this approach:
For any cell "Me" in the Data Pool range, the cell should be highlighted if a match is found in (E1:G1) for one of these combinations:

Me & two cells below Me
cell above Me & Me & cell below me
two cells above Me & Me

Due to the placement of the helper cells, those 3 lookup combinations are located in the same relation to the cell "Me".
For cell "Me" = B6 the CF formula checks to see if any of the combinations in F4:F6 are found in E1:G1
For cell "Me" = A4 the CF formula checks to see if any of the combinations in E2:E4 are found in E1:G1, for values near the top and bottom of the Data Pool, there are less than 3 look up combinations. To keep the formula simple though, it still uses a 1 column by 3 row lookup, but some of those combinations cells are completely blank.

I believe the same concept could be applied to your larger data set. The increased number of rows in the Data Pool has no effect.
What isn't clear to me about the Data Entry Range is whether it represents 3 sets of 3-row combinations, or could any 3 row combinations be matched...
(cell references to image in Post #3): such as A3:A5 ( A-II | A-III | B-I ) ?

 

[AssistanceIsFutile]

I believe the same concept could be applied to your larger data set. The increased number of rows in the Data Pool has no effect.
What isn't clear to me about the Data Entry Range is whether it represents 3 sets of 3-row combinations, or could any 3 row combinations be matched...
(cell references to image in Post #3): such as A3:A5 ( A-II | A-III | B-I ) ?

It is intended to be 3 sets of 3-row combinations.

A2:A4, A5:A7, A8:A10, B2:B4, etc.

 

[Jerry Sullivan]

Here's one way.

Copy the formula shown in E2 to the cells shown in the shot below.

Excel Workbook
	A	B	C	D	E	F	G
1	Data Entry				Helper Cells for Data Entry
2	A-I	D-I			A-I|A-II|A-III	D-I|D-II|D-III	||
3	A-II	D-II
4	A-III	D-III
5	B-I				B-I|B-II|B-III	||	||
6	B-II
7	B-III
8	C-I				C-I|C-II|C-III	||	||
9	C-II
10	C-III
11	Data Pool				Helper Cells for Data Pool
12	A-I	B-I	C-I		A-I|A-II|A-III	B-I|B-II|B-III	C-I|C-II|C-III
13	A-II	B-II	C-II		A-II|A-III|D-I	B-II|B-III|E-I	C-II|C-III|F-I
14	A-III	B-III	C-III		A-III|D-I|D-II	B-III|E-I|E-II	C-III|F-I|F-II
15	D-I	E-I	F-I		D-I|D-II|D-III	E-I|E-II|E-III	F-I|F-II|F-III
16	D-II	E-II	F-II		D-II|D-III|G-I	E-II|E-III|H-I	F-II|F-III|I-I
17	D-III	E-III	F-III		D-III|G-I|G-II	E-III|H-I|H-II	F-III|I-I|I-II
18	G-I	H-I	I-I		G-I|G-II|G-III	H-I|H-II|H-III	I-I|I-II|F-III
19	G-II	H-II	I-II
20	G-III	H-III	F-III
Sheet

Then use the CF formula:
=OR(E10:E12=$E$2:$G$2,E10:E12=$E$5:$G$5,E10:E12=$E$8:$G$8)
Applies To: $A$12:$C$20

 

[AssistanceIsFutile]

Okay, that's doing what I was hoping for, thanks for the assistance.