+ Reply to Thread
Results 1 to 13 of 13

Ranking Change - Compare Two Columns and Calculate Value Position Difference

  1. #1
    Registered User
    Join Date
    11-25-2015
    Location
    Philadelphia
    MS-Off Ver
    2011
    Posts
    6

    Ranking Change - Compare Two Columns and Calculate Value Position Difference

    Hi All, I am at my wits end and definitely in over my head with what should be a simple task for you. I've learned a lot about excel function in the past few hours, but I just can't seem to make it stick.

    I need to compare two columns of team rankings and calculate the change in position in absolute numbers and percentages. Assume these simple values below. The reality is that there will be 10k+ rows and not all values will be present in row b compared to row a (person falls out of ranking completely).


    Week 1 Rankings:
    1. Joe
    2. John
    3. Mike
    4. Bill

    Week 2 Rankings:
    1. Joe
    2. Mike
    3. Bill
    4. Steve

    I need:
    - Percentage Change in Position
    - Absolute Change in Position (number of spots)


    Any help is greatly appreciated.

    Shannon

  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
    52,917

    Re: Ranking Change - Compare Two Columns and Calculate Value Position Difference

    Hi, wel;come to the forum

    With something like this, it is often better to upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

    You dont show what your output would look like, so perhaps this will get us started...
    A
    B
    C
    1
    Week 1 Rankings:
    2
    1 Joe
    3
    2 John
    4
    3 Mike
    5
    4 Bill
    6
    7
    Week 2 Rankings: Prev Wk Pos
    8
    1 Joe
    1
    9
    2 Mike
    3
    10
    3 Bill
    4
    11
    4 Steve Not In Last Week

    C8=IFERROR(INDEX($A$2:$A$5,MATCH(B8,$B$2:$B$5,0)),"Not In Last Week")
    copied down

    Not sure how you want to calc the % change?
    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
    11-25-2015
    Location
    Philadelphia
    MS-Off Ver
    2011
    Posts
    6

    Re: Ranking Change - Compare Two Columns and Calculate Value Position Difference

    Wow, this is really great. Thank you. I've uploaded an example for you at:
    http://sprfrkr.com/rank_example.xlsx

    I too am not sure how to display a % change in rank.
    Your example was great, but if you could help me apply it to my four column example, that would be a huge help!

    Shannon

  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
    52,917

    Re: Ranking Change - Compare Two Columns and Calculate Value Position Difference

    Please upload your file to the forum. Not all members are able - or willing - to access file-hosting sites
    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.

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Ranking Change - Compare Two Columns and Calculate Value Position Difference

    Hi

    With Week 1 in columns A:B and Week 2 in D:E
    % change in say F2 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Absolute change in G2
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  6. #6
    Registered User
    Join Date
    11-25-2015
    Location
    Philadelphia
    MS-Off Ver
    2011
    Posts
    6

    Re: Ranking Change - Compare Two Columns and Calculate Value Position Difference

    Sorry, I didn't know we had our own file storage here. Attached. Thanks again for the replies.

    Shannon
    Attached Files Attached Files

  7. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Ranking Change - Compare Two Columns and Calculate Value Position Difference

    Hi,

    Did you try my example after adapting it for the empty c column and the divisor which in my example which did not have a column label in B? Hence you'd need to change the divisor to
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Specifically
    Your absolute change in E2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and % change in F2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    11-25-2015
    Location
    Philadelphia
    MS-Off Ver
    2011
    Posts
    6

    Re: Ranking Change - Compare Two Columns and Calculate Value Position Difference

    Thanks Richard! I took a look and it looks great! I'll know more when I apply my full data set, but it looks like you naiked it. I really can't thank you enough. Happy Thanksgiving (if in US), you really helped me out.

    Shannon

  9. #9
    Registered User
    Join Date
    11-25-2015
    Location
    Philadelphia
    MS-Off Ver
    2011
    Posts
    6

    Re: Ranking Change - Compare Two Columns and Calculate Value Position Difference

    Ahh, so close, I know it. When I applied the example given to my real world use case, it started to fall apart on me.

    1.) The old ranking vs new ranking seems to need the values inverted. Example: baidu.com went from #5 to #4 in the new ranking, so they went +1, not -1. I am not sure how to invert the % and absolute change values to reflect that.

    2.) Some are showing incorrect calculations somehow. #15 weibo.com in old rankings is #16 in new rankings, but shows a 30% and 9 absolute ranking change. That is strange! Similarly, #28 in new rankings t.co, shows a -1 when they did not exist in old rankings.

    Any pointers on this final leg of the problem solving would help me out on this Turkey Day!

    Shannon
    Attached Files Attached Files
    Last edited by ShannonS; 11-26-2015 at 09:57 AM.

  10. #10
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Ranking Change - Compare Two Columns and Calculate Value Position Difference

    OK

    The formulae contain the wrong row number reference. e.g.
    F2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    you can see this refers to E3 & D3. Since the formula is on row 2 it should of course refer to E2 & D2. Ditto with the G2 formula

    I can see an even better couple of formulae though
    F2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    G2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    11-25-2015
    Location
    Philadelphia
    MS-Off Ver
    2011
    Posts
    6

    Re: Ranking Change - Compare Two Columns and Calculate Value Position Difference

    This is great! It works exceptionally well. EXCEPT, for one issue which may be an Excel 2011 for Mac bug.

    I have applied the formula to the entire column by highlighting the column, entering the formula, and clicking "Control" and Enter. The formula applies fine to the first 26,XXX rows, but it does not return any values for the remaining rows (1M total). Selecting a field in the lower rows shows the formula is correct, but the calculation is not being performed. Field shows as 0. On latest Mac OS with 8GB of ram. I am thinking there must be some upper limit for MATCH. Thoughts?

    The file is 40MB, so I'm not posting the updated version here)

    Shannon

  12. #12
    Registered User
    Join Date
    03-27-2014
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Ranking Change - Compare Two Columns and Calculate Value Position Difference

    Holy cow I love you guys! I was trying to find the solution to a very similar type of report (ranking movement in an keyword analytics spreadsheet) and I think I've got it working, except for the null values where it's the first instance of that specific keyword showing up.

    -Adam
    Attached Files Attached Files

  13. #13
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Ranking Change - Compare Two Columns and Calculate Value Position Difference

    Quote Originally Posted by araimond View Post
    Holy cow I love you guys!
    We appreciate your confidence Adam and welcome to the forum. However our Rule 2 [URL="http://www.excelforum.com/forum-rules/642590-forum-rules.html"] asks that postings like this are not made in the thread of another member, you should start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

+ 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. Replies: 1
    Last Post: 07-02-2015, 06:57 PM
  2. Replies: 2
    Last Post: 06-28-2015, 03:09 AM
  3. Compare Two Columns and Highlight the Difference
    By VickyVykciV in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-25-2014, 01:21 AM
  4. [SOLVED] Calculate difference if there is a change in certain cell
    By thelegazy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-16-2013, 04:14 PM
  5. [SOLVED] 2 lists: how to compare and calculate difference in item numbers
    By chemoul in forum Excel General
    Replies: 5
    Last Post: 02-15-2013, 12:59 AM
  6. Compare Columns List difference
    By Wskip49 in forum Excel General
    Replies: 1
    Last Post: 02-11-2012, 10:53 PM
  7. Compare Columns and Find Difference
    By waki01 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-06-2008, 02:05 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