Data transformation from Excel

Question

I have some data in Excel file with some horizontal and vertical dimensions. It looks like this:

This data has to be loaded into some BI system. For this purpose I have to transform data to the "table style". In other words it should be presented in table like this:

I need some effective algorithm to make this transformation. The only one which I know is to take value from first cell (100000) get values from vertical and horizontal coordinates (Russia, Population, 1900) and insert into first row. Then take another cell and so on.

It would be work with small amount of data, but with big amount it works very slowly. Do you know more sophisticated algorithm for this kind of data?

Answer 1

There are several ways of doing this with VBA. In this solution, I first create a user defined Object named Country, with four properties: Name, Index, YR, and Quantity. It is not necessary to do this; but I've been working with these recently and I think it adds some clarity to the code.

I then read the Source data into a VBA array (which can be done in a single step), iterate through the array to create a collection of Country objects.

I then go through the Country collection, outputting the properties into a Results array, where I want them.

Finally, the results array is outputted to a worksheet -- again, just a single step.

One could go directly from the Source data array to the Results array, but I think it is easier to see what's going on using the object.

One could also not bother with the VBA array, but process the cells directly from one worksheet to another. In my experience, this approach will be at least an order of magnitude slower than using the VBA array approach.

Depending on the size of your database, refinements may be necessary. Be sure to read the comments in the code.

To define the Country object, insert a Class Module and rename it Country. Place the following code in that module:

==========================================

Option Explicit
Private pName As String
Private pIndex As String
Private pYr As Long
Private pQuantity As Double

Public Property Get Name() As String
    Name = pName
End Property
Public Property Let Name(Value As String)
    pName = Value
End Property

Public Property Get Index() As String
    Index = pIndex
End Property
Public Property Let Index(Value As String)
    pIndex = Value
End Property

Public Property Get Yr() As Long
    Yr = pYr
End Property
Public Property Let Yr(Value As Long)
    pYr = Value
End Property

Public Property Get Quantity() As Double
    Quantity = pQuantity
End Property
Public Property Let Quantity(Value As Double)
    pQuantity = Value
End Property

=============================================

Then, Insert a regular module and place this code there:

=======================================

Option Explicit
Sub TransformData()
    Dim wsSrc As Worksheet 'Data Source
    Dim wsRes As Worksheet, rRes As Range 'Results go here
    Dim vSrc As Variant 'Actual data goes into this array
    Dim vRes() As Variant 'Results will go here before being written to worksheet
    Dim cCTY As Country 'User defined object
    Dim colCountries As Collection
    Dim I As Long, J As Long 'counters

Set wsSrc = Worksheets("Sheet2") '<--change these to whatever
Set wsRes = Worksheets("Sheet3")

Set rRes = wsRes.Range("A1") '<--1st cell of results array

'read data into array
With wsSrc
    vSrc = .Range("A1").CurrentRegion '<--many ways to get this depending on your real data setup
End With

'iterate through Source and create collection of results
Set colCountries = New Collection
For I = 2 To UBound(vSrc, 1) '<--Rows
    For J = 3 To UBound(vSrc, 2) '<--Columns
        Set cCTY = New Country
        With cCTY
            .Name = vSrc(I, 1)
            .Index = vSrc(I, 2)
            .Yr = vSrc(1, J)
            .Quantity = vSrc(I, J)
        End With
        colCountries.Add cCTY
    Next J
Next I

'Results
ReDim vRes(0 To colCountries.Count, 1 To 4)

'Column Labels
vRes(0, 1) = "Country"
vRes(0, 2) = "Index"
vRes(0, 3) = "Year"
vRes(0, 4) = "Value"

For I = 1 To colCountries.Count
    With colCountries(I)
        vRes(I, 1) = .Name
        vRes(I, 2) = .Index
        vRes(I, 3) = .Yr
        vRes(I, 4) = .Quantity
    End With
Next I

Set rRes = rRes.Resize(UBound(vRes, 1) + 1, UBound(vRes, 2))
rRes.EntireColumn.Clear
rRes = vRes
With rRes.Rows(1)
    .Font.Bold = True
    .HorizontalAlignment = xlCenter
End With
rRes.EntireColumn.AutoFit

End Sub

====================================================

Ensure the worksheets and ranges are properly defined to accord with your real setup, and run the macro.