Puzzler

[tljenkin]

I have a record thus:


Name Tag Week 1 Week 2

Peter hsdk £5 £10

How do I transpose this automatically so that I get the following results:

Week Name Tag Amount

Week 1 Peter hsdk £5
Week 2 Peter hsdk £10

Thanks

 

[tljenkin]

try sending me a private email now. I will try running the macro again with row 1 hidden, I didnt do that previously. I had the original data starting from A2

Thanks

 

[tljenkin]

Its work, I tested it again, many many thanks! You are a star!!

Just for the future, if I ever wanted to add an extra column of data, would this be as simply as incrementing all the numbers by one and "redimming" O for that field?

eg as below

Option Explicit
Option Base 1
Sub ReorgDataV2()
' hiker95, 10/01/2011
' http://www.mrexcel.com/forum/showthread.php?t=581001
Dim w1 As Worksheet, wR As Worksheet
Dim I(), O()
Dim LR As Long, LC As Long, r As Long, c As Long, n As Long
Set w1 = Worksheets("Sheet1")
LR = w1.Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
LC = w1.Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
I = w1.Range("A2").CurrentRegion.Resize(, LC).Value
ReDim O(1 To (LC - 12 + 1) * (LR - 1) + 1, 1 To 13)
O(1, 1) = "Week"
O(1, 2) = "Seq"
O(1, 3) = "Unique"
O(1, 4) = "Type"
O(1, 5) = "Regime"
O(1, 6) = "Cohort"
O(1, 7) = "Heritage"
O(1, 8) = "Prod Area"
O(1, 9) = "Channel"
O(1, 10) = "Pro/Re"
O(1, 11) = "Direct/CMC"
O(1, 12) = "Total Complaints"
O(1, 13) = "xxxxx"

n = 1
For r = 2 To UBound(I) Step 1
  For c = 12 To LC Step 1
    n = n + 1
    O(n, 1) = I(1, c)
    O(n, 2) = I(r, 1)
    O(n, 3) = I(r, 2)
    O(n, 4) = I(r, 3)
    O(n, 5) = I(r, 4)
    O(n, 6) = I(r, 5)
    O(n, 7) = I(r, 6)
    O(n, 8) = I(r, 7)
    O(n, 9) = I(r, 8)
    O(n, 10) = I(r, 9)
    O(n, 11) = I(r, 10)
    O(n, 12) = I(r, 11)
    O(n, 13) = I(r, c)
  Next c
Next r
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
wR.Range("A1").Resize(UBound(O), 13).Value = O
wR.UsedRange.Columns.AutoFit
wR.Activate
End Sub

 

[hiker95]

tljenkin,

Thanks for the feedback.

You are very welcome.

Glad I could help.

Just for the future, if I ever wanted to add an extra column of data, would this be as simply as incrementing all the numbers by one and "redimming" O for that field?

No, it would all depend on where the extra column is.

 

[tljenkin]

Ok thanks again, you are definitely an excel guru

I managed to do a trial and error and eventually got it to work for more columns