Normalising Data in Excel with VBA

[deusextra]

23243

Please see the attached file. I would like to rearrange the data in 'Target' to be in the format shown in 'TableData' for all workers. (Example only shows for 3 workers).

I know this can be done via VBA and I'll need to do this on a table with 80 workers and 75 customers eventually so if someone could provide the code for the sample file and I will modify it for the larger sample.

Thanks in advance to anyone who can help with this.

[Paul_Hossler]

Try this



Option Explicit
Sub NormalizeData()
Dim rowIn As Long, colIn As Long, rowOut As Long
Dim rIn As Range

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("TableData").Delete
Application.DisplayAlerts = True
On Error GoTo 0

ActiveWorkbook.Worksheets.Add
ActiveSheet.Name = "TableData"

Set rIn = Worksheets("Target").Cells(1, 1).CurrentRegion

rowOut = 1
With Worksheets("TableData")
.Cells(rowOut, 1).Value = "Worker"
.Cells(rowOut, 2).Value = "Type"
.Cells(rowOut, 3).Value = "Customer"
.Cells(rowOut, 4).Value = "Time Worked"
rowOut = rowOut + 1

For rowIn = 2 To rIn.Rows.Count
For colIn = 3 To rIn.Columns.Count
If rIn.Cells(rowIn, colIn).Value > 0 Then
.Cells(rowOut, 1) = rIn.Rows(rowIn).EntireRow.Cells(1).Value
.Cells(rowOut, 2) = rIn.Rows(rowIn).EntireRow.Cells(2).Value
.Cells(rowOut, 3) = rIn.Columns(colIn).EntireColumn.Cells(1).Value
.Cells(rowOut, 4) = rIn.Cells(rowIn, colIn).Value

rowOut = rowOut + 1
End If


Next colIn
Next rowIn

End With

End Sub

[snb]

Sub M_snb()
sn = Sheet7.Cells(1).CurrentRegion

ReDim sp(UBound(sn) * UBound(sn, 2), 3)

For j = 2 To UBound(sn)
For jj = 3 To UBound(sn, 2)
sp(y, 0) = sn(j, 1)
sp(y, 1) = sn(j, 2)
sp(y, 2) = sn(1, jj)
sp(y, 3) = sn(j, jj)
y = y + 1
Next
Next

Sheet2.Cells(1, 8).Resize(UBound(sp), UBound(sp, 2) + 1) = sp
End Sub

You don't have to adapt this code when more records are involved.
Alwasy use Arrays to avoid worksheet interaction: reading/writing

[Jan Karel Pieterse]

Easy-peasy with a pivottable so why use VBA?

[Paul_Hossler]

Actually, I don't think you could make a really effective PT from the original data structure (do-able, but seemed like it was less flexible

23248


I took the purpose of the request to make a macro to reformat into a more PT-friendly structure

My macro makes a (IMHO at least) a more PT-friendly second sheet, as well as not including the 0 values


23249


Samples (for the OP, not JKP)

23250

[snb]

@JKP

I am curious with what you will come up with.

In this case the 'Tabledata' is the target, not the 'Target'.

[Jan Karel Pieterse]

Apologies, I misread the question. Instead of VBA, this should be quite simple using PowerQuery, unpivot. See attached.