Matching 2 columns

V

Vic

Column A and column B are both 10 positions long.
1) I need to list all entries in A that are not in B
2) I also need to list all entries in B that are not in A.
 
T

T. Valko

Try these array formulas** :

Items in A that are not in B:

Array entered in D1:

=IF(ROWS(D$1:D1)<=SUM(--ISNA(MATCH(A$1:A$10,B$1:B$10,0))),INDEX(A$1:A$10,SMALL(IF(ISNA(MATCH(A$1:A$10,B$1:B$10,0)),ROW(A$1:A$10)),ROWS(D$1:D1))-MIN(ROW(A$1:A$10))+1),"")

Copy down until you get blanks

Items in B that are not in A:

Array entered** in E1:

=IF(ROWS(E$1:E1)<=SUM(--ISNA(MATCH(B$1:B$10,A$1:A$10,0))),INDEX(B$1:B$10,SMALL(IF(ISNA(MATCH(B$1:B$10,A$1:A$10,0)),ROW(B$1:B$10)),ROWS(E$1:E1))-MIN(ROW(B$1:B$10))+1),"")

Copy down until you get blanks

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
G

Glen

It depends on what you want your list to be like. What are you doing with
the values that are missing?

A very simple way to find missing values is to use countif...
Assuming your data is in rows 1-10 of columns A&B, then paste this formula
into cell c1 and copy down
=COUNTIF($A$1:$A$10,B1)

This counts how many times the B column value for that row appears in column
A. Any rows with a count of zero indicate a missing value. It's not a true
list like an array but is much more intuitive.

Cheers,

Glen
 
V

Vic

My column A ranges from A2 thru A1274 and column B is from B2 thru B1003.
I can't figure out how to adjust these formulas. The first rows contains
headings.
Please help.
 
V

Vic

Here is what I need. I have 1273 invoices. My service provider has 1002
invoices. We don't match. I need to find invoices that he does not have. I
also need to know of invoices that he has but I don't. I don't care about any
duplicates. I want them gone. I do not need to count anything. I need to
produce a list of unique invoice numbers. Invoice numbers are 10
alphanumerics in length (not really numbers).
Please help.
 
G

Glen

What's the next thing you need to do with the missing data? Which formula
can't you figure out, mine or T. Valko's?

For mine, it bceomes
=COUNTIF($A$2:$A$1274,B2)

put this is c2 then copy and paste down to row 1003.

I would think twice about including formula in a spreadsheet that are too
complicated for you to edit or you risk having a spreadsheet that you can't
do anything with the next time something changes.

Cheers,

Glen
 
V

Vic

This formula counts something. I need the actual values. For example:

If value of A2 is not found in B2 thru B1003, I want the value of A2 to
appear in C2.
Also if B2 is not found in A2 thru A1274, I need the value of B2 to be
placed in D2. If anything in A and B match, I don't want that - It means that
they are already reconciled.

Thank you.
 
T

T. Valko

My column A ranges from A2 thru A1274
and column B is from B2 thru B1003.
Hmmm...

Column A and column B are both 10 positions long.
Click to expand...

The formulas I suggested will work but they'll be slow to calculate on
ranges that size. In the formulas replace all references of:

A1:A10 with A2:A1274
B1:B10 with B2:B1003
 
G

Glen

OK. Makes more sense now. The steps below will give you two lists, with
missing only (no duplicates etc). Menu locations will work for Excel95-02.
IF you have Excel07, you'll have to work them out but should be similar.

*Copy the two columns into a new sheet

First sheet will find missing values in column B:
*Paste this formula into cell B2
=COUNTIF($A$2:$A$1274,B2)
*Copy and paste the formula all the way down to row 1002 in column c.
*Select column c.
*Turn auto filter on (menu is |Data|Filter|Autofilter|)
*click the little drop down arrow at the top of column c and select Custom...
*change first filter option to read "Does not equal" and 0, click OK.
*select all (Ctrl+A) and then hit delete.
*select column A and delete
*select column C and delete
*select column B and sort (little A-Z button on menu or else |Data|Sort| and
OK.

The only thing left now should be missing invoices in column B.

Second sheet will find missing values in column A:
*Paste this formula into cell B2 of the second sheet
=COUNTIF($b$2:$b$1002,a2)
*Copy and paste the formula all the way down to row 1274 in column c.
*Select column c.
*Turn auto filter on (menu is |Data|Filter|Autofilter|)
*click the little drop down arrow at the top of column c and select Custom...
*change first filter option to read "Does not equal" and 0, click OK.
*select all (Ctrl+A) and then hit delete.
*select column B and delete
*select column C and delete
*select column A and sort (little A-Z button on menu or else |Data|Sort| and
OK.

The only thing left now should be missing invoices in column A.

Hope this helps.

Glen
 
G

Glen

This formula counts something. I need the actual values. For example:

Yes, but then the rest of the steps deleted everything where there was
something to count, leaving only the unmatched invoiced numbers.
If value of A2 is not found in B2 thru B1003, I want the value of A2 to
appear in C2.
Click to expand...

It pays to explain exactly what you need - I could have done this in the
first reply and had the answer to you ages ago :-(

Put this formula in cell C2
=IF(ISERROR(MATCH(a2,$b$2:$b$1003,0)),a2,"")

and put this formula in D2
=IF(ISERROR(MATCH(B2,$A$2:$A$1274,0)),B2,"")

paste them both down. Only missing invoices will be shown.

Cheers,

Glen
 
Top