VBA to quickly transpose data

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,240
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Guys,

I have data set that i receive daily that comes in this format

1593527146918.png


What i need to do is transpose the data so it looks like this

1593527316344.png


So the Time and data transposed by column for each day

This is taking me forever to do and would really appreciate your help

Thank You
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,152
Office Version
  1. 365
Platform
  1. Windows
Cannot manipulate pictures of data. Please reload the data using XL2BB. This will be an easy unpivot using Power Query, but to demonstrate, I need to upload the data.
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,240
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hiya

whatsXL2BB?

Im trying to replicate but each time i copy its pasting as picture:(

If you can guide us through it with1 or 2 lines of samp data would really appreciate that

thank you
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
whatsXL2BB?

 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,240
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1Skill GroupSkillMetricsDate01234567891011121314151617181920212223
2ScotlandScotland-messagingConversations23/06/2020671686431498914881846342965929057629896785530
3ScotlandScotland-messagingConversations24/06/20204348942036447743115672986540659978591488077721
4ScotlandScotland-messagingConversations25/06/2020981879989529532579769021608993626132##762264471
5ScotlandScotland-messagingConversations26/06/202027581274826986539156402674883232414646565139117
6ScotlandScotland-messagingConversations27/06/20203840727847619518196697645326075614128956057934
7ScotlandScotland-messagingConversations28/06/20209630885883235899065198912151633175632347801896
8ScotlandScotland-messagingConversations29/06/2020622397421885848265037101333513428333151219383
9EnglandEngland-MessagingConversations23/06/20209721267246306563381445244931701228701634558
10EnglandEngland-MessagingConversations24/06/2020583848398229474466126427573346852754463333261029
11EnglandEngland-MessagingConversations25/06/20207489841867571430257040361056893356471672995160
12EnglandEngland-MessagingConversations26/06/20201350963444382198364207843471718592788381667
13EnglandEngland-MessagingConversations27/06/20202225433207419624476891546196341592532713181
14EnglandEngland-MessagingConversations28/06/20203640769266551792290497859151985546228199741856
15EnglandEngland-MessagingConversations29/06/202072266104031133668435344396936781817881314101944
Sheet1
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,152
Office Version
  1. 365
Platform
  1. Windows
Using Power Query/Get and Transform, it is a matter of just unpivoting the columns. Here is the Mcode and small excerpt of the data as it is over 300 lines long

Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FormatDataType = Table.TransformColumnTypes(Source,{{"Skill Group", type text}, {"Skill", type text}, {"Metrics", type text}, {"Date", type text}, {"0", Int64.Type}, {"1", Int64.Type}, {"2", Int64.Type}, {"3", Int64.Type}, {"4", Int64.Type}, {"5", Int64.Type}, {"6", Int64.Type}, {"7", Int64.Type}, {"8", Int64.Type}, {"9", Int64.Type}, {"10", Int64.Type}, {"11", Int64.Type}, {"12", Int64.Type}, {"13", Int64.Type}, {"14", Int64.Type}, {"15", Int64.Type}, {"16", Int64.Type}, {"17", Int64.Type}, {"18", Int64.Type}, {"19", Int64.Type}, {"20", Int64.Type}, {"21", Int64.Type}, {"22", Int64.Type}, {"23", Int64.Type}}),
    UnpivotColumns = Table.UnpivotOtherColumns(FormatDataType, {"Skill Group", "Skill", "Metrics", "Date"}, "Attribute", "Value")
in
    UnpivotColumns

Review PQ steps
M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced

Book22
ABCDEF
1Skill GroupSkillMetricsDateAttributeValue
2ScotlandScotland-messagingConversations23/06/2020067
3ScotlandScotland-messagingConversations23/06/2020116
4ScotlandScotland-messagingConversations23/06/2020286
5ScotlandScotland-messagingConversations23/06/2020343
6ScotlandScotland-messagingConversations23/06/2020414
7ScotlandScotland-messagingConversations23/06/2020598
8ScotlandScotland-messagingConversations23/06/2020691
9ScotlandScotland-messagingConversations23/06/202074
10ScotlandScotland-messagingConversations23/06/202088
11ScotlandScotland-messagingConversations23/06/2020981
12ScotlandScotland-messagingConversations23/06/20201084
13ScotlandScotland-messagingConversations23/06/2020116
14ScotlandScotland-messagingConversations23/06/20201234
15ScotlandScotland-messagingConversations23/06/20201329
16ScotlandScotland-messagingConversations23/06/20201465
17ScotlandScotland-messagingConversations23/06/20201592
18ScotlandScotland-messagingConversations23/06/20201690
19ScotlandScotland-messagingConversations23/06/20201757
20ScotlandScotland-messagingConversations23/06/20201862
21ScotlandScotland-messagingConversations23/06/20201998
22ScotlandScotland-messagingConversations23/06/20202096
23ScotlandScotland-messagingConversations23/06/20202178
Sheet3
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,240
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Thank you so much although i don’t understand the full code (thank you for explaining each step - is there any chance you can explain the formatdatatype and unpivot code please...

I need to run this every day or someone from my team will run this every day by importing in the data and pasting into cell A1 on sheet Data...

Can i link this code to a macro button that does the step you provided?

so copy data from Data tab and Paste the reformatted view on the sheet called Reformatted?

that way i can get the users to press a button to reformat and keep original so i can overide each day
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,240
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Sorry if ive not explained it properly

a user will down load the data and paste it over the original data each day...

what ill need is to refresh the powerpivot to transform the way you have kindly suggested but and save over existing location which i believe it already does...

Just a couple of things..

1).i need this refresh attached to a button because what i then do is load this reformatted data into my database each day which i can add to the VBA code once refreshed..

2) where i have the attributes (well its time) i want to change to values from 0,1,2,3 etc to times all the way to 23:00

ie 00:00, 01:00, 02:00.....22:00,23:00..is this also possible to do with M Code?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
Try this

Your data on Sheet1 the results on Sheet2
VBA Code:
Sub transpose_data()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Sheets("Sheet1").Range("A2:AB" & Sheets("Sheet1").Range("A" & Rows.Count).End(3).Row).Value2
  ReDim b(1 To UBound(a) * 24, 1 To 6)
  
  For i = 1 To UBound(a, 1)
    For j = 5 To 28
      k = k + 1
      b(k, 1) = a(i, 1)
      b(k, 2) = a(i, 2)
      b(k, 3) = a(i, 3)
      b(k, 4) = a(i, 4)
      b(k, 5) = Format(j - 5, "00:00")
      b(k, 6) = a(i, j)
    Next j
  Next i
  Sheets("Sheet2").Range("A2").Resize(UBound(b), 6).Value = b
End Sub
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,152
Office Version
  1. 365
Platform
  1. Windows
If you use the same spreadsheet each day and only update the table, then all you need to do is double click on the Refresh Button on the Data Tab and your updated output will automatically appear. No need to reformat or need new code. Refresh is all you need to do. If you wish to add a button to the page, then build the button and record a macro to refresh all and link the button to the code.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,334
Messages
5,624,084
Members
416,010
Latest member
NJT

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
Top