Same name in cells is formatted differently. How can I replace with a consistent format?

Hi Folks!

My first post, and I'm cautiously optimistic!

This is probably a tough one, and perhaps impossible. But I have to ask. I use excel a lot, but I'm not a whiz by any means. My uses are for relatively straightforward spreadsheets, but I can stumble through VLOOKUP and a few of the other basic excel functions.

I have four sets of data that I cut and paste into a spreadsheet each week.

The four sets of data are all for the same person, but they each have the “names” in a different format. Here are the four different "name" formats for a person named Adrian Peterson.

1. Peterson, Adrian MIN RB
2. A Peterson (MIN)
3. Adrian Peterson
4. RB Adrian Peterson, MIN

The "MIN" means the person is from Minnesota, and the RB is an abbreviation for his position.

There are about 300 individual names in these spreadsheets each week. So with the four formats, that makes about 1200 cells with just their names!

For each set of data, I want to extract certain information, but the different formats for the name make VLOOKUP and the few other things I know about excel very difficult and time consuming.

What I’d really like is some way that the computer will look for a common denominator, say "Peterson", and extract that in a consistent format. I'd love to use the first one above (Peterson, Adrian MIN RB) as my “master format”, because most of my data is in that format, but quite frankly, I'd settle for anything at this point!

Any suggestions would be greatly appreciated! Thanks in advance!

Frankie-ray

Upload a sample excel file with target output for understanding the real issue.

Thank you! I am busy most of today, but that is a wonderful suggestion, and I will do that later tonight. Thanks again. Frankie-ray

Wow! This looks like it could actually do it! I'm amazed, and very appreciative.

A question though...I am getting some "run time" errors, the initial data doesn't seem to be all inclusive, and it doesn't line up exactly.

I have attached a few screen shots of what I am seeing, as well as a copy of the actual spreadsheet I am using with the complete list of about 276 names. The screenshot shows the “extracted data” where 1) They aren't lining up correctly, 2) it’s not collecting the data for all of the names on the first worksheet, and 3) it’s not extracting the correct data for one of the four data points.
1. As far as the “lining up”, that is so minor, and I can just move the heading over.
2. It looks like it is doing the macro for only the “example” data that I included in the example spreadsheet I provided. When I added the other names, it doesn't seem to collect the requested data. Is there a way to have the data extracted for all of the names on the first worksheet? There are about 276 names for this particular spreadsheet. I hope to use the macro not only for this worksheet, but for several others that I have that include up to 400 names. Is there a way to make the macro “generic’ enough to gather the data when the number of names on the initial sheet varies from about 276 to about 400?
3. For the data shown, three of the “extracted data points” are absolutely correct, and that makes me very happy! The fourth data point isn't quite right, and I’m hoping it’s something I did in naming the worksheets or something like that?
a. An example is Tannehill (the third name on the screenshot attached), who has four data points (26, 26, 11, 25) extracted. The “26” is his ROS ranking from the ROS worksheet, so that is correct, albeit in the wrong column. (I don’t really care which column as long as the data is there…just pointing it out…).
b. The second number is “26”, which should be the “14” from Chad’s Rankings worksheet. So that one is incorrect.
c. The third number is “11”, and that is also absolutely correct from the “Ryan’s Rankings” worksheet.
d. Finally the fourth number “25” is also spot on for Tannehill’s age from Ryan’s Rankings spreadsheet.
You are so very close, and I am so excited. I really can’t believe this is possible! You must be one amazing programmer, and a very kind and generous person to take your precious time to help others. I am very impressed, in many ways! Thank you!
Frankie-ray

Dear xladept! This is incredible! I love it! I can't thank you enough!

I have four more questions for you.

1. Do you take "donations" as a thank you? I'd be honored to send some small amount to you or to a favorite charity as a gesture of appreciation for your work and for your time.

2. If I wanted to learn how to do this, to tweak the macro as necessary, how would I go about doing that? Is what you've done "titled" anything, where I could get some books or go to the local community college, and learn how to do, or at least adapt or modify, what you've done for me?

