Tricky Excel De-Dupe Problem

  • Thread starter Thread starter garyc
  • Start date Start date
G

garyc

Hi

I have a list of email addresses and wish to filter the duplicates
based on the first few characters only being duplicated....example....

(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)


So, I need to extract a shorter list with only one row for each
"person"

Anyone got any ideas?

Gary
 
Hi

I have a list of email addresses and wish to filter the duplicates
based on the first few characters only being duplicated....example....

(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)


So, I need to extract a shorter list with only one row for each
"person"

Anyone got any ideas?

Gary
Click to expand...

Add a column with the following formula:

=SUBSTITUTE(A1,MID(A1,FIND("@",A1)-12,12),"")

Then filter for unique entries based upon that column.
 
Add a column with the following formula:

=SUBSTITUTE(A1,MID(A1,FIND("@",A1)-12,12),"")

Then filter for unique entries based upon that column.- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks for the quick response.....

it almost works....

this is what I need to end up with....

COLUMN
1
COLUMN 2

Struggling with this one! ;)
 
Thanks for the quick response.....

it almost works....

this is what I need to end up with....

COLUMN
1
COLUMN 2


Struggling with this one! ;)
Click to expand...

Try this:

=IF(SUMPRODUCT(--(SUBSTITUTE($A$1:A1,MID($A$1:A1,FIND("@",$A$1:A1)-12,12),"")=
SUBSTITUTE(A1,MID(A1,FIND("@",A1)-12,12),"")))=1,A1,"")
 
Try this:

=IF(SUMPRODUCT(--(SUBSTITUTE($A$1:A1,MID($A$1:A1,FIND("@",$A$1:A1)-12,12),"­")=
SUBSTITUTE(A1,MID(A1,FIND("@",A1)-12,12),"")))=1,A1,"")- Hide quoted text -

- Show quoted text -
Click to expand...

Jeeeez - where do you learn this stuff - NASA? ;;)) I'll give it a
try! Thanks
 
Try this:

=IF(SUMPRODUCT(--(SUBSTITUTE($A$1:A1,MID($A$1:A1,FIND("@",$A$1:A1)-12,12),"­")=
SUBSTITUTE(A1,MID(A1,FIND("@",A1)-12,12),"")))=1,A1,"")- Hide quoted text -

- Show quoted text -
Click to expand...

Genius - worked first time!!
Thanks so much
Gary
 
Back
Top