Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert matrix to flat file format
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) -- Think, then move |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert matrix to flat file format
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.PivotIt ems("Sum of Value").Position = 1 .PivotTables("PivotTable1").PivotFields("Row").Ori entation = xlHidden .PivotTables("PivotTable1").PivotFields("Column"). Orientation = xlHidden End With Range("B4").ShowDetail = True Application.DisplayAlerts = False PivotTableSheet.Delete Application.DisplayAlerts = True End Sub "Felix" wrote in message ... 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) -- Think, then move |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Repeated data into flat file format | Excel Worksheet Functions | |||
How to convert a Pivot Table into a Flat File format? | Excel Discussion (Misc queries) | |||
how do I convert an excel file to a flat text file | Excel Discussion (Misc queries) | |||
Convert excel file to flat text file | Excel Discussion (Misc queries) | |||
Convert excel file to flat text file | Excel Discussion (Misc queries) |