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.
"BobT" wrote:
> 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" wrote:
>
> > 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
|
Similar Threads
