transpose data query

[heathball]

i have been working on an efficient way to get this excel data to transpose in the way that appears underneath it, at the bottom of the post
the way i do involves several steps and its painfully slow, and the data set can be large, and as i need to do it 3 times a week, I'm hoping for a faster solution

Cells are all pasted values, no formulas. There may be values of up to 150 in the 'date rank' column.

I'm looking to transpose this data onto a separate sheet in the same work book.

I'm especially interested in a VB code that i can apply as a macro.

Thanks in advance if anybody can offer some assistance.



[TABLE="width: 382"]
<tbody>[TR]
[TD]date[/TD]
[TD]number[/TD]
[TD]name[/TD]
[TD]date rank (most recent on top)[/TD]
[/TR]
[TR]
[TD]15/04/2018[/TD]
[TD]34.4[/TD]
[TD]peter[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]22/03/2018[/TD]
[TD]34.6[/TD]
[TD]peter[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]23/12/2017[/TD]
[TD]38.1[/TD]
[TD]peter[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]6/12/2017[/TD]
[TD]36[/TD]
[TD]peter[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]18/11/2017[/TD]
[TD]36.9[/TD]
[TD]peter[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]23/09/2017[/TD]
[TD]40.9[/TD]
[TD]peter[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]23/08/2017[/TD]
[TD]40.3[/TD]
[TD]peter[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]31/12/2016[/TD]
[TD]33.8[/TD]
[TD]peter[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]9/09/2018[/TD]
[TD]52.5[/TD]
[TD]mary[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]26/08/2018[/TD]
[TD]49.8[/TD]
[TD]mary[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]3/10/2018[/TD]
[TD]52.5[/TD]
[TD]mary[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]23/09/2018[/TD]
[TD]50.2[/TD]
[TD]mary[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]2/05/2018[/TD]
[TD]42.7[/TD]
[TD]mary[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]15/01/2016[/TD]
[TD]41[/TD]
[TD]steve[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]28/12/2015[/TD]
[TD]41[/TD]
[TD]steve[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]8/12/2015[/TD]
[TD]27.1[/TD]
[TD]steve[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]28/11/2015[/TD]
[TD]37.3[/TD]
[TD]steve[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]9/11/2015[/TD]
[TD]38.5[/TD]
[TD]steve[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]5/12/2017[/TD]
[TD]29[/TD]
[TD]rod[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]23/11/2017[/TD]
[TD]30.9[/TD]
[TD]rod[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]25/07/2017[/TD]
[TD]30.2[/TD]
[TD]rod[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]6/07/2017[/TD]
[TD]41.7[/TD]
[TD]rod[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]24/09/2016[/TD]
[TD]31.5[/TD]
[TD]Ian[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]3/09/2016[/TD]
[TD]32.6[/TD]
[TD]Ian[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]27/08/2016[/TD]
[TD]37.2[/TD]
[TD]Ian[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]11/06/2016[/TD]
[TD]30.5[/TD]
[TD]Ian[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]21/05/2016[/TD]
[TD]42.3[/TD]
[TD]Ian[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]25/04/2016[/TD]
[TD]37.6[/TD]
[TD]Ian[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]15/04/2016[/TD]
[TD]42.4[/TD]
[TD]Ian[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]24/10/2015[/TD]
[TD]39.6[/TD]
[TD]Ian[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]2/02/2018[/TD]
[TD]45.6[/TD]
[TD]Julie[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]24/01/2018[/TD]
[TD]41.7[/TD]
[TD]Julie[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]17/12/2017[/TD]
[TD]40.2[/TD]
[TD]Julie[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]24/11/2017[/TD]
[TD]43.3[/TD]
[TD]Julie[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]19/03/2017[/TD]
[TD]48.3[/TD]
[TD]Julie[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]3/03/2017[/TD]
[TD]42.1[/TD]
[TD]Julie[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]19/02/2017[/TD]
[TD]46.9[/TD]
[TD]Julie[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]29/01/2017[/TD]
[TD]38.5[/TD]
[TD]Julie[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]6/01/2017[/TD]
[TD]46.2[/TD]
[TD]Julie[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]18/12/2016[/TD]
[TD]47[/TD]
[TD]Julie[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]25/11/2016[/TD]
[TD]45[/TD]
[TD]Julie[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]13/11/2016[/TD]
[TD]43.5[/TD]
[TD]Julie[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]4/03/2016[/TD]
[TD]42.9[/TD]
[TD]Julie[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]21/02/2016[/TD]
[TD]46.9[/TD]
[TD]Julie[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]31/01/2016[/TD]
[TD]43.6[/TD]
[TD]Julie[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]20/12/2015[/TD]
[TD]41.3[/TD]
[TD]Julie[/TD]
[TD="align: right"]16[/TD]
[/TR]
</tbody>[/TABLE]


Desired result.....

[TABLE="width: 1139"]
<tbody>[TR]
[TD]name[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]peter[/TD]
[TD="align: right"]34.4[/TD]
[TD="align: right"]34.6[/TD]
[TD="align: right"]38.1[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]36.9[/TD]
[TD="align: right"]40.9[/TD]
[TD="align: right"]40.3[/TD]
[TD="align: right"]33.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]mary[/TD]
[TD="align: right"]52.5[/TD]
[TD="align: right"]49.8[/TD]
[TD="align: right"]52.5[/TD]
[TD="align: right"]50.2[/TD]
[TD="align: right"]42.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]steve[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]27.1[/TD]
[TD="align: right"]37.3[/TD]
[TD="align: right"]38.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]rod[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]30.9[/TD]
[TD="align: right"]30.2[/TD]
[TD="align: right"]41.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ian[/TD]
[TD="align: right"]31.5[/TD]
[TD="align: right"]32.6[/TD]
[TD="align: right"]37.2[/TD]
[TD="align: right"]30.5[/TD]
[TD="align: right"]42.3[/TD]
[TD="align: right"]37.6[/TD]
[TD="align: right"]42.4[/TD]
[TD="align: right"]39.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Julie[/TD]
[TD="align: right"]45.6[/TD]
[TD="align: right"]41.7[/TD]
[TD="align: right"]40.2[/TD]
[TD="align: right"]43.3[/TD]
[TD="align: right"]48.3[/TD]
[TD="align: right"]42.1[/TD]
[TD="align: right"]46.9[/TD]
[TD="align: right"]38.5[/TD]
[TD="align: right"]46.2[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]43.5[/TD]
[TD="align: right"]42.9[/TD]
[TD="align: right"]46.9[/TD]
[TD="align: right"]43.6[/TD]
[TD="align: right"]41.3[/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

try PowerQuery aka Get&Transform

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"name"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "number", each Table.Column([Count],"number")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"number", each Text.Combine(List.Transform(_, Text.From), "#"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "number", Splitter.SplitTextByDelimiter("#", QuoteStyle.Csv), {"number.1", "number.2", "number.3", "number.4", "number.5", "number.6", "number.7", "number.8", "number.9", "number.10", "number.11", "number.12", "number.13", "number.14", "number.15", "number.16"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"number.1", type number}, {"number.2", type number}, {"number.3", type number}, {"number.4", type number}, {"number.5", type number}, {"number.6", type number}, {"number.7", type number}, {"number.8", type number}, {"number.9", type number}, {"number.10", Int64.Type}, {"number.11", Int64.Type}, {"number.12", type number}, {"number.13", type number}, {"number.14", type number}, {"number.15", type number}, {"number.16", type number}})
in
    #"Changed Type1"[/SIZE]

---
Ooops, I missed note about VBA, so ignore this post

 

[heathball]

Thanks Sandy, i got that to work in power query. I also got it to work in Power Pivot, which I find easier.

Im just thinking about a quicker option that i may be able to include in a macro.

Thanks for your post

 

[sandy666]

No problem
Have a nice day

 

[Fluff]

How about

Sub TransposeData()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Range("A1").CurrentRegion.value2
   With CreateObject("scripting.dictionary")
      For i = 2 To UBound(Ary)
         .Item(Ary(i, 3)) = Ary(i, 2) & "|" & .Item(Ary(i, 3))
      Next i
      Sheets("sheet2").Range("A2").Resize(.Count).Value = Application.Transpose(.keys)
      Sheets("sheet2").Range("B2").Resize(.Count).Value = Application.Transpose(.items)
      Sheets("sheet2").Range("B2").Resize(.Count).TextToColumns Range("B2"), xlDelimited, _
        xlDoubleQuote, False, False, False, False, False, True, "|"
   End With
End Sub

 

[heathball]

Thanks Fluff,
works super quick!
I can see i can discard with the numbered date order column with this code.

I reversed the date order to descending, as i wanted the most recent to be on the far left, and its interesting how it works not from the actual date, but 'down the page'
- for eg, when i changed the dates around, it used the row order, rather than the date itself.

Then i tried it with an empty date column (column A), and it still worked!, even though the code includes references to A1 and A2.
Interesting. i will just make sure the data is in descending date order first.

Much appreciated!

 

[heathball]

Sorry to trouble you again, Fluff.

I had a few issues with the code. I set up a new macro file.

I'm encountering an error

run time error 13
type - mismatch

this is the part of the code which is in yellow
.Item(Ary(i, 3)) = Ary(i, 2) & "|" & .Item(Ary(i, 3))

I have tried several things and nothing seems to work?

 

[Fluff]

What does your data look like now, do you still have three columns?

 

[heathball]

yes, still 3 columns the same as before

 

[Fluff]

Dou you have any cells with formulaic errors such as #N/A, #NA ME, #VALUE etc