Converting data from matrix to list format

L

Louis_Guionneau

New Member
Joined
Nov 12, 2019
Messages
28
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
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
 
Last edited:
Upvote 0
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!
 
Upvote 0
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!
Click to expand...

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.
 
Upvote 0
lrobbo314 said:
The following code should take care of the conversion process.


ABCDEFGHIJKLM
1BookingsBookings4 Wall4 WallCustomer IDTypeDateValue
2Customer ID7/1/20148/1/201411/1/201412/1/20141052Bookings7/1/2014$ 38K
31052$ 38K$ 31K$ 120K$ 29K1052Bookings8/1/2014$ 31K
41105$ -$ -$ 9K$ 0K10524 Wall11/1/2014$ 120K
51110$ 25K$ 56K$ 37K$ 35K10524 Wall12/1/2014$ 29K
61123$ -$ 25K$ -$ 21K1105Bookings7/1/2014$ -
71105Bookings8/1/2014$ -
811054 Wall11/1/2014$ 9K
911054 Wall12/1/2014$ 0K
101110Bookings7/1/2014$ 25K
111110Bookings8/1/2014$ 56K
1211104 Wall11/1/2014$ 37K
1311104 Wall12/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
Click to expand...
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
 
Upvote 0
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
 
Upvote 0
lrobbo314 said:
The following code should take care of the conversion process.


ABCDEFGHIJKLM
1BookingsBookings4 Wall4 WallCustomer IDTypeDateValue
2Customer ID7/1/20148/1/201411/1/201412/1/20141052Bookings7/1/2014$ 38K
31052$ 38K$ 31K$ 120K$ 29K1052Bookings8/1/2014$ 31K
41105$ -$ -$ 9K$ 0K10524 Wall11/1/2014$ 120K
51110$ 25K$ 56K$ 37K$ 35K10524 Wall12/1/2014$ 29K
61123$ -$ 25K$ -$ 21K1105Bookings7/1/2014$ -
71105Bookings8/1/2014$ -
811054 Wall11/1/2014$ 9K
911054 Wall12/1/2014$ 0K
101110Bookings7/1/2014$ 25K
111110Bookings8/1/2014$ 56K
1211104 Wall11/1/2014$ 37K
1311104 Wall12/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
Click to expand...

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!
 
Upvote 0
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!
Click to expand...
I have found a way to make it scalable: change Integer to Long and add for cycles step 1.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Locked
  • Question Question
Vlookup from multiselect data validation list
Replies
2
Views
1K
Peter_SSs
Peter_SSs
tzcoding
  • Solved
VBA Macro to Export Data from Excel Spreadsheet to CSV
Replies
1
Views
10K
Domenic
D
J
  • Question Question
Excel macro for Remove comma's and spaces from a cell and place the data in different cell
Replies
3
Views
1K
JEC
JEC
P
  • Question Question
vba to run through sorted list and copy rows in each sorted group into a new tab
Replies
3
Views
1K
vcoolio
V
C
  • Question Question
Need Help with VBA Code - Copy data below and above a specific text
Replies
1
Views
873
onlyadrafter
O

Forum statistics

Threads
1,224,815
Messages
6,181,135
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top