Macro to Pull a Name List and reformat in another cell

[00Able]

Would like a macro to copy all names in column Y and place them below starting in row A39, then convert those names into the RF format in B39, Keep in mind that Y could have 2 names or 22,354 names.

So the list of names would be rewritten in A39 and below
and then those names would be formatted in the RF format.

So if the name in Y2 is "DeRamus, Kristina"

then the name "DeRamus, Kristina" will appear in A39, and then will be formatted to say KDeRamusRF

So basically, First Name Initial, Last Name, then RF with no spaces or commas between, as shown in the examples.

[MarvinP]

Hi 00Able,
I have a short story for you.

One of my first computer jobs was to do Tech Support for a software company. We logged each call by state of caller. We had a list of states printed with their code next to them. Alabama was 01, Alaska was 02, Arizona was 03, etc. Every call we answered we had to look at this stupid sheet to convert the State to their 2 digit number. It was then that I decided, computers were supposed to make work easier for us instead of harder.

Computers should have give information to help us understand our language instead of converting our language to its.

This short story is to suggest you use people's real names instead of trying to trim, convert, truncate or merge them into something that humans need to figure out. My answer to this question is - don't do it.

[00Able]

I agree and am not happy about having to do this, my issue is that their are 2 different sources, that is feeding this data (which neither do I have the ability to change) and somehow I need to connect the dots. Unfortunately, the only source I have in the one report is RF names, and so I need to convert the names into RF's so I can automatically pull the other data...I hope that makes sence, if I had my way, they would use their employee ID...but alas I don't...

[00Able]

Also to provide some more detail, there are 2 reports being imported into this sheet. I need to extract some data from one report and put it into the other report in that format. That is why I said above, that the only field that is directly related to a person, is the RF Name.

[DonkeyOte]

You could use formulae for this but VBA wise one approach based on the limited sample might be:

Please Login or Register  to view this content.

this is one of a large number of alternatives (regexp etc) but should be pretty quick even with large datasets

[MarvinP]

Here is the code and attached to check it.

Please Login or Register  to view this content.

[00Able]

Thanks Guys they work as planned.