Populate one column with data based on another column.

**Ronnie_Greytruck** · 07-04-2013, 01:30 AM

I am new to excel and I am trying to add a salutation (e.g. Mr., Mrs. and Ms.) to every name in a list. The list sizes will change, so I need the amount of rows to go to the end of data and not to a set amount.

Column B = Salutation
Column D = First name

If D6 = Steve I need B6 to be Mr. or if D3 = Megan I need B3 to be Ms.

**FDibbins** · 07-04-2013, 01:33 AM

Hi and welcome to the forum

Im sure that Steve and Megan are not the only names you have? If they are, then in B6, copied down, maybe something like...

=if(D6="Steve", "Mr","Mrs")

**Debraj Roy** · 07-04-2013, 01:42 AM

Try this one too..

Formula:

Please Login or Register  to view this content.

@ FDibbins:

If you are planning for something interesting.. then visit here..

**codeslizer** · 07-04-2013, 01:56 AM

Hey Ronnie,
understanding your requirement, I believe you require two more columns - gender and marital status, so if married and female you can reflect the salutation as "Mrs", or else she'll be "Ms." and if male its "Mr."

So if your columns B, C, D, E and F are arranged in such order..

| Title | LastName | FirstName | Gender | Marital Status |

then in B2 we write the formula as

Please Login or Register  to view this content.

It checks for if FirstName and Gender if provided, coz we cannot set a title by just knowing someone's name. If yes, and if its Male, puts straight "Mr.". If Female, then waits for Marital Status to be provided. If yes, then checks for if its Single (Unmarried) or Married. You can further classify it to Divorced, Widow, etc.

Hope that helps!

**Ronnie_Greytruck** · 07-04-2013, 02:06 AM

I'm sorry but I should have said that there were many different names, I was just giving examples. I need a macro to go through the list and adding Ms. or Mr. to any names that I can think of adding so that I can speed up the process. Currently I am organizing the table from a --> z and then if the name is Bob I Enter Mr. and drag to fill it for all the Bobs. If the name is Beth I add Ms. and drag the autofill to the end of the Beths. I continue that all the way through the list. The Unisex names I just leave blank.

If there is more than one name in the cell, like if it has Marge and Rick, I need it to be blank so that I can go in and add Mr. and Mrs. in Salutation. I need for just the Man's name, in this case Rick, to be in First Name.

Also, we are using a customers list and can,t add gender or marital status. If I have to set up a if and then line for each name I will. i just need to know how to format it.

**FDibbins** · 07-04-2013, 02:12 AM

Maybe this suggestion will help?

In any column (even B) go down the list and enter 1, 2 or 3 depending on what you want the suffix to be. Then when you are done, find/replace 1/Mr 2/Mrs etc

It's still a laborious process, but that may make it a bit easier. You could even filter on a common name and add the 1, 2 etc (or Mr, Mrs etc) in mass to speed it up