AssistanceIsFutile
New Member
- Joined
- Apr 5, 2013
- Messages
- 4
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:
<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):
<tbody>
</tbody>
Ex 3 (C1:C3 have an exact match with B7:B9, so the duplicates are highlighted):
<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
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
Last edited: