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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,012
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,012
Office Version
365
Platform
Windows
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,090,244
Messages
5,413,281
Members
403,472
Latest member
ArtisticOwl

This Week's Hot Topics

Top