Return Row Heading, Column Heading and Cell with Value

Jhong

New Member
Joined
Aug 15, 2017
Messages
7
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...column-header-row-header-each-occurrence.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
AppleBananaCakeChips
Alex 1
Brian 2 1
Charlie3
Dana1 21
Erica 2
Fred1
Grace 2
(blank)1 1
Table2
Alex1Banana
Brian2Banana
Brian1Chips
Charlie3Apple
Dana1Apple
Dana2Cake
Dana1Chips
Erica2Chips
Fred1Apple
Grace2Banana
(blank)1Apple
(blank)1Cake

<colgroup><col width="64" span="6" style="width: 48pt; text-align: center;"> </colgroup><tbody>
</tbody>



Thanks in advance
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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, _
    strHead 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
Set dWS = Sheets.Add

With dWS
    .Name = "Rebuilt Table"
    .Range("A1").Value = "Name"
    .Range("B1").Value = "Header"
    .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
            rng1 = rng.Address
            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
 
Upvote 0
Thanks so much MrKowz. However, I am not familiar with VBA yet and only needs a simple excel formula.
 
Upvote 0
Welcome to the MrExcel board!

Try these, copied down.

Excel Workbook
ABCDE
1AppleBananaCakeChips
2Alex1
3Brian21
4Charlie3
5Dana121
6Erica2
7Fred1
8Grace2
9(blank)11
10
11
12Alex1Banana
13Brian2Banana
14Brian1Chips
15Charlie3Apple
16Dana1Apple
17Dana2Cake
18Dana1Chips
19Erica2Chips
20Fred1Apple
21Grace2Banana
22(blank)1Apple
23(blank)1Cake
24
List from table
 
Upvote 0
Welcome to the MrExcel board!

Try these, copied down.

List from table

ABCDE
1 AppleBananaCakeChips
2Alex 1
3Brian 2 1
4Charlie3
5Dana1 21
6Erica 2
7Fred1
8Grace 2
9(blank)1 1
10
11
12Alex1Banana
13Brian2Banana
14Brian1Chips
15Charlie3Apple
16Dana1Apple
17Dana2Cake
18Dana1Chips
19Erica2Chips
20Fred1Apple
21Grace2Banana
22(blank)1Apple
23(blank)1Cake
24

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:56px;"><col style="width:56px;"><col style="width:56px;"><col style="width:56px;"><col style="width:56px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
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))))

<tbody>
</tbody>

<tbody>
</tbody>


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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top