Finding Duplicate Pairs in Excel

Question

So I have this summary sheet. It contains data from multiple workbooks going across.

It's not like this question, because what I'm trying to do is find all the inconsistant pairs of data in this worksheet going across and highlight them.

Here is a fiddle that explains what I want to accomplish. I have a large worksheet, and would like to compare the first 2 rows with the next 2 rows etc. throughout the worksheet. Below is an HTML representation of what I am trying to accomplish.

<table class="tg">
<tr>
<th class="tg-031e">#INT1</th>
<th class="tg-031e">#INT1</th>
<th class="tg-031e">#INT2</th>
<th class="tg-031e">#INT2</th>
<th class="tg-031e">#INT3</th>
<th class="tg-031e">#INT3</th>
</tr>
<tr>
<td class="tg-031e">Apples</td>
<td class="tg-031e">Y</td>
<td class="tg-031e">Bananas</td>
<td class="tg-031e">Y</td>
<td class="tg-031e">Apples</td>
<td class="tg-031e">Y</td>
</tr>
<tr>
<td class="tg-031e">Bananas</td>
<td class="tg-031e">Y</td>
<td class="tg-031e">Peppers</td>
<td class="tg-031e">Y</td>
<td class="tg-031e">Bananas</td>
<td class="tg-031e">Y</td>
</tr>
<tr>
<td class="tg-031e">Peppers</td>
<td class="tg-031e">Y</td>
<td class="tg-031e">Pomegranite</td>
<td class="tg-031e">Y</td>
<td class="tg-031e">Peppers</td>
<td class="tg-031e">Y</td>
</tr>
<tr>
<td class="tg-031e">Pomegranite</td>
<td class="tg-031e">Y</td>
<td class="tg-031e">Nuts</td>
<td class="tg-031e">YYYYYNN</td>
<td class="tg-031e">Pomegranite</td>
<td class="tg-031e">Y</td>
</tr>
<tr>
<td class="tg-031e">Nuts</td>
<td class="tg-031e">YYYYYYNN</td>
<td class="tg-031e">Smango</td>
<td class="tg-031e">NNNYYNNN</td>
<td class="tg-031e">Nuts</td>
<td class="tg-031e">NNNYNNNN</td>
</tr>
<tr>
<td class="tg-zl7m">Oranges</td>
<td class="tg-zl7m">YYYYNNNN</td> <!-- this oranges is different from... -->
<td class="tg-031e">Blackberries</td>
<td class="tg-031e">NNNYYNNNN</td>
<td class="tg-zl7m">Oranges</td> 
<td class="tg-zl7m">NNNYYNNN</td> <!-- ...this one -->
</tr>
<tr>
<td class="tg-031e">Smango</td>
<td class="tg-031e">NNNYYNNN</td>
<td class="tg-031e">Berries</td>
<td class="tg-031e">YYNYNNNN</td>
<td class="tg-031e">Smango</td>
<td class="tg-031e">Y</td>
</tr>
<tr>
<td class="tg-031e">Skiwi</td>
<td class="tg-031e">NNNYNNNN</td>
<td class="tg-031e">Beer</td>
<td class="tg-031e">NNNYNNNN</td>
<td class="tg-031e">Steaks</td>
<td class="tg-031e">Y</td>
</tr>
<tr>
<td class="tg-031e">Steaks</td>
<td class="tg-031e">Y</td>
<td class="tg-031e">Blueberries</td>
<td class="tg-031e">YNNYNNNN</td>
<td class="tg-031e">Steaksauce</td>
<td class="tg-031e">NNNYNNNN</td>
</tr>
<tr>
<td class="tg-zl7m">Steaksauce</td>
<td class="tg-zl7m">YYNYNNNN</td>
<td class="tg-031e">Blucheese</td>
<td class="tg-031e">NNNYNNNN</td>
<td class="tg-zl7m">Apricot</td>
<td class="tg-zl7m">YYYYNNNN</td>
</tr>
<tr>
<td class="tg-031e">Apricot</td>
<td class="tg-031e">YYYYNNNN</td>
<td class="tg-031e">Blackberries</td>
<td class="tg-031e">NNNYNNNN</td>
<td class="tg-031e">Milkshake</td>
<td class="tg-031e">NNNYNNNN</td>
</tr>
</table>

I have tried VBA solutions and also conditional formatting. Any solution that will make this work is greatly appreciated.

Thank you.

Even with the fiddle it is not clear what you want to accomplish. — prmottajr, Jun 24 '15 at 21:30
Please provide an example of what you consider to be an inconsistent pair vs. a "consistent pair". — Parker, Jun 24 '15 at 22:58
I tried searching for duplicates, but that only gives me values on one column, like [right here](http://www.excel-easy.com/examples/find-duplicates.html). A consistant pair would consist of, for instance if you look at the fiddle: Bananas is associated with 'Y' three times. Also, 'Nuts' has the same value pair in both E and F as well as A and B. I meant to highlight Oranges and Steaksauce not the apricot part.I have updated the Fiddle here: https://jsfiddle.net/0qvj73ua/1/. You can see how both Oranges and Steaksauce are inconsistent. I am comparing the first 2 values throughout the worksheet. — Joseph R, Jun 25 '15 at 14:18
Is there anyway I could do some conditional formatting that would just highlight the pairs consistent data? For instance: Bananas Y. Wherever that is true, Bananas and Y will be highlighted. Same with any data pair that is the same. I have been working on this problem for a long time, but nothing I have seen is like what I'm doing. I'm trying to go across. I know there has to be a VBA macro or conditional formatting equation that will do this task, but I have not come across it yet. — Joseph R, Jun 26 '15 at 16:18

Tom Sharpe · Accepted Answer · 2015-06-29T10:10:14.467

I think this array formula should work:-

=SUM(ISODD(COLUMN())*($A$2:$E$12=A2)*($B$2:$F$12<>B2))

if the table starts in A1, this can be applied as conditional formatting from A2 to E12 and will highlight the left-hand (fruit) cell of inconsistent pair of cells.

Then you can use a similar formula to highlight the right-hand cell of each pair:-

=SUM(ISEVEN(COLUMN())*($A$2:$E$12=A2)*($B$2:$F$12<>B2))

Apply this from B2 to F12.

enter image description here

Note that Smango are highlighted because they are in an inconsistent group (although they are also in a consistent group).

Here is the alternative approach (as suggested) of highlighting the consistent groups:-

enter image description here

The formulae are

=SUM(ISODD(COLUMN())*($A$2:$E$12=A2)*($B$2:$F$12=B2))>1

and

=SUM(ISEVEN(COLUMN())*($A$2:$E$12=A2)*($B$2:$F$12=B2))>1

to be applied as before.

The sum this time will always be at least one because each pair of cells will match with itself, so the '>' sign is to find if there are any matches with other pairs of cells.

Thank you! The second half worked for me, although I think the highlighting made navigating through excel rather slow, I was able to get what I needed. — Joseph R, Jul 06 '15 at 18:47
Good! Yes, I suspected the array formula might be a bit slow if applied to a large number of cells. — Tom Sharpe, Jul 06 '15 at 20:38

Finding Duplicate Pairs in Excel

1 Answers1