+ Reply to Thread
Results 1 to 9 of 9

Consolidating rankings from 3 different sources

  1. #1
    Registered User
    Join Date
    07-22-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Consolidating rankings from 3 different sources

    I have 3 lists of names. Each list is populated by a very similar though not identical subset of names. In each list, every name has a ranking.

    I would like to create a sheet that contains each name listed only once, along with all 3 of it's corresponding rankings(with the rankings appearing to the right of the name).

    I would want to be able to periodically update the source of the 3 rankings which would then update the new sheet. Ideally I could arbitrarily scatter the names on the new sheet and have the rankings follow the name.

    Any suggestions? Thanks in advance for the help!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,051

    Re: Consolidating rankings from 3 different sources

    Hi and welcome to the forum

    I would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    07-22-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Consolidating rankings from 3 different sources

    Okay, thanks. The 3 lists of names are in sheets 2-4. I want the numbers next to those names to appear in the corresponding R1, R2, and R3 in sheet 1. I change the order of the names in sheet 1 occasionally so when I update sheets 2-4, I would like the rankings to update regardless of the position of the name in sheet 1.
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,051

    Re: Consolidating rankings from 3 different sources

    OK just so I'm clear on this...which sheet do you want to have the formula on?

  5. #5
    Registered User
    Join Date
    07-22-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Consolidating rankings from 3 different sources

    I want the formula on sheet1. Sheets 2-4 are just the source material. The end result of this would be values from sheets 2-4 appearing in place of "R1" "R2" and "R3" in sheet1.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,051

    Re: Consolidating rankings from 3 different sources

    OK see if this is what you want?

    In D3, copied down (for J and P as well), use this...
    =IF(C3="","",VLOOKUP(C3,'Ranks 1'!$A$1:$B$200,2,0))
    for the other 2 sheets, just change the sheet name

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,051

    Re: Consolidating rankings from 3 different sources

    I noticed that at least 1 name (Robert Griffin III, WAS) has an unprintable character in it, between the Griffin and the III. It looks like a space , but it's not a space that excel recognises - probably as a result of copying from another program.

    That was the only name I could find like that, you can just delete the 2nd "space" and put it back in again.
    OR if you will be importing data often, use this in rank 1 etc C1, copied down...
    =SUBSTITUTE(A62,CHAR(160),CHAR(32))
    you can then copy/paste values over the top of your original data

  8. #8
    Registered User
    Join Date
    07-22-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Consolidating rankings from 3 different sources

    First off, thank you. This is exactly what I was looking for. Second, I noticed the same thing about (Robert Griffin III, WAS) and I thought 1 outlier didn't really matter, but your alternatives worked well.

    Thanks again. This was a great first experience on this forum!

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,051

    Re: Consolidating rankings from 3 different sources

    Happy to help, and Im really really happy your 1st time was good for you

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. converting weekly rankings into monthly rankings
    By sfinns in forum Excel General
    Replies: 6
    Last Post: 02-09-2012, 08:51 AM
  2. Rankings
    By mitch_auspen in forum Excel General
    Replies: 7
    Last Post: 11-09-2011, 11:50 PM
  3. Going mad with rankings
    By bootjangler in forum Excel General
    Replies: 2
    Last Post: 08-01-2011, 06:12 PM
  4. Rankings
    By Jabbitar in forum Excel General
    Replies: 12
    Last Post: 03-27-2009, 12:05 AM
  5. Rankings
    By bud144 in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 11-24-2006, 01:26 PM

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