How to “flatten” or “collapse” a 2D Excel table into 1D?

by

You can use the excel pivot table feature to reverse a pivot table (which is essentially what you have here):

Good instructions here:

http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/

Which links to the following VBA code (put it in a module) if you don’t want to follow the instructions by hand:

Sub ReversePivotTable()
'   Before running this, make sure you have a summary table with column headers.
'   The output table will have three columns.
    Dim SummaryTable As Range, OutputRange As Range
    Dim OutRow As Long
    Dim r As Long, c As Long

    On Error Resume Next
    Set SummaryTable = ActiveCell.CurrentRegion
    If SummaryTable.Count = 1 Or SummaryTable.Rows.Count < 3 Then
        MsgBox "Select a cell within the summary table.", vbCritical
        Exit Sub
    End If
    SummaryTable.Select
    Set OutputRange = Application.InputBox(prompt:="Select a cell for the 3-column output", Type:=8)
'   Convert the range
    OutRow = 2
    Application.ScreenUpdating = False
    OutputRange.Range("A1:C3") = Array("Column1", "Column2", "Column3")
    For r = 2 To SummaryTable.Rows.Count
        For c = 2 To SummaryTable.Columns.Count
            OutputRange.Cells(OutRow, 1) = SummaryTable.Cells(r, 1)
            OutputRange.Cells(OutRow, 2) = SummaryTable.Cells(1, c)
            OutputRange.Cells(OutRow, 3) = SummaryTable.Cells(r, c)
            OutputRange.Cells(OutRow, 3).NumberFormat = SummaryTable.Cells(r, c).NumberFormat
            OutRow = OutRow + 1
        Next c
    Next r
End Sub

-Adam

More Related Contents:

  1. Loop through files in a folder using VBA?
  2. How to error handle 1004 Error with WorksheetFunction.VLookup?
  3. How can Excel VBA open file using default application
  4. How to compare two entire rows in a sheet
  5. Run same excel macro on multiple excel files
  6. how to detect whether VBA excel found something?
  7. VBA : save a file with UTF-8 without BOM
  8. Excel tab sheet names vs. Visual Basic sheet names
  9. Is there a faster CountIF
  10. Excel VBA How to detect if something was pasted in a Worksheet
  11. Getting error Procedure too large in VBA Macros (Excel)
  12. How to add data validation to a cell using VBA
  13. How to do late binding in VBA?
  14. How to break long string to multiple lines
  15. How to copy a chart from Excel to PowerPoint?
  16. vba paste not working
  17. mac excel vba loop : from list & then export as pdf
  18. Can Advanced Filter criteria be in the VBA rather than a range?
  19. In Excel VBA, what is the way to check if the web page is fully loaded?
  20. Excel Useform: How to hide application but have icon in the taskbar
  21. Reading Web Pages using Excel VBA
  22. Excel VBA: auto click and open file from website
  23. Calling regasm without administrative rights for COM interop in Excel VBA
  24. Is there a way for DIR(path) in VBA to handle strings longer than 260?
  25. How do I activate a specific workbook and a specific sheet?
  26. How to find a value in an excel column by vba code Cells.Find
  27. Start VBA macro when editing a cell
  28. VBA Run-time error 438 appears when “paste” runs
  29. If Not function proceeds when value *is* the one specified
  30. Copy an entire worksheet to a new worksheet in Excel 2010

Leave a Comment