How do I automatically attach data to terms I copy into excel?

Hello,

I will use baseball as an example for what I want to do.

I already have one spreadsheet where I have the names of all 30 baseball teams in column A, and where I've manually inputted each team's stats, such as batting average and runs per game, in column B through T. What I want to be able to do is, if I made a new spreadsheet where I randomized the order of the teams, to have their stats automatically attach to the correct corresponding teams. And not just randomized them, but pasted a randomized order of the teams from a website.

What I am trying to do is basically determine which "baseball team" has the most difficult schedule next year. Like i said I already have the stats inputted for all 30 teams. But what I want to be able to do is, in new spreadsheets, copy and paste each team's 2012 schedule from MLB.com into new spreadsheets, and for each team on the schedule, have their correct stats attach.

For example, say on my first spreadsheet, column A is "TEAMS," column B is "BATTING AVERAGE," and column C is "RUNS PER GAME." If I have the Boston Red Sox in spreadsheet 1 listed as having a 290% batting average in column B, and 5 runs per game in column C, then when I'm copying team schedules into new spreadsheets, every time Boston Red Sox shows up on a schedule, I want "290% batting average" and "5 runs per game" to automatically show up in column B and C on the new spreadsheet right next to the Boston Red Sox. I want this for every team. How do I achieve this? Not sure it's pertinent but I have more than just two stat categories, by the way.

Also, something to note, when you copy schedules from MLB.com, because there are home and away games, the opposition team names show up in both column A and B. Also time of the game gets copied in as well. So I would need a way to account for those things as well.

Thanks for your help!

Hi Excelatexcel and welcome to the forum,

VLookup may work for you but you might also use Index and Match.
See http://office.microsoft.com/en-us/ex...001154902.aspx

If you are using VLookup make sure you use the Range_Lookup value of FALSE so the teams don't need to be sorted. Read http://office.microsoft.com/en-us/ex...001154902.aspx about Range_Lookup and sorted.

I hope this helps.

How do I update a dummy sheet? I figured out how to do it in a new thread, but how do I add one to this existing thread?

Hi,

You don't need to Quote what we say as this is just extra stuff to read.

VLookup and Index Match will both work on your older Mac Excel. All of them are very basic Excel formulas.

To attach a file, click on "Go Advanced" and then the Paper Clip Icon above the advanced message area.

Thank you very much inayat. I do have a few questions.

You do say it's not the best way, so what is the best way? Is vlookup even the best way to do this whole thing?

Also for making, say, "Angels" getting copied and pasted in from the schedules turn into "LA Angels," is there a way to tell excel every time Angels gets copied in, to change it to angels?

Because I have to do this over and over and over again, probably 120 "schedules," so I'm looking for the best way. Anything I have to do manually is going to make doing this very difficult.

So my overall question would just be, what's the best way, and what's the fastest way to go about it. Like what are teh steps. Copy the schedule, fix the name problem and get rid of the dates and all that (which by the way how did you do that?), input the vlookup, or wahtever the steps are, I'm just looking for more specifics and more help on how to do this in mass as fast as possible. Also the dummy schedule I uploaded doesnt have names repeated, but a full "schedule" would have a team playing the yankees 10 times, boston 15, and so on. Is this a problem that the names are repeating and I want the stats to show up every time so I can add them? Like 15 boston games should be boston's stats multiplied by 15, so that when I divide the sum of everyone's stats by the total games, I can average the entire schedule out. Is this a problem or vlookup will put the stats next to boston every time, not just once?

Thank you again! I really appreciate this.

Also it says I cant convert the file format. Did you change the format? I cant open the file, so I cant see what you did. Is there any way to upload a non-converted version of the file? I downloaded the xml converter and all that but it's still not opening. If i cant see what you did in excel then the only way is if you'd be willing to explain what you did step by step (or what I should do) and then I can do it myself.

