Help with look up function (I will repost this 1st got mixed up)

Put in E1 the letter A. And in E2 the formula

=IF(ISNA(VLOOKUP(C2,$C$1:C1,1,0)),CHAR(CODE(E1)+1),VLOOKUP(C2,$C$1:E1,3,0))

and copy down as far as needed.

Aladin

: A1 CLIENT 1 : A2 CLIENT 2 : A3 CLIENT 3 : A4 CLIENT 4 : A5 CLIENT 5 : A6 CLIENT 6 : A7 CLIENT 7 : C2 12133 : C3 13323 : C4 10012 : C5 13323 : C6 19989 : C7 13323 : E2 B : E3 C : E4 A : E5 C : E6 B : E7 C

Mea culpa: Omitted to add that you have to sort your data by column C.

Almost perfect but in E2 and E6 I need the same letter

In E2 and E6 I need the same letter (B)with suggested formula; now I get B and D; all zip codes that are unique should get the same label (letter); thank you : A1 CLIENT 1 : A2 CLIENT 2 : A3 CLIENT 3 : A4 CLIENT 4 : A5 CLIENT 5 : A6 CLIENT 6 : A7 CLIENT 7 : C2 12133 : C3 13323 : C4 10012 : C5 13323 : C6 19989 : C7 13323 : E2 B : E3 C : E4 A : E5 C : E6 B : E7 C

Sorry Aladin but I can not sort; will mess up other things

Solution that does not require sorting

If you don't wish to sort you data (as my previous post required), you might try doing:

E1 =A (the letter A)

E2 =IF(ISNA(VLOOKUP(C2,$C$1:C1,1,0)),CHAR(MAX($F$1:F1)+1),VLOOKUP(C2,$C$1:E1,3,0)) [ copy down as far as needed ]

F1 =IF(ISBLANK(E1),"",CODE(E1)) [ copy down as far as needed ]

Aladin

It seems that it can't work; thanks alog anyhow

Aladin, it seems that this formula still can't return just one letter for all zip codes that are unique (both E2 and E6 should have B not B and D). Thanks alot anyway. : A1 CLIENT 1 : A2 CLIENT 2 : A3 CLIENT 3 : A4 CLIENT 4 : A5 CLIENT 5 : A6 CLIENT 6 : A7 CLIENT 7 : C2 12133 : C3 13323 : C4 10012 : C5 13323 : C6 19989 : C7 13323 : E2 B : E3 C : E4 A : E5 C : E6 B : E7 C If you don't wish to sort you data (as my previous post required), you might try doing: E1 =A (the letter A) E2 =IF(ISNA(VLOOKUP(C2,$C$1:C1,1,0)),CHAR(MAX($F$1:F1)+1),VLOOKUP(C2,$C$1:E1,3,0)) [ copy down as far as needed ] F1 =IF(ISBLANK(E1),"",CODE(E1)) [ copy down as far as needed ] Aladin

Re: It seems that it can't work; thanks alog anyhow

Aladin, it seems that this formula still can't return just one letter for all zip codes that are unique (both E2 and E6 should have B not B and D). Thanks alot anyway. :

Are you sure? I get for your zip codes

e1: A
e2: B
e3: C
e4: A
e5: C
e6: D
e7: C

The only difference between your expected set of letters and the above set concerns the value of e6. You say this should be the letter B, not D. Client 2's zip is 12133, client 6's is 19989. I do expect them to get different letters, don't you?

Or are there other problems?

ps. I can send you the file containing the formulas if you want to.

Aladin

No, all unique entries should get only 1 letter (B in our example)

I know that's confusing and difficult, I'm sorry to take your time but all entries (zip codes) that are unique should be considered by formula like zip codes that are duplicat so all unique zip codes should get just one letter (B in our case). Thank you again. : Aladin, it seems that this formula still can't return just one letter for all zip codes that are unique (both E2 and E6 should have B not B and D). Thanks alot anyway.

Does this work?

I know that's confusing and difficult, I'm sorry to take your time but all entries (zip codes) that are unique should be considered by formula like zip codes that are duplicat so all unique zip codes should get just one letter (B in our case). Thank you again.

I think that I can modify Aladin's suggestion to
get it working for you.

Have Cell E1 be:-
COUNTIF(C:C,C1)
and copy it down as far as you need.

Have Cell F1 be:-
A

Have Cell F2 be:- (here goes)
=IF(E2=1,IF(COUNTIF($E$1:E1,1)=0,CHAR(MAX(CODE($F$1:F1)+1)),VLOOKUP(1,$E$1:F1,2,FALSE)),IF(ISNA(VLOOKUP(C2,$C$1:F1,4,FALSE)),CHAR(MAX(CODE($F$1:F1)+1)),VLOOKUP(C2,$C$1:F1,4,FALSE)))

This is an array formula, so hit control-shift-enter to enter it.
Column E counts the number of times that the zip code appears.
In column F, F1 is an A. After that, if column E has a 1, then the formula in F looks to see if
the singleton zip codes already have a letter. If so, it uses that letter. If not, it uses the
next letter available. If column E is >1, column F looks if the zip code already has a letter,
and either uses a previous letter or assigns a new
one.

Obviously, if you have more than 26 zip codes,
you might want to start with 1 and replace
CHAR(MAX(CODE($F$1:F1)+1)) with MAX($F$1:F1)+1.

Good luck!

At last -- Re: No, all unique entries should get only 1 letter (B in our example)

I know that's confusing and difficult, I'm sorry to take your time but all entries (zip codes) that are unique should be considered by formula like zip codes that are duplicat so all unique zip codes should get just one letter (B in our case). Thank you again.

OK here we go:

E1 =IF(COUNTIF(C:C,""&C1)=1,"B","A")

E2 =IF(COUNTIF(C:C,""&C2)=1,"B",IF(ISNA(VLOOKUP(C2,$C$1:C1,1,0)),CHAR(MAX($F$1:F1)+1),VLOOKUP(C2,$C$1:E1,3,0)))
[ copy down this one as far as needed ]

F1 ==IF(ISBLANK(E1),"",CODE(E1)) [ copy down as far as needed ]

I enjoyed very much solving this indeed "confusing and difficult" problem.

Aladin

Thank you so much to both of you Aladin & Tim, it works fine now, thanks again