+ Reply to Thread
Results 1 to 10 of 10

using rows of mismatched values across sheets

  1. #1
    Registered User
    Join Date
    06-17-2009
    Location
    Berkeley, CA
    MS-Off Ver
    Excel 2007
    Posts
    4

    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. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: using rows of mismatched values across sheets

    Try VLOOKUP instead of LOOKUP...

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

  3. #3
    Registered User
    Join Date
    06-17-2009
    Location
    Berkeley, CA
    MS-Off Ver
    Excel 2007
    Posts
    4

    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.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    06-09-2009
    Location
    Dubai, UAE
    MS-Off Ver
    Excel 2003
    Posts
    29

    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. #5
    Registered User
    Join Date
    06-17-2009
    Location
    Berkeley, CA
    MS-Off Ver
    Excel 2007
    Posts
    4

    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.
    Attached Files Attached Files
    Last edited by jslesser; 06-18-2009 at 02:36 AM. Reason: forgot attachment

  6. #6
    Registered User
    Join Date
    06-09-2009
    Location
    Dubai, UAE
    MS-Off Ver
    Excel 2003
    Posts
    29

    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:

    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)
    Attached Files Attached Files
    Last edited by tpillow; 06-18-2009 at 03:25 AM.

  7. #7
    Registered User
    Join Date
    06-17-2009
    Location
    Berkeley, CA
    MS-Off Ver
    Excel 2007
    Posts
    4

    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. #8
    Registered User
    Join Date
    06-09-2009
    Location
    Dubai, UAE
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: using rows of mismatched values across sheets

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

  9. #9
    Registered User
    Join Date
    11-27-2011
    Location
    Bensalem, PA
    MS-Off Ver
    Excel 2007
    Posts
    1

    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. #10
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    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.
    Life is about perseverance. Remember: today's mighty oak tree is just yesterday's nut that held its ground.

    If you like a post, please rate it with the scales icon (top right).
    If you feel really indebted, please consider a donation to charity, such as Feed the Children or Habitat for Humanity.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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