How to extract non-duplicate text b/w sheets to a new sheet.

G

Georgie8888

Sorry if my explanation of the problem is confusing:

I have a document with three worksheets. The first worksheet has a list of
contacts. The second worksheet also has a list of contacts, some of which are
the same people in the first worksheet. I need to copy the contacts that do
not appear in the first worksheet into the third worksheet.

Is there are formula/macro/filter that could figure this out? I need to work
it out today so I'd be grateful to know anything, even longer methods (eg.
could I paste all the information of the two sheets on to one worksheet and
do some sort of search that will display results that only appear once?)

Thanks for any help you may provide.

Georgie
 
M

Max

One way ..

Assume source data is in col A in Sheet1, Sheet2,
with data from row2 down

In a new Sheet3,

Put in A2:
=IF(Sheet1!A2="","",IF(ISNUMBER(MATCH(Sheet1!A2,Sheet2!A:A,0)),"",ROW()))

Put in B2:
=IF(ROW(A1)>COUNT(A:A),"",INDEX(Sheet1!A:A,MATCH(SMALL(A:A,ROW(A1)),A:A,0)))

Select A2:B2, copy down to the last row of data in Sheet1

Col B will return the required results all neatly bunched at the top, ie the
contacts in Sheet1's col A not found in Sheet2's col A
 
G

Georgie8888

Hi Max!

Thanks so much for your quick reply. I'm having a problem with the formula
and I also have a question:

Excel is picking up a problem with "A2" and will not let me proceed,
although my data does start in this cell in the first two sheets.

As column A is "first name" and column B is "second name" I therefore need
the formula to check both combined (so that, for example, all fields with
"John" are not eliminated, but all "John Smith's" are.)

I'm obviously not very good with formulas. Do I change yours below to, eg:

=IF(Sheet1!A2+B2="","",IF(ISNUMBER(MATCH(Sheet1!A2+B2,Sheet2!A:A,0)),"",ROW()))

Will A2+B2 work or do I need a different symbol?

Thanks so much!

Georgie
 
M

Max

As column A is "first name" and column B is "second name" I therefore need
the formula to check both combined (so that, for example, all fields with
"John" are not eliminated, but all "John Smith's" are.)
Click to expand...

Ahh, I see ... Think we can use the same approach but we'd need to tweak the
criteria col to compare on 2 cols (cols A and B) instead of the previously
assumed single col A

Here's a sample construct to illustrate:
http://www.savefile.com/files/5474844
Extract non dupes betw 2 shts (2 col data).xls

The revised steps...

In Sheet3,

Placed in A2, then array-entered by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER)
=IF(TRIM(Sheet1!A2&Sheet1!B2)="","",IF(ISNUMBER(MATCH(1,(Sheet2!$A$2:$A$100=Sheet1!A2)*(Sheet2!$B$2:$B$100=Sheet1!B2),0)),"",ROW()))

Placed in B2
=IF(ROW(A1)>COUNT($A:$A),"",INDEX(Sheet1!A:A,MATCH(SMALL($A:$A,ROW(A1)),$A:$A,0)))
B2 copied to C2

Select A2:C2, fill down to cover the extent of data in Sheet1

Cols B and C will return the required results all neatly bunched at the
top, ie the contacts in Sheet1's cols A & B not found in Sheet2's col A & B

Adapt the ranges: Sheet2!$A$2:$A$100, Sheet2!$B$2:$B$100 to suit ..

And in case you want to do the converse as well
[ie retrieve contacts in Sheet2's cols A & B not found in Sheet1's col A & B]

In a new Sheet4,

Placed in A2, then array-entered by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER)
=IF(TRIM(Sheet2!A2&Sheet2!B2)="","",IF(ISNUMBER(MATCH(1,(Sheet1!$A$2:$A$100=Sheet2!A2)*(Sheet1!$B$2:$B$100=Sheet2!B2),0)),"",ROW()))

Placed in B2
=IF(ROW(A1)>COUNT($A:$A),"",INDEX(Sheet2!A:A,MATCH(SMALL($A:$A,ROW(A1)),$A:$A,0)))
B2 copied to C2

Select A2:C2, fill down to cover the extent of data in Sheet2

Cols B and C will return the contacts in Sheet2's cols A & B not found in
Sheet1's col A & B, with all results as before, neatly bunched at the top

(The above set-up essentially swaps the references: Sheet1 for Sheet2 & vice
versa)

Adapt the ranges: Sheet1!$A$2:$A$100, Sheet1!$B$2:$B$100 to suit ..

