Filtering unwanted cells

**gjonm** · 09-29-2009, 10:11 AM

I'm trying to filter out unwanted data from a spreadsheet.

I have two columns, (A) contains a series of numbers and this is the search criteria. Column (B) contains the data.

Example:

A
7887765
4344555
2233455
8386375
3647364
3847387

B
7875895
3847387
8386375
5345344
3465356
9887645
5767655
8386375
3847387

8386375 & 3847387 appears in column A and twice in B

I want Excel to filter out any unique numbers in column B which don't appear in column A.

Is this possible?

I'm using Excel 2007.

Thanks

Gav

**Cheeky Charlie** · 09-29-2009, 10:34 AM

C2=and(countif(B:B,B2)=1,countif(A:A,B2)=0)
Copy down
Returns TRUE for every unique entry in col B not found in col A, filter this column for TRUE and do what you want

HTH

**gjonm** · 09-29-2009, 11:10 AM

Thank you so much.

**gjonm** · 09-30-2009, 09:41 AM

I have tried this formula. However, it seems to be return faulse when there is no match in Column A?

From what I can see it's looking in Column A & B.

Example attached

Book2.xlsx

**Cheeky Charlie** · 09-30-2009, 10:00 AM

Sorry, can't download xlsx format.

Please describe your situation clearly:
My formula -
1. checks if an entry is unique in B:B i.e. count all times this appears in B:B, if it is once:
2. checks if the entry is found anywhere in A:A (i.e. count all times this entry in B appears in A:A - if it is none, then TRUE

This is what I understood from your description:

I want Excel to filter out any unique numbers in column B which don't appear in column A.

CC