Tricky Excel De-Dupe Problem
 

Hi

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

















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

Anyone got any ideas?

Gary

Tricky Excel De-Dupe Problem
 

Tricky Excel De-Dupe Problem
 

On Feb 5, 1:53*pm, Glenn wrote:
wrote:
Hi

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
















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

Anyone got any ideas?

Gary

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 -

Thanks for the quick response.....

it almost works....

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

COLUMN
1
COLUMN 2



THIS ONE TAKEN OUT AS IT IS EFFECTIVELY THE SAME PERSON AS THE ONE ABOVE












Struggling with this one! ;)

Tricky Excel De-Dupe Problem
 

Tricky Excel De-Dupe Problem
 

On Feb 5, 4:52*pm, Glenn wrote:
wrote:
On Feb 5, 1:53 pm, Glenn wrote:
wrote:
Hi
I have a list of email addresses and wish to filter the duplicates
based on the first few characters only being duplicated....example.....














So, I need to extract a shorter list with only one row for each
"person"
Anyone got any ideas?
Gary
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 -

Thanks for the quick response.....

it almost works....

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

COLUMN
1
COLUMN 2

* * * * * * * * * * * *
* * * * * * * * * *
* * * * * * * * * * THIS ONE TAKEN OUT AS IT IS EFFECTIVELY THE SAME PERSON AS THE ONE ABOVE
* * * * * * * * * * * * * *
* * * * * * * * * *










Struggling with this one! ;)

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 -

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

Tricky Excel De-Dupe Problem
 

On Feb 5, 4:52*pm, Glenn wrote:
wrote:
On Feb 5, 1:53 pm, Glenn wrote:
wrote:
Hi
I have a list of email addresses and wish to filter the duplicates
based on the first few characters only being duplicated....example.....














So, I need to extract a shorter list with only one row for each
"person"
Anyone got any ideas?
Gary
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 -

Thanks for the quick response.....

it almost works....

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

COLUMN
1
COLUMN 2

* * * * * * * * * * * *
* * * * * * * * * *
* * * * * * * * * * THIS ONE TAKEN OUT AS IT IS EFFECTIVELY THE SAME PERSON AS THE ONE ABOVE
* * * * * * * * * * * * * *
* * * * * * * * * *










Struggling with this one! ;)

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 -

Genius - worked first time!!
Thanks so much
Gary

Tricky Excel De-Dupe Problem
 

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 -

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


Right here! (in the newsgroup)