3. It turns out that the macro is about 75% perfect, but not quite 100%! It correctly gets the first, third, and fourth columns filled in with the data from the feeder worksheets, but it still has a problem with the second column, the "Chad's Rankings" worksheet. It puts a value in the appropriate cell, but it's not the correct one. If you're still up for it, I'd love for you to check it out and see if there is something I've done with titles, or labeling, or something that won't allow the macro to extract this one piece of data for each person listed? I've attached the "Dynasty 2" spreadsheet, and highlighted "Tannehill" to help you see an example. I looked to see if it was taking the number from right above or below the person listed, but I found some above, and some below. So I thought I'd check with the master one more time!

4. Finally, I wanted to try this with the largest group that I deal with. So I tried the macro with the "Going Deep" group, and got some similar results. Close, but not quite there? I attached that "Going Deep" example as the second spreadsheet in this post, to see if you could tweak whatever needs to be tweaked to have it work for that one too. It' so close...

If I was a bit smarter, I'd figure out a way to codify the code to overcome the little foibles, but I'm not there yet. But I'm fascinated by what you have done. It shows me that something like this really can be done, so now I need to figure out how to get better at this! You have not only helped me, but you have now challenged me! Whoo-hooo! I can't wait to learn more!

Thanks again! Frankie-ray

Cool! I just went to the Wounded Warrior website and made a donation. And by the way, what a fantastic group to send a donation to. We are indeed blessed in this country to have such wonderful men and women ensuring our freedom, both in the military and in law enforcement. I heartily endorse your choice!

In answer to your question, I think the spreadsheets are consistent. What I did, and hope to do in the future, is delete the information in the first four columns, and then paste in the new week's information. It's in the same format, but players move from one team to the other sometimes, so that's why I start from scratch each week.

Another thing I should have mentioned is that the three "feeder sheets" also change every week. So that's another thing I hope to do. I'll delete the information in the three feeder sheets each week, and paste in the newest set of data. Once again, since the format won’t have changed, and the titles of the worksheets won’t have changed, I'm hoping I can then run the macro and have updated information in my main sheet. I'll just rename the file each week with that week's unique date.

I can dream can't I!

Thanks again for your help.

Frankie-ray

Hello again!

As my dearly departed mother often said “Now we’re cooking with gas!”

I am once again very pleased!

There are just a few bobbles in the final macro though, so if you’re up for it, I’d love you to check it out one more time. If you've enjoyed about as much of this as you can stand though, then I’m good with what you've already sent me, and I’ll fill in the rest by hand as necessary!

I have attached one of the spreadsheets that I called “FORUM MACRO 11-14-13 FOR Going Deep” in case you wished to look at it. Here is what I found.

It seems that “later” in each of the sections, that the information from the final worksheet (Ryan’s Rankings) doesn't get extracted from that feeder sheet into the main collection sheet.

I have highlighted a few examples. I did them for Ertz (in yellow), Graham (in orange), Thomas (in green), Clark (in blue), and Dickson (in lavender). I’m sure you’ll immediately see the disconnect.

And this dream is slowly but ever so surely becoming a reality!!

Thanks again for your help.

Frankie-ray

PS – I added a VLOOKUP section over to the right, and an additional worksheet from which to gather some information such as won-loss records. At first it messed up the macro, but I was able to work through my additions one at a time until I discovered what had caused the problem. I re-positioned things and it all came back just fine!

Hi again!

As far as "cutting and pasting the code", I think I've been doing it correctly? Each time you have sent me a new version of the macro, I delete the earlier version of the macro from the spreadsheets, and also delete the previously extracted data from the collector sheets.

I then copy and paste the new code as you had directed in the initial submittal to me, hit "Alt F8" and on we go!

If I'm missing something there though, please let me know. I'm happy to work on what I do to figure out how to do it better, or at least more correctly!

I'm not sure what you mean by an “index miss” (“Hi Frankie, Another index miss:”), but I suspect that's the reason why the macro sometimes "skips" certain names on the lists. My plan is to analyze the code as best I can, to see if I can figure out why it goes to a certain point, and why it stops before it gets to the end of the names listed.

I'm very appreciative of your efforts and time. But I think I've taken way more of your precious time than any person should, so I'll bumble along with what I have. And I'm very pleased with what I have.

Thank you again!

Frank

PS - does it help you in any way if I provide positive feedback in the forum? I'm not sure how to do that, but I think I saw something earlier that explained how to do that.

Hi again!

This is Frankie-ray again. Over the winter and spring, by fantastic Macro (Frankie) that you created for me has deserted me! At least it no longer gathers up the data as it once did.

If you're still up for it, I'd love it if you'd let me send you what I have now, and see if you can easily tweak it to work again?

