# Thread: Return Row Heading, Column Heading and Cell with Value Thanks:  3 Post #4891175 (1)Post #4891181 (1)Post #4890832 (1) Likes:  3 Post #4890832 (2)Post #4891175 (1)

Hi,

I have been trying a formula to return row and column heading from table 1 including the cell value of the cell with value in it. I had been trying to follow this post : https://www.mrexcel.com/forum/excel-...ccurrence.html but can't seem to make it work. Can anyone help with a simpler Index/Vlookup formula.

here is my example, Table 1 to give something like that of Tabe 2:

 Table1 Apple Banana Cake Chips Alex 1 Brian 2 1 Charlie 3 Dana 1 2 1 Erica 2 Fred 1 Grace 2 (blank) 1 1 Table2 Alex 1 Banana Brian 2 Banana Brian 1 Chips Charlie 3 Apple Dana 1 Apple Dana 2 Cake Dana 1 Chips Erica 2 Chips Fred 1 Apple Grace 2 Banana (blank) 1 Apple (blank) 1 Cake

2. ## Re: Return Row Heading, Column Heading and Cell with Value

Welcome to the forums!

I'm not quite sure how to do this via a formula, but a VBA macro makes quick work of this.

Try:

Code:
Public Sub rebuildtable()
Dim LR      As Long, _
LC      As Long

Dim rng     As Range, _
rng1    As String, _
rowx    As Long

Dim strName As String, _
lngVal  As Long

Dim sWS     As Worksheet, _
dWS     As Worksheet

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

rowx = 2

Set sWS = ActiveSheet

With dWS
.Name = "Rebuilt Table"
.Range("A1").Value = "Name"
.Range("C1").Value = "Value"
End With

With sWS
LR = .Range("A" & Rows.Count).End(xlUp).Row
LC = .Cells(1, Columns.Count).End(xlToLeft).Column

With .Range(.Cells(2, 2), .Cells(LR, LC))
Set rng = .Find("*", LookIn:=xlValues)
If Not rng Is Nothing Then
Do
dWS.Range("A" & rowx).Value = sWS.Range("A" & rng.Row).Value
dWS.Range("B" & rowx).Value = sWS.Cells(1, rng.Column).Value
dWS.Range("C" & rowx).Value = rng.Value
rowx = rowx + 1
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing And rng1 <> rng.Address
End If
End With
End With

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

End Sub

3. ## Re: Return Row Heading, Column Heading and Cell with Value

Thanks so much MrKowz. However, I am not familiar with VBA yet and only needs a simple excel formula.

4. ## Re: Return Row Heading, Column Heading and Cell with Value

Welcome to the MrExcel board!

Try these, copied down.

List from table

 A B C D E 1 Apple Banana Cake Chips 2 Alex 1 3 Brian 2 1 4 Charlie 3 5 Dana 1 2 1 6 Erica 2 7 Fred 1 8 Grace 2 9 (blank) 1 1 10 11 12 Alex 1 Banana 13 Brian 2 Banana 14 Brian 1 Chips 15 Charlie 3 Apple 16 Dana 1 Apple 17 Dana 2 Cake 18 Dana 1 Chips 19 Erica 2 Chips 20 Fred 1 Apple 21 Grace 2 Banana 22 (blank) 1 Apple 23 (blank) 1 Cake 24

 Cell Formula A12 =IFERROR(INDEX(A\$2:A\$9,AGGREGATE(15,6,(ROW(A\$2:A\$9)-ROW(A\$2)+1)/ISNUMBER(\$B\$2:\$E\$9),ROWS(A\$12:A12))),"") B12 =IF(A12="","",INDEX(B\$2:E\$9,MATCH(A12,A\$2:A\$9,0),MATCH(C12,B\$1:E\$1,0))) C12 =IF(A12="","",INDEX(B\$1:E\$1,AGGREGATE(15,6,(COLUMN(B\$1:E\$1)-COLUMN(\$B\$1)+1)/ISNUMBER(INDEX(\$B\$2:\$E\$9,MATCH(A12,A\$2:A\$9,0),0)),COUNTIF(A\$12:A12,A12))))

Excel tables to the web >> Excel Jeanie HTML 4

5. ## Re: Return Row Heading, Column Heading and Cell with Value

Originally Posted by Peter_SSs
Welcome to the MrExcel board!

Try these, copied down.

List from table

 A B C D E 1 Apple Banana Cake Chips 2 Alex 1 3 Brian 2 1 4 Charlie 3 5 Dana 1 2 1 6 Erica 2 7 Fred 1 8 Grace 2 9 (blank) 1 1 10 11 12 Alex 1 Banana 13 Brian 2 Banana 14 Brian 1 Chips 15 Charlie 3 Apple 16 Dana 1 Apple 17 Dana 2 Cake 18 Dana 1 Chips 19 Erica 2 Chips 20 Fred 1 Apple 21 Grace 2 Banana 22 (blank) 1 Apple 23 (blank) 1 Cake 24

 Cell Formula A12 =IFERROR(INDEX(A\$2:A\$9,AGGREGATE(15,6,(ROW(A\$2:A\$9)-ROW(A\$2)+1)/ISNUMBER(\$B\$2:\$E\$9),ROWS(A\$12:A12))),"") B12 =IF(A12="","",INDEX(B\$2:E\$9,MATCH(A12,A\$2:A\$9,0),MATCH(C12,B\$1:E\$1,0))) C12 =IF(A12="","",INDEX(B\$1:E\$1,AGGREGATE(15,6,(COLUMN(B\$1:E\$1)-COLUMN(\$B\$1)+1)/ISNUMBER(INDEX(\$B\$2:\$E\$9,MATCH(A12,A\$2:A\$9,0),0)),COUNTIF(A\$12:A12,A12))))

Excel tables to the web >> Excel Jeanie HTML 4
Thanks so much Peter, I will try this later after I get into the office. I am quite interested as this will be my first time to use an aggregate function as it is uncommon for me but the whole of the formula is easy to understand as well.

6. ## Re: Return Row Heading, Column Heading and Cell with Value

To implement MrKowz code, do the following:

Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

7. ## Re: Return Row Heading, Column Heading and Cell with Value

Already tried and and formula is as I need. A12 as main then C12 dependent on A and Value or B12 dependent on both.

Thanks so much Peter.

8. ## Re: Return Row Heading, Column Heading and Cell with Value

Thanks for a quick tip Alan. I will try this as well and your instruction made it seem so easy as well as I always think VBA was complicated :D

9. ## Re: Return Row Heading, Column Heading and Cell with Value

Originally Posted by Jhong
Already tried and and formula is as I need. A12 as main then C12 dependent on A and Value or B12 dependent on both.

Thanks so much Peter.
You are welcome.