Converting data from matrix to list format

[Louis_Guionneau]

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

	Bookings	Bookings	4 Wall	4 Wall	Metric
Customer ID	7/1/2014	8/1/2014	11/1/2014	12/1/2014	Period
1052	$ 38K	$ 31K	$ 120K	$ 29K
1105	$ -	$ -	$ 9K	$ 0K
1110	$ 25K	$ 56K	$ 37K	$ 35K
1123	$ -	$ 25K	$ -	$ 21K

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Exhibit 2

Customer ID	Metric	Period	Value
1052	Bookings	7/1/2014	$ 38K
1052	Bookings	8/1/2014	$ 31K
1052	4 Wall	11/1/2014	$ 120K
1052	4 Wall	12/1/2014	$ 29K
1105	Bookings	7/1/2014	$ -
1105	Bookings	8/1/2014	$ -
1105	4 Wall	11/1/2014	$ 9K
1105	4 Wall	12/1/2014	$ 0K
1110	Bookings	7/1/2014	$ 25K
1110	Bookings	8/1/2014	$ 56K
1110	4 Wall	11/1/2014	$ 37K
1110	4 Wall	12/1/2014	$ 35K
1123	Bookings	7/1/2014	$ -
1123	Bookings	8/1/2014	$ 25K
1123	4 Wall	11/1/2014	$ -
1123	4 Wall	12/1/2014	$ 21K

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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]

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>

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]

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]

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]

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>

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]

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]

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>

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]

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.