Normalisation of data...

[dannymccann]

Hi all,

The data set in question is from this challenge:

http://chandoo.org/wp/2009/11/11/visualizing-salesdata-challenge/

Columns A - D have tabular data and then we have months from E - AB.

I have tried using the Alt+D+P method of multiple consolidation ranges to do this but can't seem to get it to work...can anyone shed any light on this?

Thanks

 

[NARAYANK991]

Hi ,

Can you explain what you want to do ? The title of the post uses the word normalization , but I am not able to understand.

Narayan

 

[dannymccann]

Maybe I am misunderstanding the terminology...

I've uploaded a spreadsheet with 2 tabs, one is the original data and the other is what I am trying to achieve

Thanks!

 

[Nebu]

Hi:

Try this macro

Sub test()

Application.ScreenUpdating = False

Dim myarray As Variant, myarray1 As Variant

i& = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column - 3
j& = Sheet2.Cells(Rows.Count, "E").End(xlUp).Row + 1

For c& = 2 To Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
    myarray1 = Sheet1.Range("E1:AB1").Value
    Sheet2.Range("E" & j & ":E" & i).Value = Application.WorksheetFunction.Transpose(myarray1)
    myarray = Sheet1.Range("E" & c & ":AB" & c).Value
    Sheet2.Range("F" & j & ":F" & i).Value = Application.WorksheetFunction.Transpose(myarray)
    Sheet1.Range("A" & c & ":D" & c).Copy
    Sheet2.Range("A" & j & ":D" & i).PasteSpecial
    Application.CutCopyMode = False
    j& = Sheet2.Cells(Rows.Count, "E").End(xlUp).Row + 1
    i& = i + j - 2
Next

Application.ScreenUpdating = True

End Sub