How to identify duplicates using two criteria

Sean15

Well-known Member
Joined
Jun 25, 2005
Messages
585
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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,150
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
585
Office Version
2010
Platform
Windows
Thank you very much.

Sean.
 

Fluff

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

Watch MrExcel Video

Forum statistics

Threads
1,090,491
Messages
5,414,853
Members
403,549
Latest member
CascadeDiver

This Week's Hot Topics

Top