How to identify duplicates using two criteria

Sean15

Well-known Member
Joined
Jun 25, 2005
Messages
582
Office Version
2010
Platform
Windows
Hello:


<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;;">Name</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;;">Invoice No</td><td style="text-align: right;;"></td><td style=";">Duplicate invoice</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Peter Paul</td><td style="text-align: right;;"></td><td style="text-align: right;;">121245</td><td style="text-align: right;;"></td><td style=";">no</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">PeterPaul</td><td style="text-align: right;;"></td><td style="text-align: right;;">121245</td><td style="text-align: right;;"></td><td style=";">yes</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Tiffany</td><td style="text-align: right;;"></td><td style="text-align: right;;">5675225</td><td style="text-align: right;;"></td><td style=";">yes</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Macy</td><td style="text-align: right;;"></td><td style="text-align: right;;">5675225</td><td style="text-align: right;;"></td><td style=";">no</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Tiffany</td><td style="text-align: right;;"></td><td style="text-align: right;;">5675225</td><td style="text-align: right;;"></td><td style=";">yes</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">PeterPaul</td><td style="text-align: right;;"></td><td style="text-align: right;;">121245</td><td style="text-align: right;;"></td><td style=";">yes</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">PeterPaul</td><td style="text-align: right;;"></td><td style="text-align: right;;">1524456</td><td style="text-align: right;;"></td><td style=";">No</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br />


If name in column C and Invoice No in column E appears more than once, required result in column G should be "duplicate", if not "no"
so in example above, Tiffany invoice No 5675225 appears more than once, so required value in column G should be "duplicate"
Macy invoice No 5675225 should be tagged "No" (even though it has same invoice no as Tiffany)

Could you help please?

Sean
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,883
Office Version
365
Platform
Windows
How about
=IF(COUNTIFS($A$2:$A$8,A2,$C$2:$C$8,C2)>1,"duplicate","no")
 

Sean15

Well-known Member
Joined
Jun 25, 2005
Messages
582
Office Version
2010
Platform
Windows
Thank you very much.

Sean.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,883
Office Version
365
Platform
Windows
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,089,995
Messages
5,411,729
Members
403,394
Latest member
Mohan Kumar

This Week's Hot Topics

Top