compare 2 columns

[[email protected]]

Hi all,

Can some one help me for my following question?

I have some data in column A, and some in column C. For example,

Column A

1
2
3
4
5
6
7
8
9
10

Column C

2
4
5
6
7

Now, I want to compare A2:A11 against C2:C6. Here is the question: If a
number in column A do not exist in column C, enter that in Column B;
otherwise if column A number exist in column C, do nothing or leave it
blank. Like 1,3,8,9,10 do not exist in column C, so they should appear
in column B.

Thank you for your help.

[Puppet_Sock]

[email protected] wrote:
> Hi all,
>
> Can some one help me for my following question?
>
> I have some data in column A, and some in column C. For example,
>
> Column A
>
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
> 10
>
> Column C
>
> 2
> 4
> 5
> 6
> 7
>
> Now, I want to compare A2:A11 against C2:C6. Here is the question: If a
> number in column A do not exist in column C, enter that in Column B;
> otherwise if column A number exist in column C, do nothing or leave it
> blank. Like 1,3,8,9,10 do not exist in column C, so they should appear
> in column B.

If it were me, I'd write some VBA to do this. It wouldn't be too
difficult
in VBA. You could be exactly as fancy as you needed. For example,
you could have the code copy the data in the original order, sort it
in place, do the compare, create the extra column, then put the data
back in the original order.

It's just barely possible that Filter might do something for you, but
I never got very good at using it.
Socks

[Herbert Seidenberg]

This will use Advanced Filter.
Add headers to your data like this:

ListA ListB ListC
1 1 2
2 3 4
3 8 5
4 9 6
5 10 7
6
7
8
9
10

Criteria
Test
TRUE

Select these 11 cells: header <ListA> and 1 thru 10
Insert > Name > Create > Top Row
Do likewise for ListC and Criteria.
Again select ListA and its header
Insert > Name > Define > Names in Workbook > type Database
Into the cell that says TRUE, enter this formula:
=NOT(COUNTIF(ListC,ListA))
Data > Filter > Advanced Filter
>Copy to another location
>List Range: type Database
>Criteria Range: type Criteria
>Copy To: select a cell in column B

[Edson]

insert this formula in B2 cell:
=IF(ISNA(MATCH(A2;C:C;0));A2;"")
copy B2 and paste in B3:B11

<[email protected]> escreveu na mensagem
news:[email protected]...
> Hi all,
>
> Can some one help me for my following question?
>
> I have some data in column A, and some in column C. For example,
>
> Column A
>
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
> 10
>
> Column C
>
> 2
> 4
> 5
> 6
> 7
>
> Now, I want to compare A2:A11 against C2:C6. Here is the question: If a
> number in column A do not exist in column C, enter that in Column B;
> otherwise if column A number exist in column C, do nothing or leave it
> blank. Like 1,3,8,9,10 do not exist in column C, so they should appear
> in column B.
>
> Thank you for your help.
>