+ Reply to Thread
Results 1 to 6 of 6

join Several Columns of data into one and return the name of the rows

  1. #1
    Registered User
    Join Date
    07-16-2017
    Location
    norway
    MS-Off Ver
    365
    Posts
    46

    Question join Several Columns of data into one and return the name of the rows

    Hey
    I need help to find a good way, maybe in several steps to join several columns of data into one and return the name of the rows that are most to the left or right - depending on the data.

    I have this data spread out over several columns and several of the columns have the same numbers.
    I want to join them into only one column and return the row header for each.
    When there are the same number in several of the rows the one furthest to the left or right will be the one that give me the row header.

    example of the data and the right answer.
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,852

    Re: join Several Columns of data into one and return the name of the rows

    Please try

    =LOOKUP(9^9,A2:K2,$A$1:$K$1)
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-16-2017
    Location
    norway
    MS-Off Ver
    365
    Posts
    46

    Re: join Several Columns of data into one and return the name of the rows

    Thanks Bo_Ry this saved me a lot of time thanks! Great solution!

    edit it ditt not work from line 38 to 44 for example.
    Last edited by thedrinkerparadox; 09-23-2020 at 03:23 PM.

  4. #4
    Registered User
    Join Date
    07-16-2017
    Location
    norway
    MS-Off Ver
    365
    Posts
    46

    Re: join Several Columns of data into one and return the name of the rows

    Quote Originally Posted by Bo_Ry View Post
    Please try

    =LOOKUP(9^9,A2:K2,$A$1:$K$1)
    works up to line 38.
    for example between 38 and 44 .

    It should return D but returns E, should return A but returns E. and so on

    D E
    A E
    B E
    B E
    D E
    D E
    C E


    I was too fast to check this.

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,852

    Re: join Several Columns of data into one and return the name of the rows

    Please try

    =LOOKUP(1,0/FREQUENCY(0,1/(ABS(COLUMN(A2:K2)-6)+1)/(1+(A2:K2<>"")*10^9)),TRANSPOSE($A$1:$K$1))
    Attached Files Attached Files

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,964

    Re: join Several Columns of data into one and return the name of the rows

    Alternative:
    M2:

    Please Login or Register  to view this content.
    Drag down

+ 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] How to join rows of multiple columns vertically?
    By rcurious in forum Excel General
    Replies: 6
    Last Post: 07-11-2020, 01:40 PM
  2. Join rows and columns for graphs
    By desibird in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-04-2020, 03:45 PM
  3. [SOLVED] How to join two columns/lists of data?
    By hadamhiram in forum Excel General
    Replies: 7
    Last Post: 06-12-2015, 08:06 PM
  4. Join rows of data
    By nredman in forum Excel General
    Replies: 3
    Last Post: 05-26-2014, 04:59 AM
  5. [SOLVED] How to join a list of data from rows to a single cell.
    By kaknoche in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-24-2013, 08:05 PM
  6. Join various columns from two data sheets
    By Apple Ling in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-06-2009, 10:24 PM
  7. return multiple columns/rows of data in formula?
    By darksoda in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-01-2007, 09:19 AM

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