Do note that the special way of entering the array formula in A2 (in both
cases) -- pressing CTRL+SHIFT+ENTER to confirm the formula -- has to be
re-done each time should the formula be edited for whatever reason. You
should see curly braces { } inserted by Excel (don't type these braces!) if
the confirmation is done correctly. Once confirmed correctly, the array
formulas in A2 can then be copied down as per normal formulae. You'd get
wrong results if the formulas are not correctly array-entered.
 
M

Max

.. you should see curly braces { } inserted by Excel ..

In the formula bar, the curly braces will appear at both ends: {<formula>}
when the formula is correctly confirmed via CTRL+SHIFT+ENTER (CSE)

Use this as a visual check before you proceed to copy/re-copy the formulas
in A2 down, in both Sheet3 and Sheet4. It's all too easy to miss doing this
CSE part correctly <g>
 
G

Georgie8888

It worked! You legend.

Thanks!

Georgie

Max said:
As column A is "first name" and column B is "second name" I therefore need
the formula to check both combined (so that, for example, all fields with
"John" are not eliminated, but all "John Smith's" are.)
Click to expand...

Ahh, I see ... Think we can use the same approach but we'd need to tweak the
criteria col to compare on 2 cols (cols A and B) instead of the previously
assumed single col A

Here's a sample construct to illustrate:
http://www.savefile.com/files/5474844
Extract non dupes betw 2 shts (2 col data).xls

The revised steps...

In Sheet3,

Placed in A2, then array-entered by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER):
=IF(TRIM(Sheet1!A2&Sheet1!B2)="","",IF(ISNUMBER(MATCH(1,(Sheet2!$A$2:$A$100=Sheet1!A2)*(Sheet2!$B$2:$B$100=Sheet1!B2),0)),"",ROW()))

Placed in B2:
=IF(ROW(A1)>COUNT($A:$A),"",INDEX(Sheet1!A:A,MATCH(SMALL($A:$A,ROW(A1)),$A:$A,0)))
B2 copied to C2

Select A2:C2, fill down to cover the extent of data in Sheet1

Cols B and C will return the required results all neatly bunched at the
top, ie the contacts in Sheet1's cols A & B not found in Sheet2's col A & B

Adapt the ranges: Sheet2!$A$2:$A$100, Sheet2!$B$2:$B$100 to suit ..

And in case you want to do the converse as well
[ie retrieve contacts in Sheet2's cols A & B not found in Sheet1's col A & B]

In a new Sheet4,

Placed in A2, then array-entered by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER):
=IF(TRIM(Sheet2!A2&Sheet2!B2)="","",IF(ISNUMBER(MATCH(1,(Sheet1!$A$2:$A$100=Sheet2!A2)*(Sheet1!$B$2:$B$100=Sheet2!B2),0)),"",ROW()))

Placed in B2:
=IF(ROW(A1)>COUNT($A:$A),"",INDEX(Sheet2!A:A,MATCH(SMALL($A:$A,ROW(A1)),$A:$A,0)))
B2 copied to C2

Select A2:C2, fill down to cover the extent of data in Sheet2

Cols B and C will return the contacts in Sheet2's cols A & B not found in
Sheet1's col A & B, with all results as before, neatly bunched at the top

(The above set-up essentially swaps the references: Sheet1 for Sheet2 & vice
versa)

Adapt the ranges: Sheet1!$A$2:$A$100, Sheet1!$B$2:$B$100 to suit ..

Do note that the special way of entering the array formula in A2 (in both
cases) -- pressing CTRL+SHIFT+ENTER to confirm the formula -- has to be
re-done each time should the formula be edited for whatever reason. You
should see curly braces { } inserted by Excel (don't type these braces!) if
the confirmation is done correctly. Once confirmed correctly, the array
formulas in A2 can then be copied down as per normal formulae. You'd get
wrong results if the formulas are not correctly array-entered.

Georgie8888 said:
Hi Max!

Thanks so much for your quick reply. I'm having a problem with the formula
and I also have a question:

Excel is picking up a problem with "A2" and will not let me proceed,
although my data does start in this cell in the first two sheets.

As column A is "first name" and column B is "second name" I therefore need
the formula to check both combined (so that, for example, all fields with
"John" are not eliminated, but all "John Smith's" are.)

I'm obviously not very good with formulas. Do I change yours below to, eg:

=IF(Sheet1!A2+B2="","",IF(ISNUMBER(MATCH(Sheet1!A2+B2,Sheet2!A:A,0)),"",ROW()))

Will A2+B2 work or do I need a different symbol?

Thanks so much!

Georgie
Click to expand...
Click to expand...
 
Top