convert matrix to flat file format

[Felix]

I gather similarly structured data from about 30 different sources. These
data are in matrix format (15 rows by 23 columns).
To be able to analyze the data with pivot tables, I want to convert them to
a flat file format, with pr line the row label, column label, and value.

What is the easiest way to do this? ( individually referring to each cell
and it's row and column label is too error prone / tedious, as I need to be
able to change the matrix format for every new version, which is about twice
yearly, and as I have more than 1 matrix format, according to the type of
source)

 

[Bernie Deitrick]

Felix,

Select a cell in your table, then run the macro below.

This assumes that you have headers in the top row of your data table....

HTH,
Bernie
MS Excel MVP

Sub MakeDataBaseTableFromCrossTab()
Dim SummaryTableRange As Range
Dim PivotTableSheet As Worksheet
Set SummaryTableRange = ActiveCell.CurrentRegion
If SummaryTableRange.Count = 1 Or SummaryTableRange.Rows.Count < 3 Then
MsgBox "Select a cell in the summary table.", vbCritical
Exit Sub
End If
ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlConsolidation, _
SourceData:=Array(SummaryTableRange.Address(True, True, xlR1C1, True))) _
.CreatePivotTable TableDestination:="", _
TableName:="PivotTable1"
Set PivotTableSheet = ActiveSheet
With PivotTableSheet
.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
.PivotTables("PivotTable1").DataPivotField.PivotItems("Sum of Value").Position = 1
.PivotTables("PivotTable1").PivotFields("Row").Orientation = xlHidden
.PivotTables("PivotTable1").PivotFields("Column").Orientation = xlHidden
End With
Range("B4").ShowDetail = True
Application.DisplayAlerts = False
PivotTableSheet.Delete
Application.DisplayAlerts = True
End Sub