Get Column Header from each rows item

[Approyale]

Hi

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

I have the following table:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
</tbody>[/TABLE]

Desired Result:
[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]D[/TD]
[/TR]
</tbody>[/TABLE]

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


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

1​

[/td][td]

x​

[/td][td]

x​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

2​

[/td][td][/td][td]

x​

[/td][td]

x​

[/td][td]

x​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

4​

[/td][td]

x​

[/td][td][/td][td][/td][td]

x​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Desired Result:​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

1​

[/td][td]

A​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

1​

[/td][td]

B​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

2​

[/td][td]

B​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

2​

[/td][td]

C​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

2​

[/td][td]

D​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

4​

[/td][td]

A​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

4​

[/td][td]

D​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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.