Macro to insert text based on column b

Microsoft Excel 2003 (full product)
July 26, 2010 at 06:27:06
Specs: Windows Vista
I am looking for a macro to assist me. If column B has specific names in it, then column A needs to have a specific letter assigned to each different persons name. For e.g. If B1 contains the name Julie Smith then A1 needs to automatically populate with the letter P. I will be using at least 50 names at once and they all will have a specific letter assigned to them. Any ideas how to do this ? Thanks to all in advance !

See More: Macro to insert text based on column b

Report •

#1
July 26, 2010 at 07:46:42
re: I will be using at least 50 names at once and they all will have a specific letter assigned to them

Maybe I'm missing something, but...

I hope you are not asking for a different letter for each of the 50 names, unless you are willing to settle for both upper case and lower case letters.

As there are only 26 letters in the English language, you can't have 50 different letters unless you use upper and lower case letters - and no more than 52 names.

Before we go any further, please clarify what it is you are trying to do.


Report •

#2
July 27, 2010 at 05:01:43
Very valid question. Not all the names will have a different letter. Ok let me give you the details.

I would like to assign only 6 letters: P, G, O, M, R & U.

The 50 names may fall under any of these 6 letters.

Let's say i want to assign Mary Smith, Jo Hunter, David Fort to the letter P. Jessica King, Chris Comet, Paul Duncan to the letter O and so forth.

Does this make a bit more sense?
Thanks for helping out ...


Report •

#3
July 27, 2010 at 05:10:50
Yes, that makes more sense, but now we need to know how to tell the macro (or formula) which letter to assign to which name(s).

You must have some sort of criteria that determines why Mary Smith, Jo Hunter and David Fort are designated as P.

If you are going to have to write code (or a formula) that hard codes every name and it's assigned letter, you might as well just type the letters in manually.

I think we still need some more details.


Report •

Related Solutions

#4
July 27, 2010 at 05:58:08
no there really isnt any criteria. i just want to be able to assign up to 8 persons to a particular letter and when i populate column b with the names, i want to be able to see the corresponding letters appear in column a for each name. shall i email the spreadsheet to you ?

Report •

#5
July 27, 2010 at 06:15:10
re: "when i populate column b with the names, i want to be able to see the corresponding letters appear in column a for each name"

The most efficient way would be to use VLOOKUP.

First, you need to create a table with each name and it's assigned letter someplace in your workbook. It can be in a different sheet, in hidden columns, etc.

e.g.

            I           J
1     Mary Smith        P
2     Jo Hunter         P
3     David Fort        P 
4     Jessica King      O
5     Chris Comet       O
6     Paul Duncan       O

In Column A, where you want the letter to appear, use something like this and drag it down:

=VLOOKUP(B1,$I$1:$J$6,2,0)

When you enter a name in Column B, VLOOKUP will look up the name in I1:I6 and pull the corresponding letter from J1:J6.

You should review the Excel Help file on VLOOKUP to get a better understanding of how it works.

To "hide" the #N/A errors you will get if there is no name (or a wrong name) in Column B, expand the formula to be:

=IF(ISNA(VLOOKUP(B1,$I$1:$J$6,2,0)),"",VLOOKUP(B1,$I$1:$J$6,2,0))

This will put "" (nothing) in Column A if the value in Column B is not found in Column I.


Report •

#6
July 27, 2010 at 07:30:12
DerbyDad03 you are the best !
Thank you so much !
It worked !

Report •

Ask Question