Complex Formula to Find Doubles

[Spike9458]

Hi All,

I have been working with Excel 2003 and Chip Pearsons website to get some
of the formula help I need. I got a lot done I think, but here is where I am
at. I have two spreadsheets with contact data in them. One list is heavily
populated with email addresses, the other is not. I need to be able to
combine the two lists into one, and get rid of the duplicate entries where
each has the same email address. In the instance where a contact in one list
has a mailing address and an email address, and only mailing address in the
other, I need to be able to get rid of the one with no email address.

For instance:

Sheet1
ColA Column B
Column C
1 Joe Smith 123 Barnyard Rd Anytown, ST, 12345 [email protected]
2 Jane Doe
3 Tom Jones 444 Anystreet, Anytown, ST, 12345 [email protected]

Sheet2
1 Joe Smith 123 Barnyard Rd Anytown, ST, 12345 [email protected]
2 Jane Doe 234 Main St Anytown, ST, 12345 [email protected]
3 Tom Jones 444 Anystreet, Anytown, ST, 12345

Note that in Sheet1, Jane Doe has not street address or email address, and
in Sheet 2 Tom Jones has no email address.



After combining the two sheets into one - Sheet3 below, how do I weed out
the Jane Doe from row2, and the Tom Jones from row6?

Sheet3
ColA Column B
Column C
1 Joe Smith 123 Barnyard Rd Anytown, ST, 12345 [email protected]
2 Jane Doe
3 Tom Jones 444 Anystreet, Anytown, ST, 12345 [email protected]
4 Joe Smith 123 Barnyard Rd Anytown, ST, 12345 [email protected]
5 Jane Doe 234 Main St Anytown, ST, 12345 [email protected]
6 Tom Jones 444 Anystreet, Anytown, ST, 12345

One of my spreadsheets has 6500 entries, and the other has 4400, so you can
understand the need to somewhat automate this.

Thanks in advance,

--Jim

[Max]

Try this on a *spare* copy of your file ..

In Sheet3,

Insert a new header row

Put in D2: =AND(A2<>"",OR(B2="",C2=""))
Copy D2 down

Then do a Data > Filter > Autofilter on col D,
select TRUE from the droplist in D1
This will filter out the lines you want to get rid of

Select all the filtered lines, do a right-click > Delete
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Spike9458" <[email protected]> wrote in message
news:[email protected]...
> Hi All,
>
> I have been working with Excel 2003 and Chip Pearsons website to get
some
> of the formula help I need. I got a lot done I think, but here is where I
am
> at. I have two spreadsheets with contact data in them. One list is heavily
> populated with email addresses, the other is not. I need to be able to
> combine the two lists into one, and get rid of the duplicate entries where
> each has the same email address. In the instance where a contact in one
list
> has a mailing address and an email address, and only mailing address in
the
> other, I need to be able to get rid of the one with no email address.
>
> For instance:
>
> Sheet1
> ColA Column B
> Column C
> 1 Joe Smith 123 Barnyard Rd Anytown, ST, 12345 [email protected]
> 2 Jane Doe
> 3 Tom Jones 444 Anystreet, Anytown, ST, 12345 [email protected]
>
> Sheet2
> 1 Joe Smith 123 Barnyard Rd Anytown, ST, 12345 [email protected]
> 2 Jane Doe 234 Main St Anytown, ST, 12345
[email protected]
> 3 Tom Jones 444 Anystreet, Anytown, ST, 12345
>
> Note that in Sheet1, Jane Doe has not street address or email address,
and
> in Sheet 2 Tom Jones has no email address.
>
>
>
> After combining the two sheets into one - Sheet3 below, how do I weed
out
> the Jane Doe from row2, and the Tom Jones from row6?
>
> Sheet3
> ColA Column B
> Column C
> 1 Joe Smith 123 Barnyard Rd Anytown, ST, 12345 [email protected]
> 2 Jane Doe
> 3 Tom Jones 444 Anystreet, Anytown, ST, 12345 [email protected]
> 4 Joe Smith 123 Barnyard Rd Anytown, ST, 12345 [email protected]
> 5 Jane Doe 234 Main St Anytown, ST, 12345
[email protected]
> 6 Tom Jones 444 Anystreet, Anytown, ST, 12345
>
> One of my spreadsheets has 6500 entries, and the other has 4400, so you
can
> understand the need to somewhat automate this.
>
> Thanks in advance,
>
> --Jim
>
>

[Max]

After line:
> Select all the filtered lines, do a right-click > Delete

Remove the autofilter
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Spike9458]

Hi Max,

Thanks, I will try that. There is one more test I need to apply to the
list.

