Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare unsorted data across workbooks
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare unsorted data across workbooks
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare unsorted data across workbooks
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare data in two different workbooks | Excel Discussion (Misc queries) | |||
How do I compare data in 2 workbooks | Excel Programming | |||
How can I compare data on 2 workbooks | Excel Discussion (Misc queries) | |||
how do I compare workbooks for duplicate data | Excel Discussion (Misc queries) | |||
Is there a way to compare data on 2 different workbooks to find m. | Excel Worksheet Functions |