using rows of mismatched values across sheets

1. using rows of mismatched values across sheets

Hi all.

I have 2 sheets of baseball player data, 1 of projections and 1 of actual stats...each sheet has mostly the some, but some missing/added players in each sheet, so that Sheet1!AX in one sheet doesn't necessarily correspond to same player at Sheet2!AX in the other sheet.

I'm looking for a way to manipulate (divide, multiply, average, use some formula on, etc.) whole rows of individual player data for equal players across sheets. I've tried playing around with lookup formulas, but can't find a way to look up each player's name, find the whole row with that player's name in another sheet, and then do some mathy stuff to both rows to yield a different row with the same player name and some resulting stats.

If anyone can provide help with this, it would be much appreciated (and with my first post, I thank you for letting me into your community!).

2. Re: using rows of mismatched values across sheets

For any further explanation can you made some example workbook and upload it?

3. Re: using rows of mismatched values across sheets

Thanks for the suggestion...I looked into VLOOKUP and I don't think that will help with what I'm trying to do, at least not the meat of it.

I've uploaded a sample document...here you can see players A2:A7 in both sheets are the same (name-wise, not stats-wise), but the Actual Stats sheet has 3 more players not listed in the other sheet before listing another common player. I would like to be able to create a formula that deals with the numbers across the row for every shared player (for example, find the difference in those numbers).

I'd be happy to elaborate more if this is confusing to anyone. Thanks again.

4. Re: using rows of mismatched values across sheets

I think you want to do SUMPRODUCT, but I can't tell for sure... could you possibly upload a '97-'03 version of the file?

5. Re: using rows of mismatched values across sheets

Hmm...I don't think sumproduct helps out, whether I'm doing sums/products or not...I'm hoping that my end result will be a sheet that, format-wise, looks the same as the other sheets (with as many rows as the smaller sheet contains), only with different values in each cell containing stats (these values will be something like the difference or avg or whatever of the corresponding values for the same player across 2 sheets).

Here's the 97-03 version, thanks for taking a look.

6. Re: using rows of mismatched values across sheets

OK still not quite sure what you're trying to do, but in sheet 3 I made a row of the differences between predicted and actuals for "Accardo, Jeremy."

That should give you an idea how to use VLOOKUP to solve your problem.

The basic formula is:

The above formula would return the value in collumn B in a row contains "Accardo, Jeremy."

"Accardo, Jeremey" is the value you're matching, \$A\$2:\$K\$16 is the range of cells you're looking in, "2" is the number of collumns RIGHT of the first collumn in the range that you're matching (so if you start with col A, then 2 is col B, 3 is col C, and so forth).

For your purposes, add the name of the sheet in front of the range. Then you can perform mathematical operations among the differerent values VLOOKUP returns for idfferent sheets (in the attached example I only did subtraction)

7. Re: using rows of mismatched values across sheets

Great, I see how the VLOOKUP works. Because I'm looking to do this for everyone, I just replaced all the "Accardo, Jeremy" values with the selection of the cell where his name is and then dragged that down so I didn't need to type all the names in. Thanks for the help, I'll probably be back tomorrow to finish up figuring out what I'm doing!

8. Re: using rows of mismatched values across sheets

When I said LEFT I meant RIGHT... good luck!

9. Re: using rows of mismatched values across sheets

This is great, but what about when there is a name on one sheet but not the other sheet, like Baes,Danys?

10. Re: using rows of mismatched values across sheets

Welcome to the forum. Your post, however, does not comply with rule #2. Please read the rules and then start your own thread. You may reference this one in it if you feel it is necessary. Thanks.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1