Changing Multiple Line to Single line

[SeamusBo]

Hi Guys
I have an excel sheet where a User Name is appearing on multiple lines (See below)

And we want it to appear as below

 

[SeamusBo]

Book1
	A	B	C	J
1	Exported On: 16/08/2019 15:40:57
2	Employees.Forename	Employees.Surname	PaymentHistory.PaymentHrsNoCur	PaymentHistory.PaymentDescription
3	User	one	45	Basic Pay
4	User	one	10.03	Overtime
5	User	two	38.5	Basic Pay
6	User	three	45	Basic Pay
7	User	three	10.25	Overtime
8	User	Four	45	ARTIC RATE
9	User	Four	9.03	ARTIC OVERTIME
10	User	Five	24	Basic Pay
11	User	six	24	Basic Pay
12	User	Seven	45	Basic Pay
13	User	Seven	4	Overtime
14	User	Eight	45	ARTIC RATE
15	User	Nine	43	Basic Pay
16	User	Ten	45	Basic Pay
17	User	Ten	2.12	Overtime
18	User	Eleven	44	Basic Pay
19	User	Twelve	44	Basic Pay
20	User	Fourteen	44	Basic Pay
21	User	Fifteen	45	Basic Pay
22	User	Fifteen	11.12	Overtime
23	User	Sixteen	45	Basic Pay
24	User	Sixteen	5.24	Overtime
25	User	Sixteen	5.53	Time and a half
Raw Data

 

[jasonb75]

You could use power query to pivot column J into new headers, aggregating the values in column C.

I can do it, but I don't know power query well enough to try and explain it. Hopefully this will help a bit https://support.office.com/en-ie/article/pivot-columns-abc9c8da-3be9-44c4-886e-0be331ab387a

 

[SeamusBo]

I can't get the power query to work for this

 

[jasonb75]

I just copied your data to a blank sheet, lined up to match the rows and columns as shown in your example, then got the results as shown in your other thread by using the following steps.

-Go to the Data tab on the excel ribbon, then choose 'From Table/Range'.
-In the popup box, enter =$A$2:$J$25 and tick the headers box.
-In the PQ editor, select the 'PaymentHistory.PaymentDescription' column.
-Go to the Transform tab, then choose 'Pivot Column'.
-Change the dropdown to 'PaymentHistory.PaymentHrsNoCur'.
-Close the editor and Keep changes.

 

[SeamusBo]

I just copied your data to a blank sheet, lined up to match the rows and columns as shown in your example, then got the results as shown in your other thread by using the following steps.

-Go to the Data tab on the excel ribbon, then choose 'From Table/Range'.
-In the popup box, enter =$A$2:$J$25 and tick the headers box.
-In the PQ editor, select the 'PaymentHistory.PaymentDescription' column.
-Go to the Transform tab, then choose 'Pivot Column'.
-Change the dropdown to 'PaymentHistory.PaymentHrsNoCur'.
-Close the editor and Keep changes.

Tried this and it is not quite right. I want all the entries for each user on one line so the basic pay for user one would be 45 and the overtime would be 10.03 but on the one line

 

[jasonb75]

That is exactly what I got, if it's not working for you then there must be something else preventing it. Quite possibly items in columns C to H that the query can not link correctly.