Looking to subtract one list of values from another

P

Paul

Hi,

If someone could heelp me with this you could save my friday for me!!

I have a problem in this format (man it's hard to articulate this)

worksheet1 has 4 columns and 10 rows of data.
worksheet2 has 4 columns and 20 rows of data.

I know that that rows in worksheet1 are duplicated in worksheet2, so I
need to "subtract" one set from the other to leave the unduplicated
records.

I should point out that some rows are common for both workbooks but
not duplicated.....i.e. the first workbook contains, let's say 3 A's,
the second contains 4 A's but only the 3 of these are duplicate.

If you can understand any of what I've written and have any
suggestions, they'd be gratefully received.

Paul
 
F

Frank Kabel

Hi
one way to identify your duplicates:
- create helper columns for both worksheets (lets say column E). enter
the following formula
=A1&B1&C1&D1
copy this down
now on your second sheet enter the following formula in F1
=IF(COUNTIF('sheet1'!$E$1:$E$10,E1)>0,"Duplicate","")

now you can filter the data for this columns and delete theses rows
 
P

Paul Browne

Thanks for the response Frank,

I'm not sure I've explained this well enough so here it goes again!!

Sheet1 from Sheet2 = Desired Result
a a a
a a a
b a b
c a
b
b
c

so as you can see only 2 of the 4 a's in the second column should be
deleted...

I think the first solution offered will place a "duplicate"flag beside
all 4 a's instead of reailising there are only 2 matches in the first
sheet.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
F

Frank Kabel

Hi
try the following formula in a helper column in sheet 2 (seems you have
only one column of data. If you have more columns concatenate them as
suggested in the first post):
=IF(COUNTIF($A$1:$A1,A1)<=COUNTIF('sheet1'!$A$1:$A$1000,A1),"","can be
removed")
copy this formula down
now filter for this columns and delete all rows which have the text
'can be removed' in this helper column
 
P

Paul

Thanks Frank, that will work fine.
Only problem now is that the values in question are 19 numbers long and excel sees;

123456789101112131415
the same as
123456789101112131400
and
123456789101112131402 etc

but that may be another thread.
many thanks to all that replied....my first time posting and i'm impressed.

Paul
 
F

Frank Kabel

Hi Paul
try converting the numbers to text ('Format - Cells - Text'). Excel
only supports 15 digits for numbers.
 
Top