transpose data query

heathball

Board Regular
Joined
Apr 6, 2017
Messages
112
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
What does your data look like now, do you still have three columns?
 
Upvote 0
Dou you have any cells with formulaic errors such as #N/A, #NA ME, #VALUE etc
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top