Results 1 to 9 of 9

Thread: Return Row Heading, Column Heading and Cell with Value

  1. #1
    New Member
    Join Date
    Aug 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Return Row Heading, Column Heading and Cell with Value

    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



    Thanks in advance

  2. #2
    Board Regular MrKowz's Avatar
    Join Date
    Jun 2008
    Location
    St. Louis, MO
    Posts
    6,649
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default 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, _
        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
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes
    - Please use [CODE] [/CODE] tags when posting your VBA code. It retains spacing, so your code is easier to read, and therefore easier to debug.
    - Please back up your file before using any macros suggested!

  3. #3
    New Member
    Join Date
    Aug 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Return Row Heading, Column Heading and Cell with Value

    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

    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))))


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    New Member
    Join Date
    Aug 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: Return Row Heading, Column Heading and Cell with Value

    Quote Originally Posted by Peter_SSs View Post
    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

    Spreadsheet Formulas
    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. #6
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,929
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  7. #7
    New Member
    Join Date
    Aug 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    New Member
    Join Date
    Aug 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Return Row Heading, Column Heading and Cell with Value

    Quote Originally Posted by Jhong View Post
    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.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •