MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Matching information from one spreadsheet to another.


Posted by H. Cooks on February 19, 2001 8:51 AM

Hello All,

I have two spreadsheets containing four columns each,
both contain the same basic information, with the
exception that one spreadsheet contains additional
rows containg additional information.

i.e.

Spreadsheet 1 Spreadsheet 2

a b c d a b c d
1 2 3 4 1 2 3 4
1 5 7 8 1 5 7 8
2 4 5 6 7 7 7 9
7 5 7 7 2 4 5 6
7 5 7 7


As you can see the information is the same in both
spreadsheets, with the exception of Row four of Columns
a, b, c, and d of spreadsheet 2, (7 7 7 9). Now
imagine 2000 such lines. I need to take information
from Spreadsheet 2, match it up to Spreadsheet 1, add
a line in Spreadsheet 1 for the additional information
in Spreadsheet 2. Can this be done with a MACRO or
a LOOKUP funtion? If so how, if not, then how can I
do this other than Cut and Paste.


Thank You.


Posted by Nick on February 19, 2001 2:09 PM

Hi,

1, are the rows unique? I doubt it, just a shot.

2, when you say "match them up" - based on
some sort of row number?

3, how many rows are off?

Posted by H. Cooks on February 20, 2001 5:52 AM

Posted by Nick on February 20, 2001 6:04 AM

do you know even the slightest bit about access? (ntip)

Posted by Aladin Akyurek on February 20, 2001 11:42 AM

Have a look at

8967.html

What you need might be a variant of the proc described therein.

Posted by Nick on February 21, 2001 7:41 AM

Drop both sets of data into access as separate tables. you can either copy the excel data and paste, append into a table, or import the table.

now you have two tables and want to compare.

say queries, new, find unmatched. the wizard will guide you through the process, and produce a list (in query form) which is your answer.

this is a great tool, you may use it again some time.
if you have problems with any step, please post again.
regards,
Nick

if it must be excel, how about summing the rows, then sorting on the sums, as a start.