Data Conversion using Dynamic Multi-Dimensional Array

[Lwebzer]

Hi everyone,

I'm trying to write a macro that will help me convert datasets to flat formats in a dynamic and scalable way. I'm doing it partly because it's useful for me and partly because I want to learn VBA (I'm a beginner).

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". In bold and underlined is where I think the problem is.


Thank you!


_______________

Here is an example of the kind of data that I would want to convert. I know it's not complete (e.g., the headers aren't going to get converted atm, but this has become a roadblock unfortunately).

Dataset example (pre-conversion)

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

Dataset example (post conversion)

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

Code


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

[Jan Karel Pieterse]

You've got the two Next's reversed in order.
NB: PowerQuery can do this easily, no coding involved.

[paulked]

Jan, TS doesn't want PQ

partly because I want to learn VBA

[Lwebzer]

thank you! Why is it that they need to be reversed in order? I’m just trying to learn some of the basic foundations of vba and also power query is less scalable I think, easier to roll out to other team members if they just need to click a button (?)

thank you again! I’ll try it again tomorrow Am, really hope it works...

also, do I not need to use ubound? I tried to get round it by using last row / last column, not sure if that’s required.

thank you again.

You've got the two Next's reversed in order.
NB: PowerQuery can do this easily, no coding involved.

[Jan Karel Pieterse]

PQ is certainly not less scalable. And it is easier to learn than VBA.
WHen you are nesting for-next loops, the inner For-Next combination belongs to each other and the outer combination. SO if you start with a for j =, then you must also end with a next j

[snb]

To unpivot the pivot:

Sub M_snb()
   sn = Sheet1.Cells(1).CurrentRegion
   ReDim sp((UBound(sn) - 2) * (UBound(sn, 2) - 1), 3)
   
   For j = 0 To UBound(sp) - 1
      y= UBound(sn, 2) - 1
      x = j \ y + 3
      y = j Mod y + 2
      sp(j, 0) = sn(x, 1)
      sp(j, 1) = sn(1, y)
      sp(j, 2) = sn(2, y)
      sp(j, 3) = sn(x, y)
   Next
   
   Cells(1, 8).Resize(UBound(sp) + 1, UBound(sp, 2) + 1) = sp
End Sub

[Lwebzer]

Thanks snb! This is great! Trying to build with my own code but this is great to refer to.

[Lwebzer]

Aha! Thank you. I will be sure to also check out PowerQuery.

Appreciate all the help.

[snb]

Alternative:

Sub M_snb()
   sn = Sheet1.Cells(1).CurrentRegion
   
   With CreateObject("scripting.dictionary")
      For j = 0 To (UBound(sn) - 2) * (UBound(sn, 2) - 1) - 1
          y = UBound(sn, 2) - 1
          x = j \ y + 3
          y = j Mod y + 2
         .Item(.Count) = Array(sn(x, 1), sn(1, y), CLng(sn(2, y)), sn(x, y))
      Next
   
      Cells(1, 12).Resize(.Count, 4) = Application.Index(.items, 0, 0)
    End With
End Sub

[p45cal]

You really should supply a workbook with all this in - it will answer some questions rather than have us guess (wrongly) what's there and save us duplicating your setup.
What you're trying to do is unpivot your data table. Sometimes you can use a pivot table (paradoxically!), nowadays more often Power Query, otherwise it's a case of using vba to run through each cell in the databody of your table, and if it's not a blank cell, picking up the the 1 or 2 headers above each cell and the row header to the left, as well as the value of the cell itself.
Do supply a workbook.

[Lwebzer]

SSS Template_v5.xlsb.xlsm

Hi Pascal,

Thanks for the feedback. Attaching a workbook here (hoping this attachment got uploaded properly) I do really really appreciate your all's help and don't ever want you to use your time inefficiently. I already feel a bit weird using so much of someone else's time without any compensation.

I know that I could in theory use Power Query / Pivot Tables to do this task. The reasons I'm trying to get to the bottom of it using VBA are:

1) If others were to use this template it might be better if they could just do it with a click of a button rather than going through Power QUery / Pivot Tables
2) I'm trying to improve my skills in VBA first, with a view to then potentially moving on to R / Python. I'm pretty much a beginner in all this now but want to get to a place where I can leverage large amounts of data to do analysis more quickly and effectively. My sense is that arrays are a pretty fundamental concept to VBA and a lot of other coding languages.

I really appreciate your support both on this specific task as well as on any continued feedback about how best to leverage this forum and / or how to continue to progress my learnings.

Thank you for all your help,

Louis

[snb]

Why do you ignore contributors to this thread ?

[Lwebzer]

Hey snb, I know I have two threads on this and I should have probably kept it as one. My bad.

Not trying to ignore any contributors - please know that I've spent a lot of time trying to understand every response that has been graciously submitted. When a response only involves code, but no explanations, it is difficult for me to follow what's going on. This might explain why. I'm just not good enough with VBA.

Hopefully that provides an adequate answer. I do really appreciate your responses and think I had replied to your responses on my other thread.

Thank you again.