edit, in my own excel dummy sheet, the one I can open, I tried what you said about the names, and by correcting the names just in the left table to match what was copied in (which is much easier than changing every name in the schedules), i got vlookup to work in the batting average column, the first one. But, two problems. One, when I tried the same formula in the next column over, the runs per game column, I got N.A. And another problem, even for the batting average column, where vlookup put the right batting average next to team's names, because the schedule also copied and pasted in dates and tv channels every other line between the teams, that means every other line there was an "N/A". So it would be like, yankees = 3.9 batting average (for 390%) on one line, and then the next line down "October 21st on Fox Sports Network = "N/A". So teh problem I was having was, when I tried to do =sum ( ) for the batting average column, to add up the total batting averages the angels would face, I got "n/a" for my result. I cant add them up right now either because of vlookup, or Im guessing because the N/A's mess it up, right?

So I need a way to delete every other line that doesnt have a team name. How do I get out all the clutter like the dates and channels that copy in along with the team names? I also don't know how to ignore the angels, or of the two teams for each game in the schedule, how to make vlookup pick the non-angels team, or the team that's not directly next to it at all. Sorry to have so many questions but there's a lot I dont know how to do here which is why I need more help with it, and the deadline for this is coming up.

edit, I have added another dummy sheet, this one farther along. I have deleted "angels" from the data table altogether. Tell me if there is a better way, but I just searched for "angels" in the document using the replace function, and then replaced it with just a space. That seems to work, except the problem still persists that the vlookup only looks works for angel's away games, or in other words the games where the opposing team's name shows up in column H, right next to the vlookup, and not the names that show up in column G. Also you can see I tried to "sum" the batting averages right under the bold text saying the sum should be there, and I get an N/A Im assuming because of all the dates and stuff that get copied into that column too.

I think we're almost there i just need to know how to fix these few issues and then Ill be off and running on this project.

Hi Excelatexcel,

You ask some interesting questions. The best one is "How do I do this the BEST WAY?"
This is always a tough question when we can't decide what you think is the "Best".

When I first looked at your workbook you had Teams and Batting Averages in Column A and B. I realized that this was one of the "bad statistics". If a game goes to extra innings or has a lot of K's you really need hits against times at bats. Think of it like this. 10 hits with 50 at bats = 20% average. Next game is 10 hits with 100 at bats = 10% average. SO the average of 20% and 10% is 15% batting average for that team. OR is it 20 hits with 150 at bats = 13% average. When I saw you were thinking of averaging percentages I dropped your thread.

Now for some answers. Excel works best in Tables. See: http://contextures.com/xlExcelTable01.html

For VLookup to work you need data that is the same. LA Angels does not equal Angles, LA or LA Angels. You need the exact same spelling, including spaces between words, for them to match.

Look at my attached file on thread http://www.excelforum.com/excel-prog...om-a-list.html for a more accurate way to do stats. This method to me is a much better way, but perhaps not the best.

I just searched the net and found http://www.baseball-almanac.com/bstatmen.shtml that has many FREE example spreadsheets to do stats. Take a look and see which one you like best.

Hi Excelatexcel,

I'm sorry that I though it was baseball stats where you were trying to average percentages. I have this rant on people who average percents and think it is a good stat.

I have a few answers for you.
Normally problems with VLookup are because:
1. You don't have the exact same thing in the cells. You are looking for Angels and the range has Angel. Or you are looking for 123 (the number) and the range has "123" the text. Or you are trying to return the 4th column over when your range is only 3 columns wide. Or you have spaces before or in the middle or after what you are looking for. Bob Smith is different than Bob Smith as one has only one space and the other has two. Look at http://office.microsoft.com/en-us/ex...005209335.aspx and read it carefully. Make sure you are using "FALSE" as the last argument instead of TRUE.

To give a better answer I need a better example. You should show/attach what you are using for your copy. You say "get rid of all the dates and tv stations" so what the data looks like is important. It is a good chance the data is TEXT from the start and not numbers at all. You will need to convert them to numbers to be able to add them up.

I've seen stuff copied in to excel that has a CHR(160) that looked like a space. When we tried to do vlookup it gave back a #N/A because the lookup value in the range didn't match. You couldn't see the difference because chr(160) looks like a space to us humans.

