Checking

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

@Azinferno 

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

image.png

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

=IFERROR(INDEX(Sheet2!$A$1:$A$15,
    AGGREGATE(15,6,
        1/(COUNTIF($E$1:$E$6,Sheet2!$A$1:$A$15)=0)/
           (COUNTIF($B$1:$B$6,Sheet2!$A$1:$A$15)=0)*
        ROW(Sheet2!$A$1:$A$15),
ROW())),"")

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.