matching 2 worksheets to find differences

[Vic]

Can someone please help me with this task?

I have 2 worksheets I need to match to find entries that don't apprear in
the other worksheet. Worksheet3 shows rows from Worksheet1 that don't appear
in Worksheet2. Worksheet4 shows rows from Worksheet2 that don't appear in
Worksheet1.

Example:
worksheet1 worksheet2 worksheet3
worksheet4
A B C <= cols => A B C <= cols => A B C <= cols => A B C
a b 1 a b 1 a b 2
b n 1
a b 2 a b 3 c h 5
d f 3
a b 3 b n 1 d f 1
c h 3 c h 3
c h 5 c h 7
c h 7 d f 3
d f 1

Thank you,
Victor

 

[Vic]

Due to the line wrap the example is diffucult to understand.

Here it is again:

Worksheet1
a b 1
a b 2
a b 3
c h 3
c h 5
c h 7
d f 1

Worksheet2
a b 1
a b 3
b n 1
c h 3
c h 7
d f 3

Result of comparison.

Worksheet3 - entries in WS1 missing from WS2
a b 2
c h 5
d f 1

Worksheet4 - entries in WS2 missing from WS1
b n 1
d f 3

Thank you.

 

[Dave Peterson]

I use a technique that I like to compare single column ranges. To use it on
your data, you'd have to do a little housework to get started.

If you want to try.

You have 3 columns in each sheet. Insert a new column and use a formula to
concatenate the three cells into a single cell.

I'd use a formula like:
=a1&"."&b1&"."&c1
and drag down the column.

I used a delimiter of a dot to separate each field. You'd want to use a
character that isn't used in your data (something like one of these: ; , | -

Then convert this column to values
Select the column
Edit|copy
Edit|paste special|Values

And do the same for the second sheet.

Now you have two individual columns that can be compare.

Insert a new worksheet
Copy that concatenated column in sheet1 to column A of this sheet. Insert
headers if there aren't any.

Copy the concatenated column in sheet2 to the bottom of the data on this new
sheet--don't include any headers.

Now all the data is in column A--but it has duplicates.

Use the technique at Debra Dalgleish's site:
http://contextures.com/xladvfilter01.html#FilterUR
or watch a video here:
http://www.contextures.com/xlVideos04.html#AdvFilt2003

Now you have a list of unique values in column B.

Delete column A. We're done with it.

Sort by the new column A (only if you want it in order).

In B1, add a header:
On Sheet1

In B2, add a formula:
=isnumber(match(a2,sheet1!d:d,0))
(and drag down)

In C1, add a header:
On Sheet2
In C2, add a formula:
=isnumber(match(a2,sheet2!d:d,0))
and drag down.

Apply data|filter|autofilter to columns A:C and you can filter to show the 3
cases in column B and C:
True/True (on both sheets)
False/True (not on sheet1, but on sheet2)
True/False (on sheet1, but not on sheet2)