VBA to quickly transpose data

[mahmed1]

Hi Guys,

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

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

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

 

[alansidman]

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]

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]

whatsXL2BB?

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[mahmed1]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Skill Group	Skill	Metrics	Date	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23
2	Scotland	Scotland-messaging	Conversations	23/06/2020	67	16	86	43	14	98	91	4	8	81	84	6	34	29	65	92	90	57	62	98	96	78	55	30
3	Scotland	Scotland-messaging	Conversations	24/06/2020	43	48	94	20	36	44	77	43	11	56	72	98	65	40	65	99	78	59	1	48	80	7	77	21
4	Scotland	Scotland-messaging	Conversations	25/06/2020	98	18	79	98	95	29	53	25	79	76	90	21	60	89	93	62	61	32	##	76	22	6	44	71
5	Scotland	Scotland-messaging	Conversations	26/06/2020	27	58	12	74	82	69	86	53	91	56	40	26	74	88	32	3	24	14	64	65	65	13	91	17
6	Scotland	Scotland-messaging	Conversations	27/06/2020	38	40	72	7	84	76	19	51	81	96	69	76	45	32	60	75	61	41	28	95	60	5	79	34
7	Scotland	Scotland-messaging	Conversations	28/06/2020	96	30	88	5	88	32	35	89	90	65	1	98	91	21	51	63	31	75	63	23	47	80	18	96
8	Scotland	Scotland-messaging	Conversations	29/06/2020	62	23	9	74	21	88	58	48	26	50	37	10	13	33	5	13	4	28	33	31	51	21	93	83
9	England	England-Messaging	Conversations	23/06/2020	97	21	2	67	2	46	30	65	63	38	14	45	24	49	31	70	12	28	70	1	63	45	5	8
10	England	England-Messaging	Conversations	24/06/2020	58	38	48	39	82	29	47	44	66	12	64	27	57	33	46	85	27	54	46	33	33	26	10	29
11	England	England-Messaging	Conversations	25/06/2020	7	48	9	84	18	67	57	14	30	25	70	40	36	10	56	89	33	56	47	16	72	99	51	60
12	England	England-Messaging	Conversations	26/06/2020	13	50	9	63	4	44	38	21	9	83	64	20	78	43	4	71	71	8	59	27	88	38	16	67
13	England	England-Messaging	Conversations	27/06/2020	22	2	54	33	20	7	41	96	2	44	76	89	15	46	19	63	4	15	92	53	27	13	18	1
14	England	England-Messaging	Conversations	28/06/2020	36	40	76	9	26	65	51	79	22	90	49	78	5	91	51	98	55	46	22	81	99	74	18	56
15	England	England-Messaging	Conversations	29/06/2020	7	22	66	10	40	31	13	36	68	43	53	44	39	69	36	78	18	17	88	13	14	10	19	44
Sheet1

 

[alansidman]

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

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
	A	B	C	D	E	F
1	Skill Group	Skill	Metrics	Date	Attribute	Value
2	Scotland	Scotland-messaging	Conversations	23/06/2020	0	67
3	Scotland	Scotland-messaging	Conversations	23/06/2020	1	16
4	Scotland	Scotland-messaging	Conversations	23/06/2020	2	86
5	Scotland	Scotland-messaging	Conversations	23/06/2020	3	43
6	Scotland	Scotland-messaging	Conversations	23/06/2020	4	14
7	Scotland	Scotland-messaging	Conversations	23/06/2020	5	98
8	Scotland	Scotland-messaging	Conversations	23/06/2020	6	91
9	Scotland	Scotland-messaging	Conversations	23/06/2020	7	4
10	Scotland	Scotland-messaging	Conversations	23/06/2020	8	8
11	Scotland	Scotland-messaging	Conversations	23/06/2020	9	81
12	Scotland	Scotland-messaging	Conversations	23/06/2020	10	84
13	Scotland	Scotland-messaging	Conversations	23/06/2020	11	6
14	Scotland	Scotland-messaging	Conversations	23/06/2020	12	34
15	Scotland	Scotland-messaging	Conversations	23/06/2020	13	29
16	Scotland	Scotland-messaging	Conversations	23/06/2020	14	65
17	Scotland	Scotland-messaging	Conversations	23/06/2020	15	92
18	Scotland	Scotland-messaging	Conversations	23/06/2020	16	90
19	Scotland	Scotland-messaging	Conversations	23/06/2020	17	57
20	Scotland	Scotland-messaging	Conversations	23/06/2020	18	62
21	Scotland	Scotland-messaging	Conversations	23/06/2020	19	98
22	Scotland	Scotland-messaging	Conversations	23/06/2020	20	96
23	Scotland	Scotland-messaging	Conversations	23/06/2020	21	78
Sheet3

 

[mahmed1]

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]

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]

Try this

Your data on Sheet1 the results on Sheet2

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]

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.