compare for discrepancy

[hiranuk]

Hi

I am trying to find out which 3 entries are missing. Column A has 192 entries B and C has 190 so there are 3 that are missing. Im not sure how to easily find which 3 they are. Is there a way to out those 3 in column D?

i have attached excel sheet.

Thanks

[Bo_Ry]

My count is 2
Please try

=FILTER(B1:B193,ISNA(MATCH(B1:B193,C1:C193,)))

[hiranuk]

Hi Thanks. but in column A there are 3 entries more that column B. thanks for finding the 2, but is there a way of finding the 3rd. there may be something in column A that is not in column B. How can i tell without going through it manually.

[bebo021999]

Sort both column then in D1:

=1-COUNTIF(A1,"*"&B1&"*")

returns 0 if match.

Go down till getting 1
The 3 are
emisnet-12911
emisnet-53208
emisnet-53234HO

and 1 more:
emisnet-53234VPN with same number-string with "emisnet-53234HO" that causes 53234 counted twice.

[alansidman]

Using Power Query/Get and Transform located on the Data Tab.

1. Bring Column A into PQ Editor and split data so you only have 5 digits
2. Bring Column B into PQ Editor
3. Merge Column A Table with Column B Table in PQ with a Left Anti Join

Result

v	A	B
1	A	B
2	53208
3	16780