Converting data from matrix to list format

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.com/questions/139718/converting-data-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


BookingsBookings4 Wall4 WallMetric
Customer ID7/1/20148/1/201411/1/201412/1/2014Period
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 IDMetricPeriodValue
1052Bookings7/1/2014 $ 38K
1052Bookings8/1/2014 $ 31K
10524 Wall11/1/2014 $ 120K
10524 Wall12/1/2014 $ 29K
1105Bookings7/1/2014 $ -
1105Bookings8/1/2014 $ -
11054 Wall11/1/2014 $ 9K
11054 Wall12/1/2014 $ 0K
1110Bookings7/1/2014 $ 25K
1110Bookings8/1/2014 $ 56K
11104 Wall11/1/2014 $ 37K
11104 Wall12/1/2014 $ 35K
1123Bookings7/1/2014 $ -
1123Bookings8/1/2014 $ 25K
11234 Wall11/1/2014 $ -
11234 Wall12/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

Well-known Member
Joined
Jul 14, 2008
Messages
2,505
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
 
Last edited:

Louis_Guionneau

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

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

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

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

Forum statistics

Threads
1,078,466
Messages
5,340,493
Members
399,378
Latest member
voodoo1

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top