Transform data - Is it transpose??

[keiranwyllie]

Hi everyone, hoping someone can provide some guidance.

Essentially I have rows of data that contain dates of various course completions against staff (one staff member per row). I want to transform that data into columns but transpose doesn't seem to be the most appropriate way I can do this. I've looked at power query too and can't really reach my end state.

Below is an example of the original data and then the way I need it to look. You can see that I need to fill the IDs and Names down the column provided the Cse/Dates are associated with that staff member.

Any help is greatly appreciated.

Surname	First	ID	Phone	Company	Cse 1	Cse 2	Cse 3	Cse 4	Cse 5
Bloggs	Joe	12345	111222	A1	1/07/2021	12/03/2021	1/04/2021	29/07/2021	8/02/2021
Smith	Bob	12435	112233	A1	28/02/2021	3/03/2021	20/05/2021	16/02/2021	30/06/2021

ID	Name	Cse Name	Date
12345	Bloggs,Joe	Cse 1	1/07/2021
12345	Bloggs,Joe	Cse 2	12/03/2021
12345	Bloggs,Joe	Cse 3	1/04/2021
12345	Bloggs,Joe	Cse 4	29/07/2021
12345	Bloggs,Joe	Cse 5	8/02/2021
12435	Smith,Bob	Cse 1	28/02/2021
12435	Smith,Bob	Cse 2	3/03/2021
12435	Smith,Bob	Cse 3	20/05/2021
12435	Smith,Bob	Cse 4	16/02/2021
12435	Smith,Bob	Cse 5	30/06/2021

 

[kennypete]

You're manipulating rather than simply transposing, so something like this does the job:

Sub transposeplus()

Sheet1.Activate
Dim r() As Variant
r = Sheet1.Range("A1").CurrentRegion

Sheet2.Activate
Cells(1, 1) = "ID": Cells(1, 2) = "Name": Cells(1, 3) = "Cse Name": Cells(1, 4) = "Date"

Dim n As Integer
Dim x As Integer
For n = 2 To UBound(r)
    For x = 0 To 4
        Cells(n + x + ((n - 2) * 4), 1) = r(n, 3)
        Cells(n + x + ((n - 2) * 4), 2) = r(n, 1) + "," + r(n, 2)
        Cells(n + x + ((n - 2) * 4), 3) = "Cse " + Trim(Str(x + 1))
        Cells(n + x + ((n - 2) * 4), 4) = r(n, 6 + x)
    Next x
Next n
End Sub

Where your source data is in Sheet1 and you have a blank Sheet2.

 

[Alex Blakenburg]

If you want to use Power Query then

• Data > From Table/Range
• Select the columns from Surname to Company
• Tranform > Unpivot Columns > Unpivot Other Columns

 

[keiranwyllie]

If you want to use Power Query then

• Data > From Table/Range
• Select the columns from Surname to Company
• Tranform > Unpivot Columns > Unpivot Other Columns

Man that Power Query is so simple I now feel like an idiot. It looks the goods and works perfectly on my sample spreadsheet. I'll test on on the real thing and let you know.

 

[Alex Blakenburg]

Thanks for the update. Unpivoting is one of the things it is really good at.
There are some tricks though. It is like recording a macro with certain steps hard coding column names.

In this case it is likely that the second step was Changed Type, this will hard code you Case 1 - 5 and this might later change to being more or less case columns.
Delete that Changed Type step.
After you unpivot, it doesn't matter how may cases you have, you will always have the same number of columns.
So apply a Changed Type step AFTER the unpivot.

• Select anywhere in the grid.
• Transform > Detect data type
• Then review the data types to make sure you are happy.
  I then added a change to make the date just the date (not date & time)
  If a Change Column Type box appears > select Replace Current
  (this merges this Changed Type step with the previous one so you only have one Changed Type step)

 

[antman2988]

D

If you want to use Power Query then

• Data > From Table/Range
• Select the columns from Surname to Company
• Tranform > Unpivot Columns > Unpivot Other Columns

Dang, that's cool. I'm very familiar with Excel and VBA, but I don't know much about Power Query since my work doesn't have access to it. Guess I gotta start learning.

 

[kennypete]

D

Dang, that's cool. I'm very familiar with Excel and VBA, but I don't know much about Power Query since my work doesn't have access to it. Guess I gotta start learning.

Hear hear. I'd used these for API data scraping but hadn't looked into it further.

 

[keiranwyllie]

