using rows of mismatched values across sheets

**jslesser** · 06-18-2009, 02:06 AM

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!).

**zbor** · 06-18-2009, 02:08 AM

Try VLOOKUP instead of LOOKUP...

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

**jslesser** · 06-18-2009, 02:24 AM

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.

**tpillow** · 06-18-2009, 02:30 AM

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?

**jslesser** · 06-18-2009, 02:34 AM

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.

**tpillow** · 06-18-2009, 03:01 AM

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:

Please Login or Register  to view this content.

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)

**jslesser** · 06-18-2009, 03:22 AM

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!

**tpillow** · 06-18-2009, 03:25 AM

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

**frankienoshow** · 11-27-2011, 03:48 PM

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

**darkyam** · 11-27-2011, 03:50 PM

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.

using rows of mismatched values across sheets

LinkBack

Thread Tools

Rate This Thread

Display

using rows of mismatched values across sheets

Re: using rows of mismatched values across sheets

Re: using rows of mismatched values across sheets

Re: using rows of mismatched values across sheets

Re: using rows of mismatched values across sheets

Re: using rows of mismatched values across sheets

Re: using rows of mismatched values across sheets

Re: using rows of mismatched values across sheets

Re: using rows of mismatched values across sheets

Re: using rows of mismatched values across sheets

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions