| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! ;) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Tricky Excel De-Dupe Problem
|
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Tricky lookup problem | Excel Discussion (Misc queries) | |||
| tricky sum problem | Excel Discussion (Misc queries) | |||
| Tricky array problem…. | Excel Discussion (Misc queries) | |||
| Tricky problem in Data validation - Excel 2003 | Excel Discussion (Misc queries) | |||
| how can i de-dupe email lists using excel? | Excel Worksheet Functions | |||
Linear Mode
