MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


Posted by Veronica P. on December 21, 2000 2:36 PM

I have this situation:
A1 CLIENT 1
A2 CLIENT 2
A3 CLIENT 3
A4 CLIENT 4
A5 CLIENT 5
A6 CLIENT 6
A7 CLIENT 7

in column C, I have zip codes for clients (some zip codes are simillar)

C1 10012
C2 12133
C3 13323
C4 10012
C5 13323
C6 19989
C7 13323

in column E, I need a formula that will assign a different letter (or number) for clients that have the same zip code and for those that have different zip code another letter; example

E1 A
E2 B
E3 C
E4 A
E5 C
E6 B
E7 C

Thank you in advance.



Posted by Aladin Akyurek on December 21, 2000 3:03 PM

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

Posted by Aladin Akyurek on December 21, 2000 3:09 PM

: 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.

Posted by Veronica P. on December 21, 2000 3:17 PM

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

Posted by Veronica P. on December 21, 2000 3:18 PM

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

Posted by Aladin Akyurek on December 21, 2000 3:30 PM

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

Posted by Veronica P. on December 22, 2000 10:34 AM

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

Posted by Aladin Akyurek on December 22, 2000 12:10 PM

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

Posted by Veronica P. on December 22, 2000 12:21 PM

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.

Posted by Tim Francis-Wright on December 22, 2000 1:28 PM

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!

Posted by Aladin Akyurek on December 22, 2000 2:00 PM

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

Posted by Veronica P. on December 22, 2000 6:20 PM

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