If name in A1 make A3 show "1"

[Cyberpawz]

I am attempting to get a nested if loop to work in Excel but I am running into the dreaded #NAME error.

Here is what I am doing.

In Column A I have lets say three names repeat in various orders.

Moe A
Larry B
Curly C

What I want to do in Column C is show if Moe A, show 1, Larry B, show 2, Curly C, show 3.

So at the end I will have a mix of 1,2,3,3,2,2,1,1,1,1,2, going down column C.

How do I get this to work? I ultimately have it where there are over 80 individual names, but there are over 250 entries. Can someone please help me figure this out?

[NBVC]

Are you just looking to assign each person a number based on first one being assigned a 1?

If so, and assuming your names are starting in A2, then in B2:

=IF(COUNTIF($A$1:A2,A2)>1,VLOOKUP(A2,A$1:B1,2,0),MAX(B$1:B1)+1)

copied down.

[Marcol]

Or maybe this, if there are only three names?

Please Login or Register  to view this content.

[NBVC]

OP did mention:

I ultimately have it where there are over 80 individual names

[Cyberpawz]

Are you just looking to assign each person a number based on first one being assigned a 1?

If so, and assuming your names are starting in A2, then in B2:

=IF(COUNTIF($A$1:A2,A2)>1,VLOOKUP(A2,A$1:B1,2,0),MAX(B$1:B1)+1)

copied down.

Actually it is A1, A2, A3, etc for the names.

C1, C2, C3, etc are where the numbers will go.

[NBVC]

So after 26 unique names you start at B1, etc...?

[Cyberpawz]

So after 26 unique names you start at B1, etc...?

No, Column B has other info in it.

[NBVC]

I am not following, can you post a sample workbook showing various names and what you want to see in adjacent column?

[Cyberpawz]

Sure, here is the script.

[NBVC]

Yes, but what is supposed to be filled into those 2 blank columns?

[Cyberpawz]

I figured that would be obvious by the titles... sorry.

The first blank is what the company does, genetic testing, etc... I took it out because honestly it's a need to know basis.
The second blank is the ID, so starting at 1, going down to the end. Hence the need for the script. But the problem comes in that if there is going to be more than one line with the same name, how is it going to be identified by your script as already given a number?

[NBVC]

What I don't know is what kind of coding do you want to give the ID. If it is simply a number, so that all Stapla get a 1, all Perkin Elemer get a 2, all Biogen Idec get a 3, etc... then I gave you that formula (you just need to adjust for the extra column):

=IF(COUNTIF($A$1:A2,A2)>1,VLOOKUP(A2,A$1:C1,3,0),MAX(C$1:C1)+1)

If you don't want just a number, it might be more complicated, and we need to know some sample code you need to use as the company id.

if the company listing is only unique companies, and no duplicates, then:

=ROWS($A$2:$A2)

and if you want a generic code like Company1, Company2, etc, then try:

="Company"&ROWS($A$1:$A1) copied down

or

="Company"&TEXT(ROWS($A$1:$A1),"000")

if you want like: Company001, Company002, ... Company999...

[Cyberpawz]

What I don't know is what kind of coding do you want to give the ID. If it is simply a number, so that all Stapla get a 1, all Perkin Elemer get a 2, all Biogen Idec get a 3, etc... then I gave you that formula (you just need to adjust for the extra column):

=IF(COUNTIF($A$1:A2,A2)>1,VLOOKUP(A2,A$1:C1,3,0),MAX(C$1:C1)+1)

If you don't want just a number, it might be more complicated, and we need to know some sample code you need to use as the company id.

if the company listing is only unique companies, and no duplicates, then:

=ROWS($A$2:$A2)

and if you want a generic code like Company1, Company2, etc, then try:

="Company"&ROWS($A$1:$A1) copied down

or

="Company"&TEXT(ROWS($A$1:$A1),"000")

if you want like: Company001, Company002, ... Company999...

What I'm looking for is this:

In Column A you get the companies, such as you shown. In Column C you get their IDs, If I have to I will do a reference from another sheet if I need to. Here is a better concept of what I need.

Of course I am going to have to make it so that if there is nothing in the name field no ID # will show up either.

I think I may of answered my own question, I probably will have to create a reference loop, but I may be wrong.

Tab 1 has the log, tab 2 has the names with their identifiers. What I want is that when a name in Column A typed in, it is referenced by the other tab. Then outputs the correct number, if none is available it shows a blank field. Does this make any sense?

[NBVC]

That's better

You never stipulated that you had a lookup table.....

Anyhoot... in C2 enter formula:

=IFERROR(VLOOKUP(A2,'Individial Companies'!$A$1:$B$84,2,FALSE),"")

and copy it down.

[Cyberpawz]

That's better

You never stipulated that you had a lookup table.....

Anyhoot... in C2 enter formula:

=IFERROR(VLOOKUP(A2,'Individial Companies'!$A$1:$B$84,2,FALSE),"")

and copy it down.

Thanks, but I have a question, is there a reason you put a limitation on here? $B$84 wouldn't it be better to have an open ended option just in case more companies are added in later on?

Something like: =IFERROR(VLOOKUP(A2,'Individial Companies'!$A$1:$B,2,FALSE),"")

Or am I oversimplifying things?

[NBVC]

No, you can do that... sometimes people have other data beneath lookup tables or just want defined ranges...

[Cyberpawz]

Not in this case, this is going to be a very simple list, thanks for the help, I'll be asking more if I need any more help