How to filter a column to data in another column

Hi all,

I refer to the attached Book 2.

I am trying to clean out data/numbers in column A so that it only contains data/numbers found in Column B.It is a huge database, column A has 28145 rows of data while Column B has 816 rows. Almost all the data in Column A have repetitions, which is fine. I only want to remove those numbers not found in Column B.

I tried Sort & Filter but was not getting anywhere.

Cheers.

Nyunos.

one possible way:

put this formula in column C

=IF(ISNA(MATCH(A1,B:B,0)),"",A1)

Copy down to the last populated row of column A

Then sort by column C, copy and paste the values

Hi Teylyn,

That works. Many thanks!! It's much appreciated.

Will look into the ISNA and MATCH functions to understand how you figure that out.

Cheers.

Nyunos

Nyunos,

MATCH(A1,B:B,0) will return the position of the value of A1 in column B if it is found. If the value is not present, Match will return the #N/A error.

The NA() function returns True if its argument is an #N/A error.

So, if the Match function returns an #N/A error, i.e. the value is not found in column B, then the IF returns a blank "". If, on the other hand, the Match function does NOT return the error, it means that the value has been found. We don't care about where, we just want to see that it has been found, so then we return the value from column A.

You may want to have a look at the formula with the Evaluate Formula tool. It's great to step through a formula and see the evaluation results step by step.