Multiple Sheets Duplicate Values

[ExcelSur]

Hello Guys,
Can I get some help in identifying duplicate values. I have an excel file where I create worksheets every week. The worksheets have many columns and I want to identify duplicate values of 1 column (Invoice #). If I create a new worksheet and enter an invoice number excel should compare all the worksheets and highlight the duplicate invoice number in the new worksheet if there is a duplicate value.


I have attached a sample excel file

 

[bosco_yip]

Try.......

Select new worksheet "Bayou 3"sheet C4:C9 >> Conditional Formatting >> New rule >> Use a formula ….>> in the rule box enter :

=IFERROR(VLOOKUP($B4,'Bayou 1'!$B$4:$C$100,2,0)=$C4,0)+IFERROR(VLOOKUP($B4,'Bayou 2'!$B$4:$C$100,2,0)=$C4,0)

>> Click "Format", then select cell color >> OK >> Finish.

Regards
Bosco

 

[ExcelSur]

Try.......

Select new worksheet "Bayou 3"sheet C4:C9 >> Conditional Formatting >> New rule >> Use a formula ….>> in the rule box enter :

=IFERROR(VLOOKUP($B4,'Bayou 1'!$B$4:$C$100,2,0)=$C4,0)+IFERROR(VLOOKUP($B4,'Bayou 2'!$B$4:$C$100,2,0)=$C4,0)

>> Click "Format", then select cell color >> OK >> Finish.

Regards
Bosco

Hello Bosco
Really appreciate your help. I have a question. I am trying to find duplicates for invoice #. I am bit confused with the formula. Is your formula looking duplicate amounts. How can I use the formula to highlight the duplicate invoice #s


Thanks

 

[rahulshewale1]

Hi @ExcelSur ,

Just Change Cell Reference in highlighted in Red Colour
=IFERROR(VLOOKUP($B4,'Bayou 1'!$B$4:$C$100,1,0)=$B4,0)+IFERROR(VLOOKUP($B4,'Bayou 2'!$B$4:$C$100,1,0)=$B4,0)

Copy Formula in Conditional Formatting New Rule

 

[ExcelSur]

Hello Rahul
That worked. Really appreciate your help.