Column matching problem

[fox8267]

Hi Gurus,

Please help me in my req.. I have two Cols

Col_01
1
2
3
4
5
6
7
8
9
10

Col_02
1
2
3
4
5
I need those value of Col1 which are not present in col_02
It mean
Col_03 should have
6
7
8
9
10

Thanks in advance

[Paul]

Hi Fox8267,

You could try the array formula:

Please Login or Register  to view this content.

After entering this formula, you must press CTRL+SHIFT+ENTER, not just ENTER.

This formula assumes list 1 is in A1:A10, list 2 is B1:B5 and you're entering the formula in C1. The formula could be changed to accommodate other scenarios/ranges, of course.

[Paul]

Actually, I just found that my formula ignores rows 1:5 of column A. Darn.

In C1 try this instead:

Please Login or Register  to view this content.

Fill that down to C10. C1:C10 will now show any values that are in A1:A10 but not in B1:B5.

To remove blanks, in D1 use the array formula (CTRL+SHIFT+ENTER):

Please Login or Register  to view this content.

Fill that down to D10 to get your final list of unmatched values without the blanks or errors.