See attached some examples and how they keep stats. I hope this helps. If not then let us look at some of your real data and tell what you want to do with it.

I expect the #N/A is because

Hi,
Your best bet for getting a quick and accurate answer is to post a very real sample of what you have and tell us what you expect to happen to it. Wanting the "best way" is not specific enought for us to help. Read post #5 to see how to attach a file. Read the forum rules to see what I suggested.

Hi Excelatexcel - I really want to help you but I really don't understand the question.

In your dummy attach on post #1 you have a formula in cell I2 that comes back with a #N/A. It does this because you are looking for the word "YANKEES" in column A. Column A DOES NOT have "Yankees" but does have "New York Yankees" These two cells do not match. This is why you get this error.

To have VLookup work corretly you need to have them match. I believe this was discribed in the links I suggested. I also remember saying be sure to make it an exact match by using "FALSE" as the last argument.

I do hope this explains why you are getting an error.

Here are some more examples
http://www.timeatlas.com/5_Minute_Ti...OOKUP_in_Excel
or watch the videos at
http://www.free-training-tutorial.co...n-vlookup.html

Marvin what don't you understand? Im trying my best to explain. I explained the three other problems I was having. Inayat already told me about the name problem and I fixed that already. I updated my own dummy spreadsheet and fixed that. I guess I should have uploaded a new one.

Here. I fixed the name issue. That is no longer an issue. If you look at the new dummy sheet, you will see the problems. I've used colors to explain really simply. Everything with an orange background should be working, but is not. This includes the vlookup for the away teams in the schedule, and for the vlookup for "runs per game." That all needs fixing. Everything in red I need a way to automatically delete every time I copy a schedule in. This includes the channels and dates.

So you can see, everything in orange, I still need help figuring out how to do. Everything in red I need to know how to automatically delete.

Sorry the colors in the data columns, ignore them there. They are meant to show everywhere in the schedule, but not the data. I will update the sheet now. Okay now the sheet is fixed.

Everything in orange needs fixing, everything in red I need a way to automatically delete every time I copy it into excel.

Okay now the sheet is really fixed, and it's still not working.

You should have said,
Using the attached sheet, why is cell J2 still giving me a #N/A.

The answer is (drum roll)
You are not using the VLookup formula correctly. Your formula in J2 is

Please Login or Register  to view this content.

This formula says to look at the value in I2 (which is 3.9) and find it in column A of the range specified. You don't have numbers in Column A, you have Team Names!!

That is only the first error. You want to return the Runs Per Gm which is the 3rd column over from the Team column.

In J2 try this formula

Please Login or Register  to view this content.

Note 3 changes from your formula.
1. Looking for H2 instead of I2
2. The range was expanded to include column C
3. You want the 3rd column over and not the second.

That should take care of the VLookup (I hope) and get some numbers in the Runs Per Gm column.

Now for the Red Rows that you don't want anymore......
I think you are going to have to delete these manually. BUT there is an easy way to do this.
First - put the word TEAM in cell H1 because blank cells are bad
Second click on F2 and sort the table by column F
All the red cells come to the top.
Select all the Red Cells that are now together and right click on the selection and click on DELETE. You want to move the remaining cells up.

That takes care of everything but the "automatically" part. To do this automatically you will need some VBA Code, but first you need to learn VLookup.

Thanks marvin. I realized I had b28 and not c29 which I just changed, but that by itself didnt change it.

Buttt, still not working. The new formula for column J is retrieving some values but not others, and the ones it does return are all wrong. For the Red Sox it's returning the Yankees runs per game stats (ironically), and stuff like that. And it's still returning nothing for the away teams.

In other words, teams that show up in column H, the vlookup seems to be working correctly for batting average in column I, and it seems to retrieving values--just the wrong ones, in column J. But for both batting average and runs per game, in column I and column J, they are returning nothing for the teams that show up in column G. So data is being retrieved for teams that show up in column H, some of it wrong data, but no data at all for column G. I will put the teams where data is not being retrieved at all in blue.

