Converting data from matrix to list format

Louis_Guionneau · Nov 15, 2019

Hi Everyone,

Firstly, this forum is really great. I generally do my utmost not to post a new thread and read through other materials and try to work it out when I can. Here are some pre-existing materials on this topic that I already read through.

https://www.mrexcel.com/forum/excel-questions/733085-convert-matrix-data-list.html

https://nature.berkeley.edu/~oboyski67/download/MatrixConvert.txt

http://nhsexcel.com/excel-pivot-table-crosstab-flat-list/

https://codereview.stackexchange.co...-in-rows-and-columns-to-rows-in-vba-for-excel

https://www.mrexcel.com/forum/excel-questions/952004-syntax-error-crosstab-flat-file-macro.html

I'm pretty inexperienced with VBA (but trying to learn) so appreciate your patience as I ask what are probably really dumb questions.

I'm trying to develop a tool in VBA that will be able to dynamically adapt datasets in matrix format (potentially with multiple row headers) into a database / list format. For example, I recently came across this dataset (see exhibit 1) that I would want to be able to convert to the format shown in exhibit 2.

Exhibit 1
[TABLE="width: 517"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]

[/TD]
[TD]Bookings[/TD]
[TD]Bookings[/TD]
[TD]4 Wall[/TD]
[TD]4 Wall[/TD]
[TD]Metric[/TD]
[/TR]
[TR]
[TD]Customer ID[/TD]
[TD="align: right"]7/1/2014[/TD]
[TD="align: right"]8/1/2014[/TD]
[TD="align: right"]11/1/2014[/TD]
[TD="align: right"]12/1/2014[/TD]
[TD]Period[/TD]
[/TR]
[TR]
[TD="align: right"]1052[/TD]
[TD] $ 38K[/TD]
[TD] $ 31K[/TD]
[TD] $ 120K[/TD]
[TD] $ 29K[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1105[/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ 9K[/TD]
[TD] $ 0K[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1110[/TD]
[TD] $ 25K[/TD]
[TD] $ 56K[/TD]
[TD] $ 37K[/TD]
[TD] $ 35K[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1123[/TD]
[TD] $ - [/TD]
[TD] $ 25K[/TD]
[TD] $ - [/TD]
[TD] $ 21K[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Exhibit 2

[TABLE="width: 365"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Customer ID[/TD]
[TD]Metric[/TD]
[TD]Period[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD="align: right"]1052[/TD]
[TD]Bookings[/TD]
[TD="align: right"]7/1/2014[/TD]
[TD] $ 38K[/TD]
[/TR]
[TR]
[TD="align: right"]1052[/TD]
[TD]Bookings[/TD]
[TD="align: right"]8/1/2014[/TD]
[TD] $ 31K[/TD]
[/TR]
[TR]
[TD="align: right"]1052[/TD]
[TD]4 Wall[/TD]
[TD="align: right"]11/1/2014[/TD]
[TD] $ 120K[/TD]
[/TR]
[TR]
[TD="align: right"]1052[/TD]
[TD]4 Wall[/TD]
[TD="align: right"]12/1/2014[/TD]
[TD] $ 29K[/TD]
[/TR]
[TR]
[TD="align: right"]1105[/TD]
[TD]Bookings[/TD]
[TD="align: right"]7/1/2014[/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD="align: right"]1105[/TD]
[TD]Bookings[/TD]
[TD="align: right"]8/1/2014[/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD="align: right"]1105[/TD]
[TD]4 Wall[/TD]
[TD="align: right"]11/1/2014[/TD]
[TD] $ 9K[/TD]
[/TR]
[TR]
[TD="align: right"]1105[/TD]
[TD]4 Wall[/TD]
[TD="align: right"]12/1/2014[/TD]
[TD] $ 0K[/TD]
[/TR]
[TR]
[TD="align: right"]1110[/TD]
[TD]Bookings[/TD]
[TD="align: right"]7/1/2014[/TD]
[TD] $ 25K[/TD]
[/TR]
[TR]
[TD="align: right"]1110[/TD]
[TD]Bookings[/TD]
[TD="align: right"]8/1/2014[/TD]
[TD] $ 56K[/TD]
[/TR]
[TR]
[TD="align: right"]1110[/TD]
[TD]4 Wall[/TD]
[TD="align: right"]11/1/2014[/TD]
[TD] $ 37K[/TD]
[/TR]
[TR]
[TD="align: right"]1110[/TD]
[TD]4 Wall[/TD]
[TD="align: right"]12/1/2014[/TD]
[TD] $ 35K[/TD]
[/TR]
[TR]
[TD="align: right"]1123[/TD]
[TD]Bookings[/TD]
[TD="align: right"]7/1/2014[/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD="align: right"]1123[/TD]
[TD]Bookings[/TD]
[TD="align: right"]8/1/2014[/TD]
[TD] $ 25K[/TD]
[/TR]
[TR]
[TD="align: right"]1123[/TD]
[TD]4 Wall[/TD]
[TD="align: right"]11/1/2014[/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD="align: right"]1123[/TD]
[TD]4 Wall[/TD]
[TD="align: right"]12/1/2014[/TD]
[TD] $ 21K[/TD]
[/TR]
</tbody>[/TABLE]

I hope this makes sense so far! I'm excited to try and build this on my own but am coming up against some roadblocks so would really appreciate any brief tips! (But don't write it for me! I want to try and understand :D :D, thank you senseis).

I've come up with the below so far. My thinking is that I'll

1) get the user to define the input data / row data / number of column (headers) / column data.
2) Then I'll transpose (is this the best way?) the old headers and make them become rows.
3) Stack all the values
4) Do a count of the number of old header columns (e.g., how many total non-unique dates were there) and then copy each Customer ID that number of times and list these out as rows to align with the right column headers (which will now each be rows).

I started out writing the below. You'll see how bad I am at VBA quite quickly. Prepare yourself! My question is:

1) I tried to do step 4) above in my last line of code (I know it's not in the order as listed above) but I thought it would be an easier step but my count isn't working - can you help guide me?
2) What is the best way to copy and paste all the values? Should I use a transpose function?

