Emily has attended:
Excel Advanced - For Power Users course
Creating a list from a matrix of data
Hello,
I have a data set which has the weeks of the year in column A (so 52 rows from A2:A53) and in row 1 I have Sku codes (up to 350 different ones, so from column B:ML).
The table is then filled with data showing the quantity requirement of that sku by week.
I need to write a macro which means I can convert this to a list. The list will be 18,200 enties long as each of the 350 skus should appear 52 times in column A, with the weeks repeating 1 - 52 along each sku (this would be column B) and then Column C would be the number from the current table which correlates to that sku in that week.
This is fairly urgent as I need it for a presentation!
Thanks
RE: Creating a list from a matrix of data
Hi Emily,
Thank you for the forum question.
Please add the following code to a module in the workbook with the tubular table.
Option Explicit
Public Sub TableToList()
If ActiveCell.CurrentRegion.Rows.Count < 2 Then
Exit Sub
End If
If ActiveCell.CurrentRegion.Columns.Count < 2 Then
Exit Sub
End If
Dim table As Range
Dim rngColHead As Range
Dim rngRowHead As Range
Dim rngData As Range
Dim cel As Range
Dim rowVal As Variant
Dim colVal As Variant
Dim val As Variant
Set table = ActiveCell.CurrentRegion
Set rngColHead = table.Rows(1)
Set rngRowHead = table.Columns(1)
Set rngData = table.Offset(1, 1)
Set rngData = rngData.Resize(rngData.Rows.Count - 1, rngData.Columns.Count - 1)
ActiveWorkbook.Worksheets.Add
ActiveCell.Value = "Row#"
ActiveCell.Offset(0, 1).Value = "RowValue"
ActiveCell.Offset(0, 2).Value = "ColValue"
ActiveCell.Offset(0, 3).Value = "Data"
ActiveCell.Offset(1, 0).Select
Dim n As Long
For Each cel In rngData
colVal = rngColHead.Cells(cel.Column - table.Column + 1)
rowVal = rngRowHead.Cells(cel.Row - table.Row + 1)
n = n + 1
ActiveCell.Value = n
ActiveCell.Offset(0, 1).Value = rowVal
ActiveCell.Offset(0, 2).Value = colVal
ActiveCell.Offset(0, 3).Value = cel.Value
ActiveCell.Offset(1, 0).Select
Next
End Sub
You will need to select a cell inside your table and then run the macro. The macro will insert a new worksheet with the flat list created from your table.
Please let me know if you cannot get it to work.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
