Compare and mark help

[Pejter]

Hi, new to forum and excel! Appriciate som help.

I have a question around comparing values in cells in diffrent sheets towards a master sheet, and marking possible duplicates.

Prerequisite
File has 5 Sheets, Sheet1, sheet2, sheet3, sheet,4 and sheet 5
Colum A in all sheet´s are containing Assettags, like "GFH135232".

The assets in Sheet 1, Colum A will be subject to some form of use in workload for a team, the assets in Colum A in the other 4 Sheets are diffrent needs to extract Assets in Sheet 1. There will be a continual increase and subraction of computers entered into Colum A, in Sheet1. The amount of Assets in Sheets, 2, 3, 4 and 5 will increase over time.

Functionality
Either by rule, or a event, if a Assettag exists in Colum A in any of Sheet2, Sheet3, Sheet4, Sheet5 AND in Sheet1, the row in Sheet1(or the Assettag cell in ColumA gets colored) So that its possible to manually remove that row from Sheet1.

Thanks again to anyone taking their time to take a look

 

[Saurabhj]

Hi,

Let assume your data is from A2 to A150 in Sheet 1.

So, select data A2 to A150 in Sheet 1 and apply below formula in Conditional Formatting.

=OR(A2=Sheet2!$A:$A,A2=Sheet3!$A:$A,A2=Sheet3!$A:$A,A2=Sheet4!$A:$A,A2=Sheet5!$A:$A)

Thanks,
Saurabh

 

[Phuoc]

Try this

=COUNTIF(Sheet2!A:A,A2)+COUNTIF(Sheet3!A:A,A2)+COUNTIF(Sheet4!A:A,A2)+COUNTIF(Sheet5!A:A,A2)>0