0

I'm tying to convert a matrix of data into a list of entries. I have found a few solutions that are close but nothing that I can get working completely. My challenges are I need 4 pieces of information from each row and I want it to be automatic. This Solution was close, but I need something That brings an extra two columns with it.I don't want to have to process the data once it's entered into the Grid.

I created something that works but I feel it's clumsy and there has to be a better way to do it. I have added a Sample Time sheet to my Google drive of what I have created. Essentially I enter the work order, description and pay code on the left. The dates are up top and hours hours worked are entered into the grid. I use a few simple formulas to make a list of every grid cell in the format my finance department needs and then bring it back into the main sheet to sort it. I feel this setup is fragile and it requires manual sort every time info is updated.

Can you help me get from the format on the left to the format on the right with 0 steps. The data arrangement on the left can be modified but the data on the right has to be exactly as show. Mostly I don't know what I'm trying to do is called. It took me longer than I care to admit to find the term "matrix to list." All suggestions are welcome.

Thanks

A Screenshot of the excel sheet for when the google drive link stops working.

Oakum
  • 1
  • 1
    Is Column T:Z desired output? I don't understand how the left comes from the right and some items don't appear to match even in the same named column. Also, logic for ordering is not clear to me.... Where do the different work dates come from and why is pay code 103 after 1, 1,1? The first solution you are showing is an unpivot. – QHarr Nov 10 '17 at 19:38
  • Thank you for identifying the unpivot term. I willl research. Yes, column T:AC is the required output format. Work dates are the 4 digit headings of the columns of the left grid in MMDD format. Ordering is just a sort by date then pay code. A row represents a different type of work for a given project. Paycodes are the type of work. eg pay code 1 is regular time, 110 is overtime. If i perform the same type of work on the same project all week i just have to fill the populate the columns in the row instead of create an new row entry. I used the processing tab to generate the data on the right. – Oakum Nov 10 '17 at 23:52
  • I found a [Video](https://www.youtube.com/watch?v=Kbu5RkUo6-k) that is What I want just without power query and an auto refresh we would be good. Thanks for the push in the right direction – Oakum Nov 11 '17 at 00:40
  • That’s great news. Please post your final solution when you have it as an answer here so others can learn from it. – QHarr Nov 11 '17 at 07:22

0 Answers0