0

I have such Excel data:

enter image description here

I want to find records in ColumnA which do not exist in ColumnB.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Hasanova
  • 213
  • 1
  • 3
  • 8
  • possible duplicate of [Excel Two Columns With Duplicates](http://stackoverflow.com/questions/10399060/excel-two-columns-with-duplicates) – Joe Feb 03 '15 at 12:11
  • @pnuts you're right; http://stackoverflow.com/questions/12393524/excel-delete-row-if-column-contains-value-from-to-remove-list may be closer. – Joe Feb 03 '15 at 12:24

2 Answers2

1

I assume that values starts from second row (A2 and B2)

Put below code in column where you want to display result

=IF(COUNTIF(B:B,A2),A2,"") //"" or 0 - whatever you want to put if B is not in A
michalczukm
  • 9,963
  • 6
  • 40
  • 47
  • Yes, thank you - it depends on locale. Polish Excel uses `;` marks. – michalczukm Feb 03 '15 at 11:51
  • In Polish excel if you try to write a formula with `,` instead od `;` it won't be valid. Thats because excel treat `,` as `.` separator. But you're of course right with `COUNTIF` – michalczukm Feb 03 '15 at 12:06
0

Assuming your data starts in Row1 please try: 

=IFERROR(MATCH(A1,B:B,0),"#")

in Row1 and copy down to suit. # indicates the corresponding row's ColumnA value is not found in ColumnB. A number indicates the row in ColumnB that matches the ColumnA value for the row the value appears in.

pnuts
  • 58,317
  • 11
  • 87
  • 139