Checking

%3CLINGO-SUB%20id%3D%22lingo-sub-2671024%22%20slang%3D%22en-US%22%3EChecking%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2671024%22%20slang%3D%22en-US%22%3EHow%20can%20I%20get%20excel%20to%20check%20B1-6%20against%20E1-6%20for%20duplicates.%20And%20check%20those%202%20columns%20against%20data%20in%20sheet%202%20that's%20used%20to%20fill%20those%20columns.%20And%20any%20names%20not%20used%20placed%20in%20H1-3%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2671024%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2671593%22%20slang%3D%22en-US%22%3ERe%3A%20Checking%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2671593%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3E%3CBR%20%2F%3Efor%20the%20first%20part%20of%20your%20question%20use%20vlookup.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fvideo-vlookup-when-and-how-to-use-it-9a86157a-5542-4148-a536-724823014785%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fvideo-vlookup-when-and-how-to-use-it-9a86157a-5542-4148-a536-724823014785%3C%2FA%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fvlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fvlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1%3C%2FA%3E%3C%2FLINGO-BODY%3E
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.