Data reordering - Moving Data from mutiple columns into multiple rows

I need to reorder data on the attached spreadsheet.  I need the Date and Amounts from View 1 to line up and look like View 2

View 2 has a date per record and the cumulative hours next to the date, but everything is in one column

I have over 60,000 lines items and 3 tabs to format, so a nice formula to do it quickly would be best,  Thank you
EE---Column-Reordering.xlsx
ablove3Asked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
If you are using Excel 2010 or later, you may consider using Power Query (A free MS Add-in) to get the desired output.
You actually need to unpivot the data.
0
 
Ejgil HedegaardCommented:
1
 
xtermieCommented:
Ejgil has provided a solid solution, however, you may want to consider ordering/labelling your data so that a PIVOT table can be used to do this grouping you want, as there may be may rows/records to index/match
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
ablove3Author Commented:
I was able to find the Pivot Query and it was amazing, but I'm glad that a formula was sent as well, so that I could deconstruct the formula and understand how MATCH/INDEX works
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Here is a Power Query Solution and to me it is more robust and dynamic enough to incorporate more data you add in the Table1 on the sheet other than 60000 rows of existing data.
After adding more data, you just need to right click the Table on Sheet2 and choose Refresh and the new data will be reflected in the table on sheet2 automatically.

See if that helps.
EE---Column-Reordering.xlsx
0
 
ablove3Author Commented:
I was able to find the Pivot Query and it was amazing and took seconds, but I'm glad that a formula was sent as well, so that I could deconstruct the formula and understand how MATCH/INDEX works
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad we could help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.