compare unsorted data across workbooks

[aileen]

I have 2 workbooks, one with 6 columns of data and the other with 12 columns
of data. Both have an unspecified number of rows. The data in the first
workbook(6 columns, A-F) needs to be compared with the data in the second
workbook(12 columns, but only columns G-L need to match the data in the
first worksheet columns A-F). I need to create a 3rd workbook showing the
data in the rows that did not match across the 2 workbooks. The first two
workbook's data are not sorted so the rows don't line up across the
workbooks.

e.g. workbook 1 data columns A-F
SPX OCT 2008 800 0 1200
SPX OCT 2008 900 0 -1400
SPX OCT 2008 975 0 200
workbook 2 data columns G-L
SPX OCT 2008 900 0 -1400
SPX OCT 2008 800 0 1200
SPX OCT 2008 975 0 100

The only data that will show up in the 3rd workbook is
SPX OCT 2008 975 0 200 and SPX OCT 2008 975 0 100

I have tried the excel addins compare.xla and workbook compare, but neither
seems to allow for rows that do not line up across workbooks. I believe I
will need to loop through one of the workbooks in order to check data for
this reason.

If this is at all possible, any help would be greatly appreciated

 

[BobT]

What you need to do is compare the union of the columns. If you can (want
to) add a column to each sheet that adds the key 6 columns together:

=A1&B1&C1&D1&E1&F1 (or whatever the columns are)

With these in place, you can then do a VLOOKUP to compare the two and report
the ones that don't match. You could do this with VBA too, but this might be
faster (though manual).

 

[aileen]

This is partially working, but I can only get the VLOOKUP to report the ones
that match. This is the code I'm using. Please let me know how to get the
unmatched data to appear instead of the matched data. This is the formula I'm
using:

=VLOOKUP($A$1:$A$500,$B$1:$B$500,1,FALSE)

Thanks very much for your help.