Forum Discussion
I need help with transforming data
I have been trying to move my data automatically instead of copy paste cells one by one, but because my data has lots of info, I'm failing in doing so.
I added a picture, I only made 6 for the example but I have over 50 agents. I want this data to go automatically in the other format from the picture. So this other format of the table should show how many hours each of them has worked each day.
As you can see I need to move the data from the first picture (different table formatting) to the other one.
I have over 50 agents, and their time worked is put one by one based on the date. Now on the second one (the one I need to be filled automatically), the table is set on the way where I put the hours worked by agent, one by one for one month.
How do I transform my data into that?
What's the best formula to use?
Please help!
Are you using Excel for Web or Desktop (some functions behave slightly differently)?
Do you want the transformation live (updates automatically as new data comes in), or just one-time?
Would you prefer a Power Query solution? That’s more robust for automation and big data.
You're already successfully sharing images, but to get help quickly and effectively here in the forum, a file would be very helpful.
Upload an Excel file (.xlsx).
Include the following:
Table 1 – the raw agent log data (as in your second image).
Table 2 – your spreadsheet format (as in your first image, even if it's just a template).
Optional: Add a few rows of dummy data (e.g., 5-10 agents) if the actual data is confidential.
After uploading the file, anyone who wants to help could do the following:
Analyze your layout
Create the exact formula or steps to automate the process
Provide a working example with formulas or PivotTable configuration
If needed, a Power Query version “may” also be available (especially useful with over 50 agents). It's more work, but there are plenty of helpers here 🙂.
Thank you for your understanding and patience.
