Compare two columns and find differences

[billtadz]

Hi, can i have an extract formula for third column that find missing data in the first two columns.

GHJ-L10-AP-RM1029 Gig 2/0/8 GHJ-L10-AP-RM1008 Gig 2/0/28 GHJ-L10-AP-RM1019 Gig 1/0/28 GHJ-L10-AP-RM1018 Gig 3/0/8 GHJ-L10-AP-RM1009 Gig 1/0/8 GHJ-L10-AP-RM1059 Gig 3/0/36 GHJ-L10-AP-RM1020 Gig 3/0/12 GHJ-L10-AP-RM1013 Gig 1/0/16 GHJ-L10-AP-RM1012 Gig 2/0/36 GHJ-L10-AP-RM1030 Gig 3/0/32 GHJ-L10-AP-RM1021 Gig 1/0/32 GHJ-L10-AP-RM1022 Gig 3/0/16 GHJ-L10-AP-RM1033 Gig 2/0/12 GHJ-L10-AP-RM1011 Gig 1/0/12 GHJ-L10-AP-RM1023 Gig 1/0/36 GHJ-L10-AP-RM1023 Gig 1/0/36 GHJ-L10-AP-RM1010 Gig 2/0/32 GHJ-L10-AP-RM1035 Gig 2/0/16 GHJ-L10-AP-RM1006 Gig 2/0/24 GHJ-L10-AP-RM1017 Gig 1/0/24 GHJ-L10-AP-RM1024 Gig 3/0/20 GHJ-L10-AP-RM1016 Gig 3/0/4 GHJ-L10-AP-RM1025 Gig 1/0/40 GHJ-L10-AP-RM1037 Gig 2/0/20 GHJ-L10-AP-RM1037 Gig 2/0/20 GHJ-L10-AP-RM1026 Gig 3/0/24 GHJ-L10-AP-RM1015 Gig 1/0/20 GHJ-L10-AP-RM1014 Gig 2/0/40 GHJ-L10-AP-RM1027 Gig 2/0/4 GHJ-L10-AP-RM1005 Gig 1/0/4

GHJ-L10-AP-RM1028 Gig 3/0/20 GHJ-L10-AP-RM1028 Gig 3/0/20 GHJ-L10-AP-RM1008 Gig 2/0/28 GHJ-L10-AP-RM1018 Gig 3/0/8 GHJ-L10-AP-RM1059 Gig 3/0/32 GHJ-L10-AP-RM1013 Gig 1/0/16 GHJ-L10-AP-RM1020 Gig 3/0/24 GHJ-L10-AP-RM1030 Gig 3/0/36 GHJ-L10-AP-RM1033 Gig 2/0/12 GHJ-L10-AP-RM1022 Gig 3/0/28 GHJ-L10-AP-RM1010 Gig 2/0/32 GHJ-L10-AP-RM1017 Gig 1/0/24 GHJ-L10-AP-RM1006 Gig 2/0/24 GHJ-L10-AP-RM1035 Gig 2/0/16 GHJ-L10-AP-RM1024 Gig 3/0/12 GHJ-L10-AP-RM1061 Gig 3/0/40 GHJ-L10-AP-RM1016 Gig 3/0/4 GHJ-L10-AP-RM1061 Gig 3/0/40 GHJ-L10-AP-RM1016 Gig 3/0/4 GHJ-L10-AP-RM1015 Gig 1/0/20 GHJ-L10-AP-RM1026 Gig 3/0/16 GHJ-L10-AP-RM1027 Gig 2/0/4

GHJ-L10-AP-RM1029 Gig 2/0/8 etc...

 

[navic]

Try it

1. Select the entire range 'C1:C30'
2. Press the 'F2' key
3. Enter the formula below
=IF(COUNTIF(A1:A30,B1:B30),"",A1:A30)
4. Press Ctrl + Shift + Enter

 

[navic]

Maybe a different way
ARRAY formula in cell 'D2'

=IFERROR(INDEX($A$2:$A$31,SMALL(IF(ISNA(MATCH($A$2:$A$31,$B$2:$B$31,0)),ROW($B$2:$B$31)-ROW($B$2)+1),ROWS(D$1:D1))),"")

ARRAY formula in cell 'E2'

=IFERROR(INDEX($B$2:$B$31,SMALL(IF(ISNA(MATCH($B$2:$B$31,$A$2:$A$31,0)),ROW($A$2:$A$31)-ROW($A$2)+1),ROWS(E$1:E1))),"")

ARRAY formula in cell 'G2'

=IFERROR(INDEX($D$2:$D$31,MATCH(0,INDEX(COUNTIF($G$1:$G1,$D$2:$D$31),0),0)),"")

ARRAY formula in cell 'H2'

=IFERROR(INDEX($E$2:$E$31,MATCH(0,INDEX(COUNTIF($H$1:$H1,$E$2:$E$31),0),0)),"")