Looking to subtract one list of values from another

  • Thread starter Thread starter Paul
  • Start date Start date
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
 
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
 
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.
 
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
 
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
 
Hi Paul
try converting the numbers to text ('Format - Cells - Text'). Excel
only supports 15 digits for numbers.
 
Back
Top