+ Reply to Thread
Results 1 to 8 of 8

Pull back column heading based on ranking

  1. #1
    Registered User
    Join Date
    07-19-2016
    Location
    Baton Rouge, Louisiana
    MS-Off Ver
    2016
    Posts
    41

    Pull back column heading based on ranking

    I am trying to work through a formula that will pull back column heading values based upon the total of each particular column heading for a given row.

    In my attachment, "Shoulder" gets pulled back as the most common injury type based upon the data in Column A.

    What I then need is for the cell below where "Shoulder" populates (B12) to pull back the most common injury type from the data in C2:G8. So in the instance of shoulder, it should say "Strain" below "Shoulder". I also need it to pull back the second most common injury type, in the cell below that, which would be "Sprain" (these points are outlined in the example data). I also need this to be able to adjust based upon what is pulled back as the most common injury, as the "Shoulder" cell can change value depending on the team that is being looked at within the report.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    Re: Pull back column heading based on ranking

    I've added a total row across the bottom if that's OK (it can go anywhere really and you can hide the row if needed.

    You can see attached, but here's what I have:

    For B11, I have:
    =INDEX(B2:B8,MATCH(MAX(A2:A8),A2:A8,0))

    For B12, I have:
    =INDEX(C1:G1,MATCH(MAX(C9:G9),C9:G9,0))

    and for B13, I have:
    =INDEX(C1:G1,MATCH(LARGE(C9:G9,2),C9:G9,0))
    Attached Files Attached Files

  3. #3
    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: Pull back column heading based on ranking

    I can't help thinking that with a little change of layout to your data a Pivot Table would be a more elegant and simpler way of analysing this stuff.

    In Sheet2 of the attached I've changed your data layout, added a Pivot Table and added a Slicer.
    The PT sorts in order of magnitude of body type and within each body type the Injury type is sorted by Total and by Maximum.

    Use the Slicer if necessary to limit the PT to a particular Body Type, the most common of course is always the first one in the PT
    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.

  4. #4
    Registered User
    Join Date
    07-19-2016
    Location
    Baton Rouge, Louisiana
    MS-Off Ver
    2016
    Posts
    41

    Re: Pull back column heading based on ranking

    Quote Originally Posted by Gregb11 View Post
    I've added a total row across the bottom if that's OK (it can go anywhere really and you can hide the row if needed.

    You can see attached, but here's what I have:

    For B11, I have:
    =INDEX(B2:B8,MATCH(MAX(A2:A8),A2:A8,0))

    For B12, I have:
    =INDEX(C1:G1,MATCH(MAX(C9:G9),C9:G9,0))

    and for B13, I have:
    =INDEX(C1:G1,MATCH(LARGE(C9:G9,2),C9:G9,0))
    This is working, it is just not as robust as I need for the larger data sets I am actually working with. I redid some of the numbers in the attachment to show where it falls short. This is where I think the formula is somehow based in a lookup of the value in B11, I am just not sure what that lookup would be.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-19-2016
    Location
    Baton Rouge, Louisiana
    MS-Off Ver
    2016
    Posts
    41

    Re: Pull back column heading based on ranking

    Quote Originally Posted by Richard Buttrey View Post
    I can't help thinking that with a little change of layout to your data a Pivot Table would be a more elegant and simpler way of analysing this stuff.

    In Sheet2 of the attached I've changed your data layout, added a Pivot Table and added a Slicer.
    The PT sorts in order of magnitude of body type and within each body type the Injury type is sorted by Total and by Maximum.

    Use the Slicer if necessary to limit the PT to a particular Body Type, the most common of course is always the first one in the PT
    I am with you, I am just ultimately pulling these values to a sheet that will generate a more visual report. Is it possible to do lookups within that pivot table to pull back the same information? The pivot table will update dynamically, as I am aggregating similar data sets for a 20+ different teams, so their data sets will be vastly different, and I need the formulas to be robust to this

  6. #6
    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: Pull back column heading based on ranking

    The whole point of a PT is it cuts out the need to use formulae at all.

    That's why I suggested that you use the Slicer to select, in this case "Shoulder". If you hide the two columns of numbers G & H you'll be left with EXACTLY what you asked for, i.e. just two cells containing Strain & Sprain, (plus the 3rd most common injury).

    The stated requirement was for two cells to contain the result you identified. Just so long as your aggregated data set contains the same three column table I show, if you use a dynamic range name that automatically adjusts to the size of your changing data then the PT will be about as robust as you can get.

  7. #7
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    Re: Pull back column heading based on ranking

    Well, I wasn't sure if you meant to bring back to most injury type for Shoulder, or fall all body parts (hence why I added the total row). And as it turned out, the data you supplied had the same two injury types the most prolific for both Shoulder and Total. Anyway, here are a couple of ugly formulas that work the way I believe you want them to: (I assume these can be shortened, but they do work as is)

    B12:
    =INDEX(C1:G1,0,MATCH(MAX(OFFSET(C1,MATCH(B11,B2:B8,0),0):OFFSET(G1,MATCH(B11,B2:B8,0),0)),OFFSET(C1,MATCH(B11,B2:B8,0),0):OFFSET(G1,MATCH(B11,B2:B8,0),0),0))

    B13:
    =INDEX(C1:G1,0,MATCH(LARGE(OFFSET(C1,MATCH(B11,B2:B8,0),0):OFFSET(G1,MATCH(B11,B2:B8,0),0),2),OFFSET(C1,MATCH(B11,B2:B8,0),0):OFFSET(G1,MATCH(B11,B2:B8,0),0),0))

  8. #8
    Registered User
    Join Date
    07-19-2016
    Location
    Baton Rouge, Louisiana
    MS-Off Ver
    2016
    Posts
    41

    Re: Pull back column heading based on ranking

    Much appreciated!

+ 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. I need help creating a formula to pull back data and pricing based on tiers
    By bbeards2 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-19-2015, 01:36 PM
  2. [SOLVED] Find value that doesn't match and bring back column heading
    By Keelin in forum Excel General
    Replies: 20
    Last Post: 02-13-2015, 10:16 AM
  3. Replies: 3
    Last Post: 01-23-2014, 01:30 PM
  4. [SOLVED] Need code for macro to pull all data from column heading matching drop-down
    By cpfenninger in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-02-2013, 04:00 PM
  5. Calculate standard deviation for column based on column heading
    By mattadler22 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2013, 04:25 PM
  6. Replies: 9
    Last Post: 10-22-2012, 02:03 PM
  7. Newbie question: change column heading back to letter
    By dredwin in forum Excel General
    Replies: 1
    Last Post: 01-19-2010, 09:23 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