Edit, now it's working for runs per game the same as batting average, but it's still not retrieving any data at all from column G teams.

Also, when I sort by F (at the end of inputting also the formulas, right, not before?) and get rid of the red cells, you said to right click and delete, but then it asks me if I want to shift the cells up or down or whatnot. Also after i delete the cells, how do I sort it back to having the titles at the top?

The blue rows come back as #N/A because you are still learning VLookup.

See attached.

Hey Excelatexcel,

I remember a sentence way back on post number 1. The ultimate question was "who has the toughest 2012 schedule?"

I was wondering how you were going to calculate this. What criteria were you going to use to determine "toughest". Did you consider a home game easier than away? Did you consider who the pitcher was going to be for that game? Might you consider the distance traveled by the visiting team and/or time zone changes? Was there a different weight given for double headders? How about double headers and depth of the pitching staff? Perhaps you were going to use the past 5 year records for each opponent to determing these things.

Then I opened your workbook and you had two simple numbers. Team batting averge and runs per game. It looked like you were going to average batting percentages, and you know how I feel about that.

I pointed you to some other examples where all those real stats were kept but you simply wanted to know how to do vlookup.

Keep asking those hard questions and remember that there may be more involved than a few numbers.

I hope you are getting better at VLookup by now. There is a lot more to learn.

Marvin,

Dont worry about the math part of it, I know what Im doing there. It's just the excel part that I need help with. We're almost there i just need to know how to do the blue.

As for the stats and the toughest team, that was just a summation. You cant quantify the toughest team. Basically what Im going to do is rank each stats. i wont know who has the toughest schedule per se, but I will know which team faces, on the average, teams that score the most, teams with the highest batting averages. Basicalyl Im not going to combine the stats, Im going to rank each one of them and look at them individually. For example if I can figure out which team faces the best hitting teams (by runs per game), that will tell me their pitchers might be in for a difficult season. What you said about which pitcher pitches which game would be a huge problem there actually, except luckily Im not doing this for baseball. That would be a problem if I was doing this for something that rotated like that but im not. All my stats for what im doing would be "team" centric. Everthing would be a team stat, everyone plays every game. There aren't any variables like that so I can do it. And the way Im going to average each stat out for each team is just sum the vlookups for each category and average it out. And the reason I can average averages here is because it's on a per game basis. You couldn't add up every team's batting average and divide it by the total number of teams to see what the average batting average for the league is, because like you said some teams have more at bats than others. But when you're talking about, on a per game basis, what's the average batting average a team will be facing next year, you can average the average. When you do it on a per game basis you are still assuming every game is the same length, which is the only place for error. If one game goes 60 innings and it's against a really poor hitting team, that would change your pitchers performance for the better slightly over the season. But the margin there is small enough that Im still getting a near exact approximation.

So Im good on the math side of it, it's just excel. I really need help with that blue. It's the last thing. Tell me how you automatically made it work, or moved it to the next column. That and telling me how to sort back after I sort F, those are the two things that are left. Im just worried we've gotten so far and you're going to leave me hanging on the last thing when we're so close lol.

As for vlookup, I understand the $A$2:$C$29 part, the 2, or 3, part, and that it needs to be false. I think I understand everything I need to do it besides the blue part.

Ok, you can't move the visitor team to the right. New formulas:
In cell I2 put this:

Please Login or Register  to view this content.

and it cell J2 put this:

Please Login or Register  to view this content.

Pull both of them down to the bottom after sorting and deleting all the red cells like I told you how to do a few posts ago.

See sheet 1 for the formulas,
See sheet 2 for the final results - I copied sheet 1 to 2 manually to show the red cells gone.

Sorry Marvin I was away for a few minutes. Man I cannot tell you how much I appreciate your continued help here. Its been a really slow process with you doing 90% of the work and I really appreciate you sticking it out. Like really really appreciate. My project would be dead without you.

Now im back here, and I will look at the sheets you just uploaded. One thing i didnt see you mention is how, after I sort F to get rid of the red, do I get things back how they were? And when I delete the red cells, do I shift cells up, entire row, which do I pick?

