Return Multiple Column headings in a single Cell
Results 1 to 6 of 6

Thread: Return Multiple Column headings in a single Cell

  1. #1
    New Member
    Join Date
    Nov 2015
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Return Multiple Column headings in a single Cell

    Hi,

    I've a question with slight variation as it was posted in this thread.

    https://www.mrexcel.com/forum/excel-...-headings.html


    I need to return the column headings for each matching x in a row; to a single cell instead of separate cells.

    Thanks for your time!

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Return Multiple Column headings in a single Cell

    ABCDEFGHIJK
    1Mark 1Mark 2Mark 3Mark 4Mark 5Mark 6Mark 7Mark 8Mark 9Mark 10
    212xx45101091Mark 3,Mark 4
    35x6864985xMark 2,Mark 10

    Sheet1



    In K2 control+shift+enter, not just enter, and copy down:

    =TEXTJOIN(",",TRUE,IF($A2:$J2="x",$A$1:$J$1,""))
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    New Member
    Join Date
    Nov 2015
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return Multiple Column headings in a single Cell

    Thanks @Aladin Akyurek

    thanks for this formula, it solved my problem!

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Return Multiple Column headings in a single Cell

    Quote Originally Posted by qadeerahmed View Post
    Thanks @Aladin Akyurek

    thanks for this formula, it solved my problem!
    You are welcome. Thanks for the update.
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    New Member
    Join Date
    Nov 2017
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return Multiple Column headings in a single Cell

    Hi Aladin,

    I'm using EXCEL 2013, But when i try this it is not working.
    It gives below error "#NAME?"

    Plz help me with another formulas.

    Thanks


    Mark 1 Mark 2 Mark 3 Mark 4 Mark 5 Mark 6 Mark 7 Mark 8 Mark 9 Mark 10 Answer
    1 2 X X 4 5 10 10 9 = TEXTJOIN(",",TRUE,IF($A2:$J2="X",$A$1:$J$1,""))
    5 x 6 8 6 4 9 8 5 X #NAME?


    Quote Originally Posted by Aladin Akyurek View Post
    A B C D E F G H I J K
    1 Mark 1 Mark 2 Mark 3 Mark 4 Mark 5 Mark 6 Mark 7 Mark 8 Mark 9 Mark 10
    2 1 2 x x 4 5 10 10 9 1 Mark 3,Mark 4
    3 5 x 6 8 6 4 9 8 5 x Mark 2,Mark 10
    Sheet1

    In K2 control+shift+enter, not just enter, and copy down:

    =TEXTJOIN(",",TRUE,IF($A2:$J2="x",$A$1:$J$1,""))

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Return Multiple Column headings in a single Cell

    Quote Originally Posted by NITIN MANE View Post
    Hi Aladin,

    I'm using EXCEL 2013, But when i try this it is not working.
    It gives below error "#NAME ?"

    Plz help me with another formulas.

    Thanks

    […]
    Excel 2013 does not have the TEXTJPOIN function, hence the #NAME ? error. Try the following instead...

    Add the following VBA code to your workbook as a module, using Alt+F11...

    Function aconcat(a As Variant, Optional sep As String = "") As String
    ' Harlan Grove, Mar 2002
    Dim y As Variant
    If TypeOf a Is Range Then
    For Each y In a.Cells
    aconcat = aconcat & y.Value & sep
    Next y
    ElseIf IsArray(a) Then
    For Each y In a
    aconcat = aconcat & y & sep
    Next y
    Else
    aconcat = aconcat & a & sep
    End If
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
    End Function

    Then invoke...

    Control+shift+enter, not just enter:

    =REPLACE(ACONCAT(IF($A2:$J2="x",","&$A$1:$J$1,"")),1,1,"")
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •