+ Reply to Thread
Results 1 to 16 of 16

top 3 and last 3 values in a table should return row headers concatenate with comma

  1. #1
    Forum Contributor
    Join Date
    06-04-2011
    Location
    Hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    108

    top 3 and last 3 values in a table should return row headers concatenate with comma

    Dear Experts / Members,

    I have a table with values (rows and columns), from the table I have to find top - 3 which should return their row heading with in a cell concatenated with comma for the 3 values and also for last 3 values.

    Tried with large, index, match, offset but, no luck.

    Please find attached the image for better understanding.
    Untitled.png

    Thanks in advance
    Thanks & Regards
    Chaitanya A

  2. #2
    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: top 3 and last 3 values in a table should return row headers concatenate with comma

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context. Pictures are rarely much use and not many of us are prepared to recreate a workbook for testing when you already have one.
    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.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: top 3 and last 3 values in a table should return row headers concatenate with comma

    Many contributors are not able to view .png files on this forum due to software incompatibilities with some browsers. It would be better if you attached a sample Excel workbook.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    Pete

  4. #4
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: top 3 and last 3 values in a table should return row headers concatenate with comma

    Deleted!!!!!!!!!!!!
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  5. #5
    Forum Contributor
    Join Date
    06-04-2011
    Location
    Hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    108

    Re: top 3 and last 3 values in a table should return row headers concatenate with comma

    Dear experts,
    My apology for the inconvenience and or not followed the forum rule. Will make sure, from next time it won't repeat.

    Please find below the table and the output.

    Source Scale-1 Scale-2 Scale-3 Scale-4 Scale-5 Output
    Wave 77 94 77 77 79 Best 3 Scale-2, Scale-3, Scale-1
    Rock 76 76 76 76 76 Worse 3 Scale-2, Scale-3, Scale-5
    Build 76 76 93 76 76
    N-wave 89 70 70 89 70
    N-build 72 72 72 72 72

    I couldnt able to attach a file, please guide.

  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: top 3 and last 3 values in a table should return row headers concatenate with comma

    Quote Originally Posted by shukla.ankur281190 View Post
    Deleted!!!!!!!!!!!!
    Does that mean you have a solution? In which case would you care to share it with the rest of the forum members?

  7. #7
    Forum Contributor
    Join Date
    06-04-2011
    Location
    Hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    108

    Re: top 3 and last 3 values in a table should return row headers concatenate with comma

    I find how to upload the file, once again please accept my apology for wasting many experts time.

    I have attached the workbook.

    Thanks in advance.
    Attached Files Attached Files

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: top 3 and last 3 values in a table should return row headers concatenate with comma

    Getting your question to this point has made my brain hurt. I haven't even tried to concatenate the results.

    Can you live with this? They are array formulae.



    Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly braces yourself - it won't work...

    No doubt a VBA person will now come along, write 10 lines of code and make everything perfect. But this is a start???!!!
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  9. #9
    Forum Contributor
    Join Date
    06-04-2011
    Location
    Hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    108

    Re: top 3 and last 3 values in a table should return row headers concatenate with comma

    Hi Glenn,

    I am sorry for that.
    You are awesome with the formula, Mr. Glenn. No words to express the gratitude.
    To say frank, I am not that good at the formulas so, understanding the solution you have provided will have crack my mind.

    Thanks, Glenn.
    As you said, if someone comes up with coding that too helps.

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: top 3 and last 3 values in a table should return row headers concatenate with comma

    Enjoy getting a sore brain. I am away from my PC for a while. I just thought of a possible problem. Is it possible to have two equal values in the same COLUMN? If so, I will need to make a modification.

  11. #11
    Forum Contributor
    Join Date
    06-04-2011
    Location
    Hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    108

    Re: top 3 and last 3 values in a table should return row headers concatenate with comma


    Glenn, as of now my criteria is to have 3 unique results in one cell. What you have asked is out of box for me. But, it sounds cool to have equal values in same column. Hope, this time you won't hurt your brain.

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: top 3 and last 3 values in a table should return row headers concatenate with comma

    My mistake. there's no issue here. It's working fine for highest and lowest 3, irrespective of whether duplicates are in columns or rows.

    See attached file...
    Attached Files Attached Files

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: top 3 and last 3 values in a table should return row headers concatenate with comma

    No VBA Guru response do far. Here's a cheat to get to the same place....
    Attached Files Attached Files

  14. #14
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: top 3 and last 3 values in a table should return row headers concatenate with comma

    You can use this code for that if you want to avoid duplicates :
    Please Login or Register  to view this content.
    Last edited by sanram; 09-12-2016 at 07:35 PM.

  15. #15
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: top 3 and last 3 values in a table should return row headers concatenate with comma

    If you don't want to avoid the duplicates, then you can combine the array formulas provided by Glenn Kennedy :
    For Top 3:
    Please Login or Register  to view this content.
    For Below 3:
    Please Login or Register  to view this content.

  16. #16
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: top 3 and last 3 values in a table should return row headers concatenate with comma

    Also the code without avoiding the duplicates :
    Please Login or Register  to view this content.
    Last edited by sanram; 09-12-2016 at 08:11 PM. Reason: Wrong code added before

+ 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. [SOLVED] Need formula to return value from table based on varying order of column headers
    By ncpcpa in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-13-2015, 02:44 PM
  2. How to return column headers for where certain values appear in each row
    By gc-spurs in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 03-16-2014, 07:05 PM
  3. Finding the lowest 2 values across two rows, return row headers
    By ronanmagee in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-04-2013, 06:08 AM
  4. Return Headers Based On Two Values
    By just2cruz in forum Excel General
    Replies: 6
    Last Post: 06-16-2013, 08:31 PM
  5. Return Headers Based On Two Values
    By just2cruz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-16-2013, 03:03 PM
  6. Concatenate matched values and separate by comma
    By scoffman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-15-2012, 10:36 AM
  7. Replies: 2
    Last Post: 05-09-2012, 01:30 PM

Tags for this Thread

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