Get Column Header from each rows item

[Approyale]

Hi

For every row with the value, fetch the table header.

I have the following table:

	A	B	C	D
1	x	x
2		x	x	x
3
4	x			x

<tbody>
</tbody>

Desired Result:

1	A
1	B
2	B
2	C
2	D
4	A
4	D

<tbody>
</tbody>

Thank you in advance.

Best,
AppRoyale

 

[offthelip]

this should do it:

Sub test2()

inarr = Range(Cells(1, 1), Cells(5, 5))
Range(Cells(1, 10), Cells(30, 11)) = ""
outarr = Range(Cells(1, 10), Cells(30, 11))
indi = 1
For i = 2 To 5
 For j = 2 To 5
  If inarr(i, j) = "x" Then
  outarr(indi, 1) = inarr(i, 1)
  outarr(indi, 2) = inarr(1, j)
  indi = indi + 1
  End If
 Next j
Next i
Range(Cells(1, 10), Cells(30, 11)) = outarr


End Sub

 

[Approyale]

this should do it:

Sub test2()

inarr = Range(Cells(1, 1), Cells(5, 5))
Range(Cells(1, 10), Cells(30, 11)) = ""
outarr = Range(Cells(1, 10), Cells(30, 11))
indi = 1
For i = 2 To 5
 For j = 2 To 5
  If inarr(i, j) = "x" Then
  outarr(indi, 1) = inarr(i, 1)
  outarr(indi, 2) = inarr(1, j)
  indi = indi + 1
  End If
 Next j
Next i
Range(Cells(1, 10), Cells(30, 11)) = outarr


End Sub

Thank you. I suppose that the result can not be achieved by an Ecel formula only?

 

[offthelip]

Very difficult because in your example the number of rows need for the "1" s is two rows, but if you add an x in then this becaomse 3 rows and every thing shifts down I can't see how you can do that with worksheet functions alone

 

[Marcelo Branco]

A possible solution using formulas

	A​	B​	C​	D​	E​	F​
1​		A​	B​	C​	D​
2​	1​	x​	x​
3​	2​		x​	x​	x​
4​	3​
5​	4​	x​			x​
6​
7​	Desired Result:​
8​	1​	A​
9​	1​	B​
10​	2​	B​
11​	2​	C​
12​	2​	D​
13​	4​	A​
14​	4​	D​
15​

Array formula in A8 copied down
=IFERROR(INDEX(A$2:A$5,MATCH(ROWS(A$8:A8)-1,COUNTIF(OFFSET(B$1:E$1,,,ROW(B$1:E$5)-ROW(B$1)+1),"x")),1),"")
Ctrl+Shift+Enter

Array formula in B8 copied down
=IF(A8="","",INDEX($B$1:$E$1,SMALL(IF(INDEX($B$2:$E$5,MATCH(A8,A$2:A$5,0),0)="x",COLUMN($B$1:$E$1)-COLUMN($B$1)+1),COUNTIF(A$8:A8,A8))))
Ctrl+Shift+Enter

M.

 

[offthelip]

It is always nice to be proved wrong, I learn something every day!!

 

[Approyale]

Thank you very much!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.