So to go one step further, if I have training codes allocated to courses plus expiry dates, can I also use power query to manipulate it in the same way?

From this:

data transform.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Surname	First	ID	Phone	Company	Tng Code1	Cse 1	Cse 1 Exp	Tng Code2	Cse 2	Cse 2 Exp	Tng Code3	Cse 3	Cse 3 Exp	Tng Code4	Cse 4	Cse 4 Exp	Tng Code5	Cse 5	Cse 6
2	Bloggs	Joe	12345	111222	A1	Cse1 Code	1/07/2021	1/07/2022	Cse2 Code	12/03/2021	1/07/2022	Cse3 Code	1/04/2021	1/07/2022	Cse4 Code	29/07/2021	1/07/2022	Cse5 Code	8/02/2021	29/07/2021
3	Smith	Bob	12435	112233	A1	Cse1 Code	28/02/2021	28/02/2022	Cse2 Code	3/03/2021	28/02/2022	Cse3 Code	20/05/2021	28/02/2022	Cse4 Code	16/02/2021	28/02/2022	Cse5 Code	30/06/2021	30/07/2021
4	Holden	Roger	54321	221133	A1	Cse1 Code	20/05/2021	20/05/2022	Cse2 Code	16/02/2021	20/05/2022	Cse3 Code	28/02/2021	20/05/2022	Cse4 Code	30/06/2021	20/05/2022	Cse5 Code	3/03/2021	31/07/2021
Sheet1

To This:

data transform.xlsx
	A	B	C	D	E	F	G	H
12	ID	Merged	Phone	Company	Tng Code	Cse	Completed	Expires
13	12345	Bloggs,Joe	111222	A1	Cse1 Code	Cse 1	1/07/2021	1/07/2022
14	12345	Bloggs,Joe	111222	A1	Cse2 Code	Cse 2	12/03/2021	12/03/2022
15	12345	Bloggs,Joe	111222	A1	Cse3 Code	Cse 3	1/04/2021	1/04/2022
16	12345	Bloggs,Joe	111222	A1	Cse4 Code	Cse 4	29/07/2021	29/07/2022
17	12345	Bloggs,Joe	111222	A1	Cse5 Code	Cse 5	8/02/2021	8/02/2022
18	12435	Smith,Bob	112233	A1	Cse1 Code	Cse 1	28/02/2021	28/02/2022
19	12435	Smith,Bob	112233	A1	Cse2 Code	Cse 2	3/03/2021	3/03/2022
20	12435	Smith,Bob	112233	A1	Cse3 Code	Cse 3	20/05/2021	20/05/2022
21	12435	Smith,Bob	112233	A1	Cse4 Code	Cse 4	16/02/2021	16/02/2022
22	12435	Smith,Bob	112233	A1	Cse5 Code	Cse 5	30/06/2021	30/06/2022
23	54321	Holden,Roger	221133	A1	Cse1 Code	Cse 1	20/05/2021	20/05/2022
24	54321	Holden,Roger	221133	A1	Cse2 Code	Cse 2	16/02/2021	16/02/2022
25	54321	Holden,Roger	221133	A1	Cse3 Code	Cse 3	28/02/2021	28/02/2022
26	54321	Holden,Roger	221133	A1	Cse4 Code	Cse 4	30/06/2021	30/06/2022
27	54321	Holden,Roger	221133	A1	Cse5 Code	Cse 5	3/03/2021	3/03/2022
Sheet1

 

[Alex Blakenburg]

Can I assume that Tng Code1 relates to Cse 1 (both 1)
I also assume that Cse1 Code under Tng Code1 is just an example and that it might not be 1 or the exercise seems a bit pointless.

In the headings are you able to make them consistent ?
eg add a space before the number in Tng Code1 same as in "Cse 1"

 

[keiranwyllie]

Can I assume that Tng Code1 relates to Cse 1 (both 1)
I also assume that Cse1 Code under Tng Code1 is just an example and that it might not be 1 or the exercise seems a bit pointless.

In the headings are you able to make them consistent ?
eg add a space before the number in Tng Code1 same as in "Cse 1"

It does. I've just tried to use really basic headings in my example to keep it simple (well hopefully). In my real Data, it's 'Proficiency Code (Cse1)' and 'Cse1'. Basically, the 1's belong together, 2's together and so on (they're different codes for each course).

And yeah, 'Tng Code1' can easily be 'Tng Code 1'.