Other than those questions I believe if everything works according to plan on the new sheets you uploaded then I should be set on this thread.

Also you mentioned sheet 1 and sheet 2, but I only see one sheet attached.

Update - new formula seems to work. You may have already done this in the sheet you didn't upload, but could you explain to me how this one formula works? This seems to be my money formula. I could spend hours trying and failing to learn vlookup in its entirety (looks very complicated), but I think i can live on just this formula, so if you teach me this exact formula i can do it. Like i said I understand the base parts of it with the money signs, and telling excel which column you want it to retrieve data from, but the new stuff I dont understand. The reason I should learn it is because when I do my project, there will be a lot more stats than just column B and column C, which means those extra stats will push the vlookup further to the right. It wont still be the exact same column so I should learn how to do it myself. Or I can try just, if it changes from column H to column Z, just change everywhere it says H to Z.

Hi Excelatexcel,

The last workbook I attached has a "Sheet2" on it. This is the second sheet I wanted you to see. What I've tried to show you on it is:
1. I put the word "Team" in H1 to make that group of cells a List/Tabel
2. Sorted by Column J to move all the #N/A to the bottom so you can select the group of cells you want to delete and remove them. You will choose Move Cells UP after you select Delete.

The formula in Column I is this:

Please Login or Register  to view this content.

Let me explain in english what it is doing.
I want to look at the teams in column A and return their batting averages. The Team names are either in Column G or H depending if they are a home or away team. If I try to lookup a team in Column H that is blank I get an error. So before I do the lookup I see if the cell is blank or not. The part of the formula that is

Please Login or Register  to view this content.

