Looking to subtract one list of values from another
 

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

Looking to subtract one list of values from another
 

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


--
Regards
Frank Kabel
Frankfurt, Germany

Paul wrote:
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

Looking to subtract one list of values from another
 

Another source of information/techniques can be found at Chip Pearson's
site:

http://www.cpearson.com/excel/duplicat.htm
A page on dealing with duplicates and uniques.

--
Regards,
Tom Ogilvy

"Paul" wrote in message
om...
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

Looking to subtract one list of values from another
 

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!

Looking to subtract one list of values from another
 

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


--
Regards
Frank Kabel
Frankfurt, Germany

Paul Browne wrote:
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!

Looking to subtract one list of values from another
 

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



"Frank Kabel" wrote in message ...
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


--
Regards
Frank Kabel
Frankfurt, Germany

Paul Browne wrote:
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!

Looking to subtract one list of values from another
 

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

--
Regards
Frank Kabel
Frankfurt, Germany

Paul wrote:
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



"Frank Kabel" wrote in message
...
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


--
Regards
Frank Kabel
Frankfurt, Germany

Paul Browne wrote:
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!