And I'm still a Wounded Warriors fan, so if that was still cool for you, I'd be happy to send in my "2014 donation" to our friends at Wounded Warriors!

Thanks in advance!

Frankie-ray

Originally Posted by xladept

Hi Frankie,

Thanks for the Kudo - a contribution to "Wounded Warriors" would be appreciated.

I'll take a look at your issues tomorrow - are the spreadsheets consistent??

Hello again and thanks!

I have attached two things. One is an Excel spreadsheet that "used to work" as far as the macro "frankie".

The other is a Word document with three "screenshots" to show you what is happening.

In the first of those 3 "screenshots", I highlighted columns F, G, H, and I to show where data goes once it is extracted from three of the tabs of the spreadsheet.

The second screenshot shows the screen "after" macro "frankie" is run. Those cells highlighted in yellow are the same as the first screenshot, and it appears that the macro is extracting data from the first two tabs. But in pink highlighting you can see that it is no longer extracting data from the tab "Ryan's Rankings".

The third screenshot shows what the other sections are doing. Basically, only the first section is extracting data and putting it into columns F, G, H, and I. For all subsequent sections, the cells remain "blank".

Subsequent to you putting together the original macro, I added "page breaks" between each of the 14 sections in this particular spreadsheet. I wonder if that has messed up the macro?

If you have any questions that I can answer, please let me know!

Thanks again!

Frankie-ray!

Whoo Hoo!

We're getting there again!

The new macro now seems to work perfectly, but only for the first of the 14 "sections" or teams?

The data is extracted for cells F3 to I29, but it does't extract data for any of the subsequent cells. Examples would be F45 to I69, F89 to I115, and so on.

If you can find the glitch, that would be awesome!


By the way, I saw a nice news article this morning about service members putting a small American flag at every headstone at Arlington National Cemetery. I thought it was very cool. There is also an additional brief video article about PetSmart pairing wounded veterans with animals...that is such a fabulous thing.

Freedom is not free...we owe so much to the brave men and women who keep us and our country safe....

Here is the link if you care to visit it....http://www.nbcnews.com/news/veterans...ington-n112706

Thanks again!

Frankie-ray

Aaahhh! Getting so close!

I have attached two additional screen shots. It's a Word document called "Screen Shot 2 for XLADEPT".

While the macro is now gathering data for all 14 sections, or teams, the collection is kind of haphazard, or incomplete, in that it isn't grabbing the information for all of the cells in each range. Please see the yellow highlighted areas on the screenshots.

Hopefully it's a small thing in there somewhere?

By the way, over the last several months, I've gone to several libraries to gather info on Visual Basic. It's a fascinating system, but with my limited knowledge and background, I'm still not coming close to understanding the language. My plan after we get this macro up and running again is to compare each different iteration of the macro with the next one, to see if I can figure out what you changed, why you changed it, and what difference it made in the results. A fun project for me!

Thanks XLADEPT!

XLADEPT...as I continue to stare at the screen, I wonder if the macro is working just fine? I think the "blanks" in the cells are where the specific data was not included in the various tabs. So please don't do anything yet...I have some work to do! I have to head out for some volunteer work now though, so may not get back for a couple of days. But I'm on it!

Thanks!

Frank

Hello again!

Got back late tonight. I'm a volunteer golf rules official, and I was the lead rules guy for a very cool junior ladies event. 17 years old and younger, 12 girls from Washington and Oregon have a match play competition on Friday, Saturday, and Sunday against 12 girls from British Columbia. It's so very cool, and I love doing that event each year!

Anyway, I was able to check into the macro tonight, and I do think there is a minor glitch. It appears that the macro is doing "every other" line, or row. I've attached two screen shots from two different files, and we can see that it is skipping every other row. I labeled it "Screenshot 3 for XLADEPT"

Interesting!

Frank

Helloooooo! Is there anybody out there? (to quote Pink Floyd!)

XLADEPT! If you still have it in you, I'd love to have you give this another once over...any ideas why it doesn't fill in all the cells, even when there is corresponding data in the worksheets?

Thanks!

Frank

Hello, and thank you so much again!


I have attached two documents.

One is a Word document called "Troubleshooting the macro 6-2-14". It is a screenshot of the areas that are not being filled in by the macro.

