Find & Substitute Full Names with First Name

[servcoor]

Hi, all.

On a daily basis, I use a data table having 4 columns and up to 500 rows or more to create a pivot table. In my data table, one column contains user's full names. There may be 12 or more different names, and each name may appear dozens of times each throughout the table. Since the pivot table I create from this data table uses the names column for one of its Data columns, displaying the full names causes the pivot table to be wider than we would like. I want to substitute the full names with each user's first name only. Example: "Williams, Stephen R" would be displayed in my pivot table as "Steve", and so on.

Any assistance with this substitution would be greatly appreciated. I have attempted to use the SUBSTITUTE function, but so far haven't been successful.

Thanks!

 

[SteveO59L]

Tabulate your relationship, then use vlookup()

 

[servcoor]

Thanks, SteveO - I'm trying to apply your suggestion...I beg your patience, but I haven't figured out how to "tabulate" the relationship...I get that my tabulation needs to assign "Steve" to the name “Williams, Steven R” and so on and then use VLOOKUP to find every occurrence of “Steve” in my new table, but how would I set up that tabulation? Also, I’ve used VLOOKUP successfully when the value looked for occurred only once within the table, however I cannot figure out how to get it to work when the values occur multiple times. Every example I’ve found in online tutorials shows using VLOOKUP to find single occurrences of unique identifiers, not multiple occurrences.

I hope I’m clearly explaining my needs and questions.

Thank you for your attempts to help me.

 

[servcoor]

Update: ok, nevermind what I said before about not being able to get VLOOKUP to work for multiple occurrences of an identifier within the table. I got that. Sorry. So that function is ok.

The piece I'm still unable to make work is how to substitute a first name for any/all occurrences of the full name within the table. Example: if my original data table has 30 rows of "Williams, Stephen R" how do I automate the substitution of "Steve" for that full name? The number of rows containing that full name - and all of the other names in my table - will vary from day-to-day so I need each substitution to be automated. Find each of the full names, substitute with the person's first name only. I think this is what Steve-O meant when he said I need to tabulate my relationships, but I haven't figured out how to do that yet.

Thanks again!

 

[SteveO59L]

I think I would start by giving each staff member a unique ID. Then use that as the key value in your vlookup()

If you send me a pm with your e-mail, I will send you a worked example

 

[servcoor]

Thanks again, Steve-O. I did send the pm with my email addy.