remove duplicates that arent quite duplicates

[FreakyGirl]

hello

i have a list of names in a column (BB). most of them are the same name only reversed seperated by a "/".

ie: player1/player2 & player2/player1


i need to remove the duplicates of the names leaving only the 1st occurance of the name in column BC.


i have tried diff versions of countif, left, right, find, cell= cell in columns BC on to no avail.

can anyone please help?

thanks in advance for your time,
FreakyGirl

 

[Nebu]

Hi:

I am not completely sure what you are looking for. However, your countif formula in column BC was wrong, I have corrected it. Find the attached.

Thanks

 

[FreakyGirl]

Hi:

I am not completely sure what you are looking for. However, your countif formula in column BC was wrong, I have corrected it. Find the attached.

Thanks

hello.

ty for that fix.

let me see if i can explain better.

if you look at BB7 & BB28 you can see its the same value only reversed using a a "/" to seperate the names.

the same with BB13 & BB29. the same with BB20 & BB33. etc..

for any values like the above i need to keep 1 version in the BC column - preferably the one in the earlier rows. ie: BB7 over BB29

the logic is look in BB$2:BB2 & search for a match of the current cell in reverse order. if it finds one then "" if not, then BB(current cell#)

i have it as BB2 so that it wont look lower than the current cell so it will keep the 1st set of names but the 2nd will be ""

i have tried so many if then, count if, left, len, right, mid but nothing works.

also note that some will not be the same such as BB21 & BB32 & thats ok. i only want to hide the ones that are the same.

thanks,
FreakyGirl

 

[Nebu]

Hi:

I have added a helper column BC, Is this what you are looking for?

Thanks

 

[FreakyGirl]

Hi:

I have added a helper column BC, Is this what you are looking for?

Thanks

hello.

ty for your quick reply. however im using a version before 2007 so the iferror will not work.

the BC column shows up as #NAME in every row.

do you have another solution perhaps?

thanks,
FreakyGirl

 

[Chihiro]

You can change the formula in column BC to something like below.
In BC2:
=IF(ISERROR(IF(BB2="","",LOOKUP(2^15,SEARCH(AZ2,$BB$2:$BB$34),$BB$2:$BB$34))),"",IF(BB2="","",LOOKUP(2^15,SEARCH(AZ2,$BB$2:$BB$34),$BB$2:$BB$34)))

 

[bosco_yip]

You can change the formula in column BC to something like below.
In BC2:
=IF(ISERROR(IF(BB2="","",LOOKUP(2^15,SEARCH(AZ2,$BB$2:$BB$34),$BB$2:$BB$34))),"",IF(BB2="","",LOOKUP(2^15,SEARCH(AZ2,$BB$2:$BB$34),$BB$2:$BB$34)))

In avoid to repeat using LOOKP(), ISNUMBER+SEARCH() is general used in this example for the testing in Excel 2003 version.

=IF(BB2="","",IF(ISNUMBER(SEARCH(AZ2,BB$2:BB$34)),LOOKUP(2^15,SEARCH(AZ2,BB$2:BB$34),BB$2:BB$34),""))

Regards
Bosco

 

[FreakyGirl]

You can change the formula in column BC to something like below.
In BC2:
=IF(ISERROR(IF(BB2="","",LOOKUP(2^15,SEARCH(AZ2,$BB$2:$BB$34),$BB$2:$BB$34))),"",IF(BB2="","",LOOKUP(2^15,SEARCH(AZ2,$BB$2:$BB$34),$BB$2:$BB$34)))

THANK YOU !!!

the formula didnt remove the reverse version of the values but rather duplicates to the ones already present. that allowed me to use another code of yours to get the unique values needed!

there is now light at the end of the worksheet tunnel.

im so happy im ready to do cartwheels!

truely awesome
FreakyGirl

 

[FreakyGirl]

In avoid to repeat using LOOKP(), ISNUMBER+SEARCH() is general used in this example for the testing in Excel 2003 version.

=IF(BB2="","",IF(ISNUMBER(SEARCH(AZ2,BB$2:BB$34)),LOOKUP(2^15,SEARCH(AZ2,BB$2:BB$34),BB$2:BB$34),""))

Regards
Bosco

thanks bosco for providing another solution!

love this place!
FreakyGirl