Changing Multiple Line to Single line

SeamusBo

New Member
Joined
Sep 12, 2019
Messages
6
Hi Guys
I have an excel sheet where a User Name is appearing on multiple lines (See below)

clip_image002.jpg

And we want it to appear as below

clip_image004.jpg
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Book1
ABCJ
1Exported On: 16/08/2019 15:40:57
2Employees.ForenameEmployees.SurnamePaymentHistory.PaymentHrsNoCurPaymentHistory.PaymentDescription
3Userone45Basic Pay
4Userone10.03Overtime
5Usertwo38.5Basic Pay
6Userthree45Basic Pay
7Userthree10.25Overtime
8UserFour45ARTIC RATE
9UserFour9.03ARTIC OVERTIME
10UserFive24Basic Pay
11Usersix24Basic Pay
12UserSeven45Basic Pay
13UserSeven4Overtime
14UserEight45ARTIC RATE
15UserNine43Basic Pay
16UserTen45Basic Pay
17UserTen2.12Overtime
18UserEleven44Basic Pay
19UserTwelve44Basic Pay
20UserFourteen44Basic Pay
21UserFifteen45Basic Pay
22UserFifteen11.12Overtime
23UserSixteen45Basic Pay
24UserSixteen5.24Overtime
25UserSixteen5.53Time and a half
Raw Data
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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