Compare 2 columns and align duplicates into same row

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AGOLFA - 22 May 2008 17:25 GMT

Hi
Im trying to sort out my email list.
After doing a mail out to the list of 1500 I found that about 220 had
bounced. I am now faced with trying to link up the bounced emails to the
persons they belong to.
I have 3 columns Fname/Lname column A,
Email Column B,
Bounced Emails Column C.
As columns A & B both are the same size (1500 rows) it's making finding the
220 in column C very time consuming.

I am hoping that I can find a formula or something that will let me find the
duplicates in column C and sort them to the same rows in column B rather than
just highlighting the duplicates which I have been able to do. But the
sorting to the correct row is proving to be very difficult.

Hope this is clear to you all what im looking for?
Thanx for your help in advance, Mike

Reply to this Message

Max - 23 May 2008 13:39 GMT

Try this play ..
Assume data in cols A, B, C (as per post) start in row2 down
Put in D2: =IF(ISNUMBER(MATCH(B2,C:C,0)),"x","")
Copy D2 down to the last row of data in col B. This flags email in col B
which match those in col C with an "x". Apply autofilter on col D, choose
"x", this gives you the filtered results (in cols A and B) that I think
you're after.

Signature

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

> Im trying to sort out my email list.
> After doing a mail out to the list of 1500 I found that about 220 had
[quoted text clipped - 13 lines]
> Hope this is clear to you all what im looking for?
> Thanx for your help in advance, Mike

Reply to this Message

AGOLFA - 23 May 2008 16:00 GMT

Thanx Max for your help, but it isnt working how I intended it to.
I wanted to be able to match up the Bounced emails in Column "C" with the
emails in column "B".
Your formula has marked the bounced emails with an "x" in column "D" and
when sorting hasnt moved them next to the duplicate email in Column "B".
Is there something im doing wrong or is the formula incorrect?
Sorry for the inconvience and thanx for your help in advance.

> Try this play ..
> Assume data in cols A, B, C (as per post) start in row2 down
[quoted text clipped - 20 lines]
> > Hope this is clear to you all what im looking for?
> > Thanx for your help in advance, Mike

Reply to this Message

Max - 23 May 2008 16:16 GMT

> match up the Bounced emails in Column "C" with the email in column "B".

Try this revised play
Assume data in cols A, B, C (as per post) start in row2 down
In D2:
=IF(ISNUMBER(MATCH($C2,$B:$B,0)),INDEX(A:A,MATCH($C2,$B:$B,0)),"")
Copy D2 to E2, fill down to last row of data in col C. This should return
cols A & B aligned with the bounced email in col C.

Signature

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

> Thanx Max for your help, but it isnt working how I intended it to.
> I wanted to be able to match up the Bounced emails in Column "C" with the
[quoted text clipped - 3 lines]
> Is there something im doing wrong or is the formula incorrect?
> Sorry for the inconvience and thanx for your help in advance.

Reply to this Message