Occasional Visitor
How can I get excel to check B1-6 against E1-6 for duplicates. And check those 2 columns against data in sheet 2 that's used to fill those columns. And any names not used placed in H1-3
2 Replies


Unfortunately you didn't mention on which version of Excel you are.

As variant:

To check duplicates you may apply conditional formatting rule to each of ranges


Names which are not in B1:B6 and E1:E6 - add in H1 formula


and drag it down till empty cells appear.

If range starts not from first row of the sheet formula is to be modified, except range

ROW() - ROW(cell before start)

at the end.

In attached file they are applied to the first and last blocks.