Return Row Heading, Column Heading and Cell with Value

J

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:

[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width: 48pt; text-align: center;"> </colgroup><tbody>[TR]
[TD="width: 64, align: center"]Table1[/TD]
[TD="width: 64, align: center"][/TD]
[TD="width: 64, align: center"][/TD]
[TD="width: 64, align: center"][/TD]
[TD="width: 64, align: center"][/TD]
[TD="width: 64, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"] [/TD]
[TD="class: xl63, align: center"]Apple[/TD]
[TD="class: xl63, align: center"]Banana[/TD]
[TD="class: xl63, align: center"]Cake[/TD]
[TD="class: xl63, align: center"]Chips[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]Alex[/TD]
[TD="class: xl63, align: center"] [/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD="class: xl63, align: center"] [/TD]
[TD="class: xl63, align: center"] [/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]Brian[/TD]
[TD="class: xl63, align: center"] [/TD]
[TD="class: xl63, align: center"]2[/TD]
[TD="class: xl63, align: center"] [/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]Charlie[/TD]
[TD="class: xl63, align: center"]3[/TD]
[TD="class: xl63, align: center"] [/TD]
[TD="class: xl63, align: center"] [/TD]
[TD="class: xl63, align: center"] [/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]Dana[/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD="class: xl63, align: center"] [/TD]
[TD="class: xl63, align: center"]2[/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]Erica[/TD]
[TD="class: xl63, align: center"] [/TD]
[TD="class: xl63, align: center"] [/TD]
[TD="class: xl63, align: center"] [/TD]
[TD="class: xl63, align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]Fred[/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD="class: xl63, align: center"] [/TD]
[TD="class: xl63, align: center"] [/TD]
[TD="class: xl63, align: center"] [/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]Grace[/TD]
[TD="class: xl63, align: center"] [/TD]
[TD="class: xl63, align: center"]2[/TD]
[TD="class: xl63, align: center"] [/TD]
[TD="class: xl63, align: center"] [/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"](blank)[/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD="class: xl63, align: center"] [/TD]
[TD="class: xl63, align: center"]1[/TD]
[TD="class: xl63"] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Table2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Alex[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl63"]Banana[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Brian[/TD]
[TD="class: xl64"]2[/TD]
[TD="class: xl63"]Banana[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Brian[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl63"]Chips[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Charlie[/TD]
[TD="class: xl64"]3[/TD]
[TD="class: xl63"]Apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Dana[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl63"]Apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Dana[/TD]
[TD="class: xl64"]2[/TD]
[TD="class: xl63"]Cake[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Dana[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl63"]Chips[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Erica[/TD]
[TD="class: xl64"]2[/TD]
[TD="class: xl63"]Chips[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Fred[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl63"]Apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Grace[/TD]
[TD="class: xl64"]2[/TD]
[TD="class: xl63"]Banana[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"](blank)[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl63"]Apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"](blank)[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl63"]Cake[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Thanks in advance
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
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
Peter_SSs said:
Welcome to the MrExcel board!

Try these, copied down.

List from table

ABCDE
Alex
Brian
Charlie
Dana
Erica
Fred
Grace
(blank)
Alex
Brian
Brian
Charlie
Dana
Dana
Dana
Erica
Fred
Grace
(blank)
(blank)

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: center"]Apple[/TD]
[TD="align: center"]Banana[/TD]
[TD="align: center"]Cake[/TD]
[TD="align: center"]Chips[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]Banana[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Banana[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]Chips[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Apple[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]Apple[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Cake[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]Chips[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]19[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Chips[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]20[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]Apple[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]21[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Banana[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]22[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]Apple[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]23[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]Cake[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]24[/TD]

</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
Click to expand...

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
You must log in or register to reply here.

Similar threads

N
  • Solved
Formula to find last cell with a entry in the row and return the column heading name
Replies
3
Views
157
Fluff
Fluff
N
  • Question Question
Find cell value from Row & Column match
2
Replies
10
Views
963
proficient
proficient
E
  • Solved
Index/Match - Return value if partial text from cell contained anywhere in separate column
Replies
5
Views
4K
excelnewb221
E
E
  • Solved
Index/Match - Return value if partial text from cell contained in separate column
2
Replies
16
Views
3K
Kevin_Li
Kevin_Li
L
  • Solved
Map column headings to Row value based in value in Row/Column Cell
Replies
6
Views
435
Alex Blakenburg
A

Forum statistics

Threads
1,223,713
Messages
6,174,043
Members
452,542
Latest member
Bricklin

Share this page

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