Return column header if X in cell

[orjanmen]

Hi,

Got this table:

...and would like this result:

If there is "X" in the cell, return value in 1:1. I have found multiple formulas to do so, but they only return first hit.

Anyone who can guide me in right direction on this one? Would appreciate it

 

[mumps]

I'm not sure about a formula but this macro should do what you want. It assumes your data is in Sheet1 and the result will be displayed in Sheet2. It also assumes that you are using merged cells in row 1.

Sub ReturnColHeader()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, Ary As Variant, r As Long, c As Long, lCol As Long
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    With srcWS
        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
        desWS.Range("A1").Resize(LastRow - 1).Value = srcWS.Range("A2").Resize(LastRow - 1).Value
        Ary = .Range("A1").Resize(LastRow, lCol).Value
        With CreateObject("scripting.dictionary")
            For r = 2 To UBound(Ary)
                For c = 2 To UBound(Ary, 2)
                    If Ary(r, c) = "x" Then
                        desWS.Cells(r - 1, desWS.Columns.Count).End(xlToLeft).Offset(0, 1) = Ary(1, c - 1)
                    End If
              Next c
           Next r
        End With
    End With
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Toadstool]

Hi Orjanmen,
I am assuming there is a typo and the heading in F1 should actually be in G1, in which case this should work.

Orjanmen.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			m, 31.01		t, 01.02		w, 02.02		th, 03.02		f, 04.02		sa, 05.02		su, 06.02
2	Jeff
3	Steven	j							x				x		x
4	Austin	n		n	x	n		n		n		n		n
5	Nicole						x
6	Emma			n		n		n			x		x
Sheet1

Orjanmen.xlsx
	A	B	C	D	E
1	Jeff
2	Steven	th, 03.02	sa, 05.02	su, 06.02
3	Austin	t, 01.02
4	Nicole	w, 02.02
5	Emma	f, 04.02	sa, 05.02
Sheet2

Cell Formulas
Range	Formula
A1:A5	A1	=Sheet1!A2
B1:E5	B1	=IFERROR(INDEX(Sheet1!$C$1:$O$1,AGGREGATE(15,6,COLUMN(Sheet1!$C$1:$O$1)-COLUMN(Sheet1!$B$1)/((Sheet1!$C2:$O2="x")),COUNTIF($A1:A1,"<>"))),"")

 

[orjanmen]

Thank you for your replies. I went for the formula, but thanks for the VBA alternative.

 

[Toadstool]

Thank you for your replies. I went for the formula, but thanks for the VBA alternative.

You're welcome. Glad you found a solution.

 

[nepakumar]

I'm not sure about a formula but this macro should do what you want. It assumes your data is in Sheet1 and the result will be displayed in Sheet2. It also assumes that you are using merged cells in row 1.

Sub ReturnColHeader()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, Ary As Variant, r As Long, c As Long, lCol As Long
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    With srcWS
        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
        desWS.Range("A1").Resize(LastRow - 1).Value = srcWS.Range("A2").Resize(LastRow - 1).Value
        Ary = .Range("A1").Resize(LastRow, lCol).Value
        With CreateObject("scripting.dictionary")
            For r = 2 To UBound(Ary)
                For c = 2 To UBound(Ary, 2)
                    If Ary(r, c) = "x" Then
                        desWS.Cells(r - 1, desWS.Columns.Count).End(xlToLeft).Offset(0, 1) = Ary(1, c - 1)
                    End If
              Next c
           Next r
        End With
    End With
    Application.ScreenUpdating = True
End Sub

I landed on this question while searching for similar.
I have a question on mumps's VBA solution: why is a dictionary object required here? Looping through the array (Ary) could be regardless once the array (Ary) is allocated?

 

[mumps]

You are absolutely correct. It is not necessary.

 

[nepakumar]

You are absolutely correct. It is not necessary.

Thank you for the clarification.

 

[mumps]

You are very welcome and thank you for picking up on that.