Per my last post, I should have realized before and apologize for the
inconvenience, but the two spreadsheets contain data that is about 6 months
apart in age. There is a column with expiration dates of each contacts
license, and I need to keep the more recent contact information if there is
a duplicate. So the additional column would be Column D (there are a lot of
other columns with various other data, but these are the key columns.

Sheet3
ColA Column B
Column C Column D
1 Joe Smith 123 Barnyard Rd Anytown, ST, 12345 [email protected]
2007-12-06
2 Jane Doe
2005-08-26
3 Tom Jones 444 Anystreet, Anytown, ST, 12345 [email protected]
2007-04-20
4 Joe Smith 123 Barnyard Rd Anytown, ST, 12345 [email protected]
2005-12-06
5 Jane Doe 234 Main St Anytown, ST, 12345 [email protected]
2007-08-26
6 Tom Jones 444 Anystreet, Anytown, ST, 12345
2005-04-20

My thoughts are to create a work column, and concatenate the name with the
email address to create unique entries. Jane Doe would be in the list once
with and once without the email address. Then copy the values to another
column, and delete the first work column. Then do a sort of the entire sheet
(about 10800 rows) with the first criteria being the column with names and
emails combined, secondary would be the dates. Not sure if it would work, or
if a formula in a filter or something might be more effective.

Thank-you for your thoughts and ideas.

--Jim


"Max" <[email protected]> wrote in message
news:[email protected]...
: After line:
: > Select all the filtered lines, do a right-click > Delete
:
: Remove the autofilter
: --
: Max
: Singapore
: http://savefile.com/projects/236895
: xdemechanik
: ---
:
:

[Max]

Tinker with this ..

Sample construct at:
http://cjoint.com/?bEdUHoeDob
Spike9458_wks_1.xls

In a copy of Sheet3,

Assume source data in cols A to D, from row1 down, viz.:
Name, Add, Email, Contract Date

Put
In K1: =IF(C1="","",D1-ROW()/10^10)
In L1: =INDEX(A:A,MATCH(LARGE($K:$K,ROW()),$K:$K,0))
Copy L1 to O1

In P1: =IF(ISERROR(L1),"",IF(COUNTIF($L$1:L1,L1)>1,"",ROW()))
In Q1:
=IF(ISERROR(SMALL($P:$P,ROW())),"",
INDEX(L:L,MATCH(SMALL($P:$P,ROW()),$P:$P,0)))
Copy Q1 to T1

Select K1:T1, fill down till the last row of source data
Format col T as dates to taste

Cols Q to T would return the desired results,
i.e. only the most recent unique names with email ids,
all neatly bunched at the top.

If necessary, select cols K to T,
do an "in-place" : Copy > Paste special > Values > OK
to kill the formulas / freeze the values
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Spike9458" <[email protected]> wrote in message
news:[email protected]...
> Hi Max,
>
> Thanks, I will try that. There is one more test I need to apply to the
> list.
>
> Per my last post, I should have realized before and apologize for the
> inconvenience, but the two spreadsheets contain data that is about 6
months
> apart in age. There is a column with expiration dates of each contacts
> license, and I need to keep the more recent contact information if there
is
> a duplicate. So the additional column would be Column D (there are a lot
of
> other columns with various other data, but these are the key columns.
>
> Sheet3
> ColA Column B
> Column C Column D
> 1 Joe Smith 123 Barnyard Rd Anytown, ST, 12345 [email protected]
> 2007-12-06
> 2 Jane Doe
> 2005-08-26
> 3 Tom Jones 444 Anystreet, Anytown, ST, 12345 [email protected]
> 2007-04-20
> 4 Joe Smith 123 Barnyard Rd Anytown, ST, 12345 [email protected]
> 2005-12-06
> 5 Jane Doe 234 Main St Anytown, ST, 12345
[email protected]
> 2007-08-26
> 6 Tom Jones 444 Anystreet, Anytown, ST, 12345
> 2005-04-20
>
> My thoughts are to create a work column, and concatenate the name with the
> email address to create unique entries. Jane Doe would be in the list once
> with and once without the email address. Then copy the values to another
> column, and delete the first work column. Then do a sort of the entire
sheet
> (about 10800 rows) with the first criteria being the column with names and
> emails combined, secondary would be the dates. Not sure if it would work,
or
> if a formula in a filter or something might be more effective.
>
> Thank-you for your thoughts and ideas.
>
> --Jim

[Max]

> Select K1:T1, fill down till the last row of source data

As a precaution, and for improved performance, we could set the calc mode to
Manual before proceeding with the above fill down. Click Tools > Options >
Calculation tab, check "Manual" > OK. Then do the fill. Press F9 to
re-calc, and when calculations are complete, kill all the formulas, then
re-set the calc mode back to "Automatic".
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Spike9458]

Hi Max,

WOW, this is great! I will post back once I get it into my spreadsheet.

--Jim

"Max" <[email protected]> wrote in message
news:[email protected]...
:> Select K1:T1, fill down till the last row of source data
:
: As a precaution, and for improved performance, we could set the calc mode
to
: Manual before proceeding with the above fill down. Click Tools > Options
>
: Calculation tab, check "Manual" > OK. Then do the fill. Press F9 to
: re-calc, and when calculations are complete, kill all the formulas, then
: re-set the calc mode back to "Automatic".
: --
: Max
: Singapore
: http://savefile.com/projects/236895
: xdemechanik
: ---
:
:

[Max]

You're welcome, Jim.
Trust it'll work for you ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Spike9458" <[email protected]> wrote in message
news:[email protected]...
> Hi Max,
>
> WOW, this is great! I will post back once I get it into my spreadsheet.
>
> --Jim