This formula, by DLL or DO, has worked well for removing duplicates from a continuous list of strings.
However, when trying to apply it to a long list of web addresses, I got a catastrophic failure that required the 3 finger salute to clear.=LOOKUP(REPT("Z",255),CHOOSE({1;2},"",INDEX($A$2:$A$300,MATCH(TRUE,INDEX(ISNA(MATCH($A$2:$A$300,$C$1:$C1,0)),0),0))))
I traced it to the above two lines, well apart in the original list of an 1100+ row file.
The formula is in C2:C10 in the attached sheet.
Delete the tilde ("~") from the string in row 2 to see what happens, then delete the same in row 2.
I'm posting this as a warning to others manipulating lists of web addresses, but any insight to this behavior would be appreciated.
Hello Marcol,
Tilde characters can be a problem whenever wildcards are accepted as the MATCH function will treat a single ~ as an "escape character". One solution is to use SUBSTITUTE to "double up" any tilde characters so that Excel knows you want a literal tilde, i.e. change formula to this:
=LOOKUP(REPT("Z",255),IF({1,0},"",INDEX($A$2:$A$300,MATCH(TRUE,INDEX(ISNA(MATCH(SUBSTITUTE($A$2:$A$3 00,"~","~~"),$C$1:$C1,0)),0),0))))
regards, daddylonglegs
Last edited by daddylonglegs; 01-24-2012 at 11:47 AM.
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks