1. There are additional columns in the output, Date (1), STP (4) and Category (8).

2. From column 6 of input, dataArray, onwards, every 4 columns unpivot into 4 rows in one column such that, for the remaining 16 columns (6:21), there are 4 columns with 4 rows (for every one input row) generated.

E.g. If you look at A8, A10, A11 andA12, they "unpivot", I use that term loosely, into the Count of Incidents column. It will be the actual corresponding values i.e. 8469 | 55555 | 21759 | 2743 for the sample row, but I have used the headers for illustrative purposes.

1. There are additional columns in the output, Date (1), STP (4) and Category (8).

2. From column 6 of input, dataArray, onwards, every 4 columns unpivot into 4 rows in one column such that, for the remaining 16 columns (6:21), there are 4 columns with 4 rows (for every one input row) generated.

   E.g. If you look at A8, A10, A11 andA12, they "unpivot", I use that term loosely, into the Count of Incidents column. It will be the actual corresponding values i.e. 8469 | 55555 | 21759 | 2743 for the sample row, but I have used the headers for illustrative purposes.

Situation:

I am processing an unformatted CSV, New-AmbSYS-to-2018-Jan.csv, from NHS England Ambulance Quality Indicators, by reading its contents into an array, and carrying out a number of steps that lead to a final output array format.

All comments welcome.

First 5 columns -

1. Reduce number of columns and re-order

a) Function GetData grabs all the data from the sheet (it will always be the first sheet), ignoring the header row (optional argument), and loads into an array dataArray.

b) Function ReduceColumnsArray takes dataArray, grabs the columns of interest and orders them, as per the specification in array columnsToKeepArray. I tried to apply what I learnt from the answers to my prior question on CR: Reorder columns in Array .

2. Unpivot columns in array and populate output array

i) There are additional columns in the output, Date (1), STP (4) and Category (8).

ii) From column 6 of input, dataArray, onwards, every 4 columns unpivot into 4 rows in one column such that, for the remaining 16 columns (6:21), there are 4 columns with 4 rows (for every one input row) generated.

Arranging the data

a) Function GetFinalArray takes dataArray as an argument and performs the assignment of dataArray values to finalArray

b) Dimensioning finalArray is defined by the fact UBound(finalArray,2) is known, 12; and that the UBound(finalArray,1) will be, due to the unpivot, 4 * the number of rows read in i.e. 4 * UBound(dataArray,1)

c) Column 1 (Date): Function GetDate is used to populate this

d) Column 4 (STP): This uses a placeholder, vbNullString, at present.

e) Column 8 (Category): This is populated by the GetCategory function. This is based on the input rows being ordered such that rows in always run in sequence 1,2,3,4, which map to "Cat1: Life Threatening" , "Cat2: Emergency", "Cat3: Urgent" , "Cat4: Less Urgent". The mod function is applied to the current row to determine this i.e. if current row in array mod 4, 4 for the unpivot, is 0 then Cat4, if 1 then Cat1 etc.

Where, as shown, in matching picture below, the red are the finalArray (row,column) positions and the inner black, "co-ordinates", are the corresponding values to assign from dataArray.

To get the "co-ordinates" I use the fact that the row part of the co-ordinate increments 1 for every 4 rows of the outer array.

So, if I am currently assigning to the outer array (1,9) then 1 mod 4 is 1; and the intersection above, of 1 and 9, is 6. So I use (1,6) as my co-ordinates.

Code:

I am processing an unformatted CSV, New-AmbSYS-to-2018-Jan.csv, from NHS England Ambulance Quality Indicators, by reading its contents into an array, and carrying out a number of steps that lead to a final output array format.

First 5 columns:

Reduce number of columns and re-order

1. Function GetData grabs all the data from the sheet (it will always be the first sheet), ignoring the header row (optional argument), and loads into an array dataArray.

2. Function ReduceColumnsArray takes dataArray, grabs the columns of interest and orders them, as per the specification in array columnsToKeepArray. I tried to apply what I learnt from the answers to my prior question on CR.

Unpivot columns in array and populate output array

1. There are additional columns in the output, Date (1), STP (4) and Category (8).

2. From column 6 of input, dataArray, onwards, every 4 columns unpivot into 4 rows in one column such that, for the remaining 16 columns (6:21), there are 4 columns with 4 rows (for every one input row) generated.

Arranging the data

1. Function GetFinalArray takes dataArray as an argument and performs the assignment of dataArray values to finalArray.

2. Dimensioning finalArray is defined by the fact UBound(finalArray,2) is known, 12; and that the UBound(finalArray,1) will be, due to the unpivot, 4 * the number of rows read in i.e. 4 * UBound(dataArray,1).

1. Column 1 (Date): Function GetDate is used to populate this.

2. Column 4 (STP): This uses a placeholder, vbNullString, at present.

3. Column 8 (Category): This is populated by the GetCategory function. This is based on the input rows being ordered such that rows in always run in sequence 1,2,3,4, which map to "Cat1: Life Threatening" , "Cat2: Emergency", "Cat3: Urgent" , "Cat4: Less Urgent". The mod function is applied to the current row to determine this i.e. if current row in array mod 4, 4 for the unpivot, is 0 then Cat4, if 1 then Cat1 etc.

Where, as shown, in matching picture below, the red are the finalArray (row,column) positions and the inner black, "coordinates", are the corresponding values to assign from dataArray.

To get the "coordinates" I use the fact that the row part of the coordinate increments 1 for every 4 rows of the outer array.

So, if I am currently assigning to the outer array (1,9) then 1 mod 4 is 1; and the intersection above, of 1 and 9, is 6. So I use (1,6) as my coordinates.

Test dataset:

Option Explicit
Option Base 1
Public Function GetFinalArray(ByVal dataArray As Variant) As Variant 'to convert to function
 Dim i As Long
 Dim j As Long
 Dim finalArray()
 Dim totalOutputRows As Long
 Dim numberRowsInDataSet As Long
 
 numberRowsInDataSet = UBound(dataArray, 1)
 totalOutputRows = 4 * numberRowsInDataSet 'scale to account for columns becoming rows
 
 ReDim finalArray(1 To totalOutputRows, 1 To 12)
 
 Dim outputCol As Long
 outputCol = 0
 Dim sourceRow As Long
 
 For i = LBound(finalArray, 1) To UBound(finalArray, 1)
 If i Mod 4 = 1 Then sourceRow = sourceRow + 1
 
 finalArray(i, 1) = GetDate(dataArray(sourceRow, 1), dataArray(sourceRow, 2)) 'this needs formatting to generate output 'yyyy-mm-dd' ;adding -01 as dd
 
 finalArray(i, 2) = dataArray(sourceRow, 1)
 
 finalArray(i, 3) = dataArray(sourceRow, 2)
 
 finalArray(i, 4) = vbNullString 'STP - TODO Add function call in to return this value
 
 finalArray(i, 5) = dataArray(sourceRow, 3)
 
 finalArray(i, 6) = dataArray(sourceRow, 4)
 
 finalArray(i, 7) = dataArray(sourceRow, 5)
 
 finalArray(i, 8) = GetCategory(i) 'Category
 
 Dim n As Long
 
 For n = 9 To 12
 finalArray(i, n) = dataArray(sourceRow, GetFinalColumn(n, i)) 'finalColumn
 Next n
 
 Next i
 GetFinalArray = finalArray
End Function
Public Function ReduceColumnsArray(ByVal dataArray As Variant) As Variant
 'From the initial 79 columns keep only 21 columns as specified in columnsToKeepArray
 Const ColsToKeep As Long = 21
 Dim columnsToKeepArray()
 columnsToKeepArray = Array(1, 2, 3, 4, 5, 14, 16, 17, 18, 19, 25, 28, 31, 20, 26, 29, 32, 21, 27, 30, 33)
 Dim i As Long
 Dim j As Long
 Dim tempArr()
 ReDim tempArr(1 To UBound(dataArray, 1), 1 To ColsToKeep)
 
 For i = LBound(dataArray, 1) To UBound(dataArray, 1)
 
 For j = LBound(columnsToKeepArray) To UBound(columnsToKeepArray)
 tempArr(i, j) = dataArray(i, columnsToKeepArray(j)) 'map the required columns to the output array
 Next j
 Next i
 ReduceColumnsArray = tempArr
End Function
Public Function GetData(ByVal ws As Worksheet, Optional ByVal offsetRows As Long = 0) As Variant
 Dim dataArray()
 Dim startRange As Range
 
 Set startRange = ws.Range("A1").CurrentRegion
 
 With startRange
 dataArray = .Offset(offsetRows, 0).Resize(.Rows.Count - offsetRows, .Columns.Count).Value2
 End With
 GetData = dataArray
End Function
Public Function GetDate(ByVal yearValue As String, ByVal monthValue As String) As Date '? or string
 GetDate = CDate(Format$(yearValue & "-" & monthValue & "-" & "01", "yyyy-mm-dd"))
End Function
Public Function GetCategory(ByVal i As Long) As String
 Select Case i Mod 4 'I know number will not exceed range for mod
 Case 1
 GetCategory = "Cat1: Life Threatening"
 Case 2
 GetCategory = "Cat2: Emergency"
 Case 3
 GetCategory = "Cat3: Urgent"
 Case 0
 GetCategory = "Cat4: Less Urgent"
 Case Else
 GetCategory = "Unknown"
 End Select
End Function
Public Function GetFinalColumn(ByVal finalColumn As Long, ByVal i As Long) As Long
 Dim testVal As Long
 Dim n As Long
 testVal = i Mod 4 'i will always be a positive integer. It will not exceed the max for mod.
 Select Case testVal
 
 Case 0
 n = 3
 Case 1
 n = 0
 Case 2
 n = 1
 Case 3
 n = 2
 
 End Select
 Select Case finalColumn
 Case 9
 GetFinalColumn = 6 + n
 Case 10
 GetFinalColumn = 10 + n
 Case 11
 GetFinalColumn = 14 + n
 Case 12
 GetFinalColumn = 18 + n
 End Select
 
End Function

Option Explicit
Option Base 1
Public Function GetFinalArray(ByVal dataArray As Variant) As Variant 'to convert to function
 Dim i As Long
 Dim j As Long
 Dim finalArray()
 Dim totalOutputRows As Long
 Dim numberRowsInDataSet As Long
 
 numberRowsInDataSet = UBound(dataArray, 1)
 totalOutputRows = 4 * numberRowsInDataSet 'scale to account for columns becoming rows
 
 ReDim finalArray(1 To totalOutputRows, 1 To 12)
 
 Dim outputCol As Long
 outputCol = 0
 Dim sourceRow As Long
 
 For i = LBound(finalArray, 1) To UBound(finalArray, 1)
 If i Mod 4 = 1 Then sourceRow = sourceRow + 1
 
 finalArray(i, 1) = GetDate(dataArray(sourceRow, 1), dataArray(sourceRow, 2)) 'this needs formatting to generate output 'yyyy-mm-dd' ;adding -01 as dd
 
 finalArray(i, 2) = dataArray(sourceRow, 1)
 
 finalArray(i, 3) = dataArray(sourceRow, 2)
 
 finalArray(i, 4) = vbNullString 'STP - TODO Add function call in to return this value
 
 finalArray(i, 5) = dataArray(sourceRow, 3)
 
 finalArray(i, 6) = dataArray(sourceRow, 4)
 
 finalArray(i, 7) = dataArray(sourceRow, 5)
 
 finalArray(i, 8) = GetCategory(i) 'Category
 
 Dim n As Long
 
 For n = 9 To 12
 finalArray(i, n) = dataArray(sourceRow, GetFinalColumn(n, i)) 'finalColumn
 Next n
 
 Next i
 GetFinalArray = finalArray
End Function
Public Function ReduceColumnsArray(ByVal dataArray As Variant) As Variant
 'From the initial 79 columns keep only 21 columns as specified in columnsToKeepArray
 Const ColsToKeep As Long = 21
 Dim columnsToKeepArray()
 columnsToKeepArray = Array(1, 2, 3, 4, 5, 14, 16, 17, 18, 19, 25, 28, 31, 20, 26, 29, 32, 21, 27, 30, 33)
 Dim i As Long
 Dim j As Long
 Dim tempArr()
 ReDim tempArr(1 To UBound(dataArray, 1), 1 To ColsToKeep)
 
 For i = LBound(dataArray, 1) To UBound(dataArray, 1)
 
 For j = LBound(columnsToKeepArray) To UBound(columnsToKeepArray)
 tempArr(i, j) = dataArray(i, columnsToKeepArray(j)) 'map the required columns to the output array
 Next j
 Next i
 ReduceColumnsArray = tempArr
End Function
Public Function GetData(ByVal ws As Worksheet, Optional ByVal offsetRows As Long = 0) As Variant
 Dim dataArray()
 Dim startRange As Range
 
 Set startRange = ws.Range("A1").CurrentRegion
 
 With startRange
 dataArray = .Offset(offsetRows, 0).Resize(.Rows.Count - offsetRows, .Columns.Count).Value2
 End With
 GetData = dataArray
End Function

Public Function GetDate(ByVal yearValue As String, ByVal monthValue As String) As Date '? or string
 GetDate = CDate(Format$(yearValue & "-" & monthValue & "-" & "01", "yyyy-mm-dd"))
End Function
Public Function GetCategory(ByVal i As Long) As String
 Select Case i Mod 4 'I know number will not exceed range for mod
 Case 1
 GetCategory = "Cat1: Life Threatening"
 Case 2
 GetCategory = "Cat2: Emergency"
 Case 3
 GetCategory = "Cat3: Urgent"
 Case 0
 GetCategory = "Cat4: Less Urgent"
 Case Else
 GetCategory = "Unknown"
 End Select
End Function
Public Function GetFinalColumn(ByVal finalColumn As Long, ByVal i As Long) As Long
 Dim testVal As Long
 Dim n As Long
 testVal = i Mod 4 'i will always be a positive integer. It will not exceed the max for mod.
 Select Case testVal
 
 Case 0
 n = 3
 Case 1
 n = 0
 Case 2
 n = 1
 Case 3
 n = 2
 
 End Select
 Select Case finalColumn
 Case 9
 GetFinalColumn = 6 + n
 Case 10
 GetFinalColumn = 10 + n
 Case 11
 GetFinalColumn = 14 + n
 Case 12
 GetFinalColumn = 18 + n
 End Select
 
End Function