I'd really appreciate any tips! Please don't feel you have to write the code for me. You guys are so good at this and I really want to learn.

Thank you so much!

Louis

Sub LG_Data_Converter()

'Part 1 - Definitions

Dim Input_Range As Range
Dim Output_Range As Range
Dim Row_ID As Range
Dim Record_Descriptor_1 As Range
Dim Record_Descriptor_2 As Range

Set Input_Range = Application.InputBox( _
"Make Selection", "Select Input Range", Type:=8, Default:=Selection.CurrentRegion.Address)

Set OutputRange = Application.InputBox(prompt:="Select Output Location", Type:=8, Default:=Selection.CurrentRegion.Address)

Set Row_ID = Application.InputBox("Make Selection", "Select Record Label (Row Titles)", Type:=8, _
Default:=Selection.CurrentRegion.Address)

Set Record_Descriptor_1 = Application.InputBox( _
"Make Selection", "Select Record Descriptor #1 (#1 Column Title)", Type:=8, Default:=Selection.CurrentRegion.Address)

Set Record_Descriptor_2 = Application.InputBox( _
"Make Selection", "Select Record Descriptor #2 (#2 Column Title", Type:=8, Default:=Selection.CurrentRegion.Address)

'Part 2 - Conversion Process

Dim Count As Long

Count = Cells(Range(Record_Descriptor_1)).End(xlToRight).Columns

lrobbo314 · Nov 15, 2019

The following code should take care of the conversion process.

<tbody>
[TH="bgcolor: #A6A6A6"] A [/TH]
[TH="bgcolor: #A6A6A6"] B [/TH]
[TH="bgcolor: #A6A6A6"] C [/TH]
[TH="bgcolor: #A6A6A6"] D [/TH]
[TH="bgcolor: #A6A6A6"] E [/TH]
[TH="bgcolor: #A6A6A6"] F [/TH]
[TH="bgcolor: #A6A6A6"] G [/TH]
[TH="bgcolor: #A6A6A6"] H [/TH]
[TH="bgcolor: #A6A6A6"] I [/TH]
[TH="bgcolor: #A6A6A6"] J [/TH]
[TH="bgcolor: #A6A6A6"] K [/TH]
[TH="bgcolor: #A6A6A6"] L [/TH]
[TH="bgcolor: #A6A6A6"] M [/TH]

