Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 68
  • Last Modified:

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
0
ablove3
Asked:
ablove3
2 Solutions
 
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now