tests if the cell in column H (in the same row as the formula is blank. If it isn't blank then I use the team name in column H to lookup the batting average. If it is blank then I use the team name in column G to lookup the batting average.

You now have two different formulas to study to accomplish your goal. You need to understand the VLookup formulas 1st and the IF formulas second.

Look first at http://www.excelfunctions.net/ExcelIf.html to see the commas in the formula and how the "IF" sends the formula to the True of False parts of the function.

Then look at http://spreadsheets.about.com/od/iff...p-Tutorial.htm and go through all 8 steps to learn the If formulas workings.

I hope this gets you on your way to solving your problem and seeing how Excel can be a great tool for doing statistics.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

Originally Posted by catalyce

Hello,

I'd appreciate some help. In Column L there cells containing a variety of phrases, I need column M to show a number according to the phrase in the corresponding L cell. Column M looks like this

Column M
TRF
OVERTIME
REGULAR
NIGHT SHIFT
PREMIUM PAY

There are thousands of entries so the phrases are repeated. I need to insert a formula that says, if M2 = overtime, 5, if M2=night shift, 4 and so on, and the last part of the formula needs to be (if not any of the mentioned phrases),""

Please help me!

Do you have a data table? As in do you already have your data somewhere that already says, overtime = 5, night shift = 4, and all of that? And then the only issue is now u need it to automatically copy to the corresponding word?

If that's the case, you can try this. But Im not sure Im understanding you correctly as first you said you wanted the data in L2 to be looked up in M2, but then you said the data is in M2, so it would be copied into L2.

Anyway, if overtime and all of that is in M2, and you want the numbers to automatically be put next to them in L2, try this.

First, in column A, put all the terms you will be using. Put "Overtime" in A2, "Night Shift" in A3, and all the other "words" in column A. For this example we'll say it goes down to A50. Then next to each of the words, manually put in the number you want to correspond. So if Overtime = 5, and overtime is in A2, then you put "5" in B2, and so on.

That is your data table that excel will look up the data from later on.

Once you've done that, go back to column M where you have these words repeating a thousand times in different orders. In the next column over, column L, in cell L2, you put the following formula. Then you copy cell L2, and paste down as long as need to. If you have a thousand words repeating in M2, and it goes down to M1002, then you in column L you copy the formula down to L1002 also. Here is the formula.

=VLOOKUP(M2, $A$2:$B$50, 2, FALSE)

The only part of that formula you will need to change is the "50" part. I have B50 in there because in the example, we're saying your data table in columns A and B goes down 50 rows. But if it goes down farther or less, you change 50 to that number. So if it only goes down to column 25, you change "50" to "25." If it goes down to 100, you change 50 to 100. That's the one part you need to change.

If you want to understand how the formula works so you can mess around with it more yourself, here is what it means.

=VlOOKUP means that you want excel to look up data one place and automatically copy it to another place.

The parenthesis are where you tell excel what to look up exactly.

Where it says M2, that's telling excel to look at what's in that cell, and whatever it is, that's what you want excel to look up from your data table. So if in M2 you have "night shift," that tells excel to go find where it says "night shift" in column A as well, and copy the data that's attached to column A's "night shift" to M2's night shift. So if in column A30, for example, it says night shift, and column B30 it says "5," this tells excel to take the "5" attached to the "night shift" in column A30, and put it next to M2's "night shift" in L2. And once you copy the formula down the rest of the cells, it will make it so "5" gets copied to every "night shift" in column M.

And the way it knows to look in column A is because of the next part....

$A$2:$B$50 is essentially just telling excel where the original data table to look up data from is located. In this case the data table ranges from A2 to B50. Column A has the names, which are not what you want to look up but you have to include them in the data table so excel can match them with the names in column M, and B has the data you want.

And lastly, of the two columns A & B you told excel to look at, the way you specify which column exactly you want data looked up from to show up in L2 is with the "2". You have two columns specified, A and B, and you want the second one to the right, column B, to have data looked up, so you put 2. Note that this is not counting total columns on excel, although B also happens to be excel's second column. The number only counts out of columns you've highlighted. So if you'd told excel to look in column D and E instead of A & B, and you wanted column E to be looked up, you would still put 2, because out of D & E, E is second.

"False," I forget exactly what putting true instead of false does, but false always works for me, so just leave that.

Now, the last part, the "if not any of these names" part. I am not an excel expert so I cant tell you the perfect way to do this, but here is one way that should work.

You do nothing more formula wise. What will happen is if none of the names match your data table, you will get "#N/A" in those L2 cells. When that happens, now you can just replace all "#N/A" with whatever you want it to say. If you want all names that don't match to be "20", then you just replace all #N/A's with 20. The problem is that because it's showing #N/A because of a formula error, and not because you just typed in "#N/A" the replace function won't work as is. The way I personally get around this is I take the whole column, highlight the whole column, in this case L2, and then I copy it into a microsoft word document. What this does is it essentially takes your results, your numbers, but gets rid of the excel formula that got them there. Then after you copy it into word, you copy it back from word and paste it into column L over what you have. You will be left with the same numbers and results, just the formula will no longer be there. So you only do this after you don't need the formula anymore. But after you've copied it back from word, now you will be able to replace all the #N/A's.

All you have to do is go to the edit menu, replace, and type in "#N/A" in the top typing space, and to "replace all" of those #N/A's with whatever it is you want it to say, which you write on the second line.

This should work.

If you have any questions you can ask in my thread it's okay. I will try to help but Im not an expert. Let me know if this works.

Here you go, Ive attached a file for you. I did it for you, basically. You can see what I did in the actual sheet and then put your own data there or copy it for your sheet and data. I used the words you specified, and then for the words that don't match, I used "holiday." You will notice all your words, or all the words that are in the data table, their correct corresponding number shows up in column M (I was confused in my last post, it's column L with the names and column M with the numbers, not the other way around). The one word that isnt in the data table, holiday, brings up the #N/A. You will probably have more than just one word like this. That's where you need to copy the whole column into word and then back so you can replace the #N/A with whatever you want it to say there instead.

But that's it basically.

Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore Moderators' or Administrators' requests - note that this includes requests by senior members as well, if you are unclear about their request or instruction then send a private message to them asking for help. Do not post a reply to a thread where a moderator has requested an action that has not been complied with e.g Title change or Code tags...etc