[TH="bgcolor: #A6A6A6"] 1 [/TH]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"] Bookings [/TD]
[TD="bgcolor: #FFFFFF"] Bookings [/TD]
[TD="bgcolor: #FFFFFF"] 4 Wall [/TD]
[TD="bgcolor: #FFFFFF"] 4 Wall [/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"] Customer ID [/TD]
[TD="bgcolor: #FFFFFF"] Type [/TD]
[TD="bgcolor: #FFFFFF"] Date [/TD]
[TD="bgcolor: #FFFFFF"] Value [/TD]

[TH="bgcolor: #A6A6A6"] 2 [/TH]
[TD="bgcolor: #FFFFFF"] Customer ID [/TD]
[TD="bgcolor: #FFFFFF"] 7/1/2014 [/TD]
[TD="bgcolor: #FFFFFF"] 8/1/2014 [/TD]
[TD="bgcolor: #FFFFFF"] 11/1/2014 [/TD]
[TD="bgcolor: #FFFFFF"] 12/1/2014 [/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"] 1052 [/TD]
[TD="bgcolor: #FFFFFF"] Bookings [/TD]
[TD="bgcolor: #FFFFFF"] 7/1/2014 [/TD]
[TD="bgcolor: #FFFFFF"] $ 38K [/TD]

[TH="bgcolor: #A6A6A6"] 3 [/TH]
[TD="bgcolor: #FFFFFF"] 1052 [/TD]
[TD="bgcolor: #FFFFFF"] $ 38K [/TD]
[TD="bgcolor: #FFFFFF"] $ 31K [/TD]
[TD="bgcolor: #FFFFFF"] $ 120K [/TD]
[TD="bgcolor: #FFFFFF"] $ 29K [/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"] 1052 [/TD]
[TD="bgcolor: #FFFFFF"] Bookings [/TD]
[TD="bgcolor: #FFFFFF"] 8/1/2014 [/TD]
[TD="bgcolor: #FFFFFF"] $ 31K [/TD]

[TH="bgcolor: #A6A6A6"] 4 [/TH]
[TD="bgcolor: #FFFFFF"] 1105 [/TD]
[TD="bgcolor: #FFFFFF"] $ - [/TD]
[TD="bgcolor: #FFFFFF"] $ - [/TD]
[TD="bgcolor: #FFFFFF"] $ 9K [/TD]
[TD="bgcolor: #FFFFFF"] $ 0K [/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"] 1052 [/TD]
[TD="bgcolor: #FFFFFF"] 4 Wall [/TD]
[TD="bgcolor: #FFFFFF"] 11/1/2014 [/TD]
[TD="bgcolor: #FFFFFF"] $ 120K [/TD]

[TH="bgcolor: #A6A6A6"] 5 [/TH]
[TD="bgcolor: #FFFFFF"] 1110 [/TD]
[TD="bgcolor: #FFFFFF"] $ 25K [/TD]
[TD="bgcolor: #FFFFFF"] $ 56K [/TD]
[TD="bgcolor: #FFFFFF"] $ 37K [/TD]
[TD="bgcolor: #FFFFFF"] $ 35K [/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"] 1052 [/TD]
[TD="bgcolor: #FFFFFF"] 4 Wall [/TD]
[TD="bgcolor: #FFFFFF"] 12/1/2014 [/TD]
[TD="bgcolor: #FFFFFF"] $ 29K [/TD]

[TH="bgcolor: #A6A6A6"] 6 [/TH]
[TD="bgcolor: #FFFFFF"] 1123 [/TD]
[TD="bgcolor: #FFFFFF"] $ - [/TD]
[TD="bgcolor: #FFFFFF"] $ 25K [/TD]
[TD="bgcolor: #FFFFFF"] $ - [/TD]
[TD="bgcolor: #FFFFFF"] $ 21K [/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"] 1105 [/TD]
[TD="bgcolor: #FFFFFF"] Bookings [/TD]
[TD="bgcolor: #FFFFFF"] 7/1/2014 [/TD]
[TD="bgcolor: #FFFFFF"] $ - [/TD]

[TH="bgcolor: #A6A6A6"] 7 [/TH]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"] 1105 [/TD]
[TD="bgcolor: #FFFFFF"] Bookings [/TD]
[TD="bgcolor: #FFFFFF"] 8/1/2014 [/TD]
[TD="bgcolor: #FFFFFF"] $ - [/TD]

[TH="bgcolor: #A6A6A6"] 8 [/TH]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"] 1105 [/TD]
[TD="bgcolor: #FFFFFF"] 4 Wall [/TD]
[TD="bgcolor: #FFFFFF"] 11/1/2014 [/TD]
[TD="bgcolor: #FFFFFF"] $ 9K [/TD]

[TH="bgcolor: #A6A6A6"] 9 [/TH]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"] 1105 [/TD]
[TD="bgcolor: #FFFFFF"] 4 Wall [/TD]
[TD="bgcolor: #FFFFFF"] 12/1/2014 [/TD]
[TD="bgcolor: #FFFFFF"] $ 0K [/TD]

[TH="bgcolor: #A6A6A6"] 10 [/TH]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"] 1110 [/TD]
[TD="bgcolor: #FFFFFF"] Bookings [/TD]
[TD="bgcolor: #FFFFFF"] 7/1/2014 [/TD]
[TD="bgcolor: #FFFFFF"] $ 25K [/TD]

[TH="bgcolor: #A6A6A6"] 11 [/TH]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"] 1110 [/TD]
[TD="bgcolor: #FFFFFF"] Bookings [/TD]
[TD="bgcolor: #FFFFFF"] 8/1/2014 [/TD]
[TD="bgcolor: #FFFFFF"] $ 56K [/TD]

[TH="bgcolor: #A6A6A6"] 12 [/TH]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"] 1110 [/TD]
[TD="bgcolor: #FFFFFF"] 4 Wall [/TD]
[TD="bgcolor: #FFFFFF"] 11/1/2014 [/TD]
[TD="bgcolor: #FFFFFF"] $ 37K [/TD]

[TH="bgcolor: #A6A6A6"] 13 [/TH]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"] 1110 [/TD]
[TD="bgcolor: #FFFFFF"] 4 Wall [/TD]
[TD="bgcolor: #FFFFFF"] 12/1/2014 [/TD]
[TD="bgcolor: #FFFFFF"] $ 35K [/TD]

</tbody>

Code:

Sub TRANSFORM()
Dim AR() As Variant: AR = Range("A1:F" & Range("A" & Rows.Count).End(xlUp).Row).Value
Dim HeadCount As Integer: HeadCount = 2
Dim Cols As Integer: Cols = UBound(AR, 2)
Dim Ros As Integer: Ros = (UBound(AR) - HeadCount) * Cols
Dim res() As Variant: ReDim res(1 To Ros, 1 To Cols - HeadCount)
Dim pos As Integer: pos = 1


For i = 1 + HeadCount To UBound(AR) - 1
    For j = 1 To UBound(AR, 2) - HeadCount
        res(pos, 1) = AR(i, 1)
        res(pos, 2) = AR(1, j + 1)
        res(pos, 3) = AR(2, j + 1)
        res(pos, 4) = AR(i, j + 1)
        pos = pos + 1
    Next j
Next i


Range("J1:M1") = Array("Customer ID", "Type", "Date", "Value")
Range("J2").Resize(UBound(res), UBound(res, 2)).Value = res
End Sub

Louis_Guionneau · Nov 17, 2019

Thanks! I really appreciate your response. Are you able to tell me why the count function I am trying to use doesn't work? I'd like to work out how many columns there are in the range that I defined (as per my last line of code).

Also, I'm trying work out how the Ubound / Array code is being used but not able to fully understand it. Any pointers you can give me here?

Thank you!

Louis_Guionneau · Nov 17, 2019

Louis_Guionneau said:
Thanks! I really appreciate your response. Are you able to tell me why the count function I am trying to use doesn't work? I'd like to work out how many columns there are in the range that I defined (as per my last line of code).

Also, I'm trying work out how the Ubound / Array code is being used but not able to fully understand it. Any pointers you can give me here?

Thank you!

My concern with the code you posted is that it's not scalable - it won't work with other datasets. So I've been trying to figure out how to leverage what you posted to make this work.

Thank you again.

Louis_Guionneau · Nov 18, 2019

lrobbo314 said:
The following code should take care of the conversion process.

A B C D E F G H I J K L M
1 Bookings Bookings 4 Wall 4 Wall Customer ID Type Date Value
2 Customer ID 7/1/2014 8/1/2014 11/1/2014 12/1/2014 1052 Bookings 7/1/2014 $ 38K
3 1052 $ 38K $ 31K $ 120K $ 29K 1052 Bookings 8/1/2014 $ 31K
4 1105 $ - $ - $ 9K $ 0K 1052 4 Wall 11/1/2014 $ 120K
5 1110 $ 25K $ 56K $ 37K $ 35K 1052 4 Wall 12/1/2014 $ 29K
6 1123 $ - $ 25K $ - $ 21K 1105 Bookings 7/1/2014 $ -
7 1105 Bookings 8/1/2014 $ -
8 1105 4 Wall 11/1/2014 $ 9K
9 1105 4 Wall 12/1/2014 $ 0K
10 1110 Bookings 7/1/2014 $ 25K
11 1110 Bookings 8/1/2014 $ 56K
12 1110 4 Wall 11/1/2014 $ 37K
13 1110 4 Wall 12/1/2014 $ 35K

<tbody>
</tbody>

Code:

Sub TRANSFORM() Dim AR() As Variant: AR = Range("A1:F" & Range("A" & Rows.Count).End(xlUp).Row).Value Dim HeadCount As Integer: HeadCount = 2 Dim Cols As Integer: Cols = UBound(AR, 2) Dim Ros As Integer: Ros = (UBound(AR) - HeadCount) * Cols Dim res() As Variant: ReDim res(1 To Ros, 1 To Cols - HeadCount) Dim pos As Integer: pos = 1 For i = 1 + HeadCount To UBound(AR) - 1 For j = 1 To UBound(AR, 2) - HeadCount res(pos, 1) = AR(i, 1) res(pos, 2) = AR(1, j + 1) res(pos, 3) = AR(2, j + 1) res(pos, 4) = AR(i, j + 1) pos = pos + 1 Next j Next i Range("J1:M1") = Array("Customer ID", "Type", "Date", "Value") Range("J2").Resize(UBound(res), UBound(res, 2)).Value = res End Sub

iRobbo - thanks again for your response. Do you think you could possibly include a brief line by line explanation of your code so that I can better understand it??!!
Thank you so much again for your help. Louis

Louis_Guionneau · Nov 19, 2019

I don't know if anyone is still looking at this thread... but if they are, I would greatly appreciate any tips on why the below code doesn't seem to be working. It tells me "Compile Error: Invalid Next control variable Reference".

Thank you!

Sub LG_Data_Converter()

Dim Nmbr_Headers As Byte
Nmbr_Headers = Application.InputBox("Input Required", "How many Header Rows are there?", Type:=1, Default:=2)

Dim FirstRow As Long, LastRow As Long, FirstColumn As Long, LastColumn As Long

FirstRow = Cells.Find(What:="*", After:=Range("XFD300000"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row

LastRow = Cells.Find(What:="*", After:=Range("XFD300000"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

FirstColumn = Cells.Find(What:="*", After:=Range("XFD300000"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column

LastColumn = Cells.Find(What:="*", After:=Range("XFD300000"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Dim No_Data_Rows As Long
No_Data_Rows = LastRow - FirstRow - Nmbr_Headers + 1 '(Inclusive)'

Dim No_Data_Columns As Long
No_Data_Columns = LastColumn - FirstColumn + 1 - 1 '(Take into account the customer ID column)'

Dim Dataset() As Variant
ReDim Dataset(1 To No_Data_Rows, 1 To No_Data_Columns)

Dim i As Long, j As Long

For i = 1 To No_Data_Rows
For j = 1 To No_Data_Columns

Dataset(i, j) = Cells(i, j)

Next i
Next j

End Sub

Anigito · Mar 12, 2021

lrobbo314 said:
The following code should take care of the conversion process.

A B C D E F G H I J K L M
1 Bookings Bookings 4 Wall 4 Wall Customer ID Type Date Value
2 Customer ID 7/1/2014 8/1/2014 11/1/2014 12/1/2014 1052 Bookings 7/1/2014 $ 38K
3 1052 $ 38K $ 31K $ 120K $ 29K 1052 Bookings 8/1/2014 $ 31K
4 1105 $ - $ - $ 9K $ 0K 1052 4 Wall 11/1/2014 $ 120K
5 1110 $ 25K $ 56K $ 37K $ 35K 1052 4 Wall 12/1/2014 $ 29K
6 1123 $ - $ 25K $ - $ 21K 1105 Bookings 7/1/2014 $ -
7 1105 Bookings 8/1/2014 $ -
8 1105 4 Wall 11/1/2014 $ 9K
9 1105 4 Wall 12/1/2014 $ 0K
10 1110 Bookings 7/1/2014 $ 25K
11 1110 Bookings 8/1/2014 $ 56K
12 1110 4 Wall 11/1/2014 $ 37K
13 1110 4 Wall 12/1/2014 $ 35K

<tbody>
</tbody>

Code:

Sub TRANSFORM() Dim AR() As Variant: AR = Range("A1:F" & Range("A" & Rows.Count).End(xlUp).Row).Value Dim HeadCount As Integer: HeadCount = 2 Dim Cols As Integer: Cols = UBound(AR, 2) Dim Ros As Integer: Ros = (UBound(AR) - HeadCount) * Cols Dim res() As Variant: ReDim res(1 To Ros, 1 To Cols - HeadCount) Dim pos As Integer: pos = 1 For i = 1 + HeadCount To UBound(AR) - 1 For j = 1 To UBound(AR, 2) - HeadCount res(pos, 1) = AR(i, 1) res(pos, 2) = AR(1, j + 1) res(pos, 3) = AR(2, j + 1) res(pos, 4) = AR(i, j + 1) pos = pos + 1 Next j Next i Range("J1:M1") = Array("Customer ID", "Type", "Date", "Value") Range("J2").Resize(UBound(res), UBound(res, 2)).Value = res End Sub

Hello,

Posting this to let you know, that the code works perfectly and really fast!

However, it seems to be not scalable. I currently have matrix 1033 rows and 55 columns, and if I run this code for the full matrix, the system overflows. When I am limiting the processing column count to half (26), it runs perfectly again.

Do you have any tips to make the code scalable, maybe some settings on my PC should be adjusted? OR if there is a way to exclude empty and zero values from transforming?

Thank you!

Anigito · Mar 12, 2021

Anigito said:
Hello,

Posting this to let you know, that the code works perfectly and really fast!

However, it seems to be not scalable. I currently have matrix 1033 rows and 55 columns, and if I run this code for the full matrix, the system overflows. When I am limiting the processing column count to half (26), it runs perfectly again.

Do you have any tips to make the code scalable, maybe some settings on my PC should be adjusted? OR if there is a way to exclude empty and zero values from transforming?

Thank you!

I have found a way to make it scalable: change Integer to Long and add for cycles step 1.

Converting data from matrix to list format

Louis_Guionneau

New Member

Excel Facts

lrobbo314

Well-known Member

Louis_Guionneau

New Member

Louis_Guionneau

New Member

Louis_Guionneau

New Member

Louis_Guionneau

New Member

Anigito

New Member

Anigito

New Member

Similar threads

Forum statistics

Share this page

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Bookings	Bookings	4 Wall	4 Wall					Customer ID	Type	Date	Value
2	Customer ID	7/1/2014	8/1/2014	11/1/2014	12/1/2014					1052	Bookings	7/1/2014	$ 38K
3	1052	$ 38K	$ 31K	$ 120K	$ 29K					1052	Bookings	8/1/2014	$ 31K
4	1105	$ -	$ -	$ 9K	$ 0K					1052	4 Wall	11/1/2014	$ 120K
5	1110	$ 25K	$ 56K	$ 37K	$ 35K					1052	4 Wall	12/1/2014	$ 29K
6	1123	$ -	$ 25K	$ -	$ 21K					1105	Bookings	7/1/2014	$ -
7										1105	Bookings	8/1/2014	$ -
8										1105	4 Wall	11/1/2014	$ 9K
9										1105	4 Wall	12/1/2014	$ 0K
10										1110	Bookings	7/1/2014	$ 25K
11										1110	Bookings	8/1/2014	$ 56K
12										1110	4 Wall	11/1/2014	$ 37K
13										1110	4 Wall	12/1/2014	$ 35K

Converting data from matrix to list format

New Member

Excel Facts

Well-known Member

New Member

New Member

New Member

New Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock