transpose data query

heathball

Board Regular
Joined
Apr 6, 2017
Messages
63
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:confused:

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.



datenumbernamedate rank (most recent on top)
15/04/201834.4peter1
22/03/201834.6peter2
23/12/201738.1peter3
6/12/201736peter4
18/11/201736.9peter5
23/09/201740.9peter6
23/08/201740.3peter7
31/12/201633.8peter8
9/09/201852.5mary1
26/08/201849.8mary2
3/10/201852.5mary3
23/09/201850.2mary4
2/05/201842.7mary5
15/01/201641steve1
28/12/201541steve2
8/12/201527.1steve3
28/11/201537.3steve4
9/11/201538.5steve5
5/12/201729rod1
23/11/201730.9rod2
25/07/201730.2rod3
6/07/201741.7rod4
24/09/201631.5Ian1
3/09/201632.6Ian2
27/08/201637.2Ian3
11/06/201630.5Ian4
21/05/201642.3Ian5
25/04/201637.6Ian6
15/04/201642.4Ian7
24/10/201539.6Ian8
2/02/201845.6Julie1
24/01/201841.7Julie2
17/12/201740.2Julie3
24/11/201743.3Julie4
19/03/201748.3Julie5
3/03/201742.1Julie6
19/02/201746.9Julie7
29/01/201738.5Julie8
6/01/201746.2Julie9
18/12/201647Julie10
25/11/201645Julie11
13/11/201643.5Julie12
4/03/201642.9Julie13
21/02/201646.9Julie14
31/01/201643.6Julie15
20/12/201541.3Julie16

<tbody>
</tbody>


Desired result.....

name12345678910111213141516
peter34.434.638.13636.940.940.333.8
mary52.549.852.550.242.7
steve414127.137.338.5
rod2930.930.241.7
Ian31.532.637.230.542.337.642.439.6
Julie45.641.740.243.348.342.146.938.546.2474543.542.946.943.641.3

<tbody>
</tbody>
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,249
try PowerQuery aka Get&Transform
Code:
[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
 
Last edited:

heathball

Board Regular
Joined
Apr 6, 2017
Messages
63
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,732
Office Version
365
Platform
Windows
How about
Code:
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

Board Regular
Joined
Apr 6, 2017
Messages
63
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

Board Regular
Joined
Apr 6, 2017
Messages
63
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,732
Office Version
365
Platform
Windows
What does your data look like now, do you still have three columns?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,732
Office Version
365
Platform
Windows
Dou you have any cells with formulaic errors such as #N/A, #NA ME, #VALUE etc
 

Forum statistics

Threads
1,085,545
Messages
5,384,371
Members
401,890
Latest member
Angela7

Some videos you may like

This Week's Hot Topics

Top