Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Vic Vic is offline
external usenet poster
 
Posts: 117
Default matching 2 worksheets to find differences

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
Vic Vic is offline
external usenet poster
 
Posts: 117
Default matching 2 worksheets to find differences

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.

"Vic" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default matching 2 worksheets to find differences

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 he
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)







Vic wrote:

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.

"Vic" wrote:

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


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to find exact differences between two worksheets geek Excel Worksheet Functions 1 February 25th 09 01:19 PM
Find differences between two Excel workbooks or worksheets savage_planet Excel Discussion (Misc queries) 0 September 19th 05 09:27 PM
Find Matching Records in Two Worksheets kittybat Excel Discussion (Misc queries) 2 April 5th 05 06:51 PM
Find matching records in two worksheets kittybat Excel Discussion (Misc queries) 3 March 30th 05 12:11 AM
How do I find differences between two excel worksheets? jfurneaux New Users to Excel 1 March 10th 05 02:05 PM


All times are GMT +1. The time now is 02:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"