The second is a copy of one of the spreadsheets I've been using, and I called it "6-2-14 DEEP testing". I thought if you could actually see what I was using, it may be easier to perhaps determine why it isn't working as it once did?


The macro gathers the data perfectly from “Chad’s Rankings”.

In most cases, it does not gather the data from “Ryan’s Rankings”. Please see the light green highlighted areas in the Word document attached. It collected it for six cells (three names – Bernard, Vereen, and Freeman), but none of the others?

I must have messed up the "Ryan's Rankings" portion of the worksheet...that's all I can think of.

If you're still stumped as I am, I'd sure appreciate a peek to see if you can determine what I am doing wrong on this end!

Thanks, as always!

Frankie

Aaaaahhhhh!

I've met the enemy, and he is I!

After going through it over and over, I discovered that I had entered the data for "Ryan's Rankings" with a couple of additional rows on the top where I had put the date of the last update....not a good idea!

I appears to me that everything is wonderful in my world!

Thank you so very much...I think I'm there!

If the Wounded Warriors group is still one of your favorites, I'd love to make another contribution in your honor!

I also cannot remember how to say this forum item is once again "solved", but I'll figure that out in the morning! I'm exhausted! How about you?

Thanks again!

Frankie-ray

Thanks again XLADEPT!

I tried to check the star to "Add Reputation", but apparently I don't visit this site often enough for them to allow me to praise you once again! Instead, I get this notice..."You must spread some Reputation around before giving it to xladept again."

But I did just make another donation to the Wounded Warrior Project. You're worth it, they've earned it!

I may just check in here every now and then just to see if you have any other "deja vu's" going on!

My best to you.

Frankie-ray

Hello again XLADEPT! At least I hope you're still part of this forum!

The macro you created for me has served me well for several years, but just lately it has not worked.

Are you able to review it if I send it to you and do a bit of troubleshooting?

If you are up for it, I'll send you the macro and explain what it is and is no longer doing.

Thanks!

Frankie-ray

Wow! It's great to hear from you!

As background, you created a macro for me that does two things I love.

One is that it goes to my tab titled "2. ROS (Rest of Season Rankings)" and fills in column F of the first tab "1. Basic info I want to add to".

Two is that it goes to my tab titled "4. Ryan's Rankings" and fills in columns H & I of the first tab "1. Basic info I want to add to".


I have attached two example files.

The file with 16 teams still runs the macro, but it (and my other similar ones that still run) does it relativity slowly compared to the
original run time.

It takes 30-40 seconds now, when before it was 2 or 3 seconds, almost instantaneously! The actual "run time" doesn't matter to me,
but it seems to be herky-jerky when it does the macro. As if there is something not quite right with it now?

The file with 12 teams now stops after 10-20 seconds or so, and doesn't complete. It says "run time error", with data transferred only up to about
row 440. It doesn't go all the way to the end anymore.

I've tried to figure out the "Visual Basic", that I think you used for the macro, but it's beyond me. But if you have any suggestions on
research material you could direct me to, that I could study to become more familiar with your process, that would be awesome!

But regardless xladept, I think you are amazing!

Thank you so much!

Frankie-ray

Thanks again XLADEPT!

I think we're almost there again! The macro now fills out the entire three columns in both attachments, as well as in my additional spreadsheets that I use.

However it still has the "herky-jerky-ness" where it seems to fill out each cell individually, rather than "all at once", and thus is slower?

And I think that when I first pasted this new version of the macro into the first of my spreadsheets, that it worked as it used to....all three
columns filled in, only a couple of seconds to run it, no "herky-jerky-ness".

But then when I tried it again, the "herky-jerky-ness" was back, and so was the 20-30 seconds.

I'm not sure what I'm not doing correctly. I went in and deleted the previous macros, and pasted this new version in its place.

If it's not too much to ask, I wonder if you could hit "run' in these two attachments and see if it does that same "herky-jerky-ness" for you?

Thanks!

xladept, you are officially amazing! It now works like a charm!

Are you still supporting the Wounded Warrior Project? When I Googled them a few minutes ago, I saw that they'd undergone some changes at the top, maybe some "not so good" folks were ruining things for the rest of us? Anyway, if that is still your charity of choice, I'd love to head that way and make a donation? If not them, please let me know where you'd like a donation sent!

Thanks again xladept! You are so patient, have such a wonderful attitude, and are so knowledgeable. You've made my day once again!

Frankie-ray