comparing and merging two sets of data

[Vince]

i have two worksheets with one common column (geographic
regions). I have 1200 rows of data in one and 2300 in the
other. I am looking for a (semi) automated way to compare
these two sets of data and add a blank row in the smaller
worksheet every time it misses a region from the larger
worksheet, so i can subsequently have two worksheets with
the same number of rows, and merge them.

Any help would be welcome

 

[Bernie Deitrick]

Vince,

This solution assumes that your Geography column values are all
unique.

If you insert a column in the larger worksheet, let's say Sheet1, you
can use a Countif formula to flag those values that don't appear in
the second worksheet. The formula would be along the lines of, in row
1 of the inserted column:

=COUNTIF(Sheet2!G:G,Sheet1!G1)

(If your common column is column G, for Geography ;-) )

Copy it down to match your data, then sort based on that column, and
copy any values where the formula's value is 0 and paste them into the
smaller data set.

Sort both data sets on the Geography column, and you should then be
able to combine your tables.

HTH,
Bernie
MS Excel MVP