0

I have a sheet that collects data from weekly time sheets workbooks. There is a single cell in each workbook that pulls all the data I need transferred into one cell. The data is from a range of 29 columns by 29 rows but this could grow eventually. Each row represents data from one work order (WO) including the WO#, the labour cost ($) and a list of employees who worked on the job (E#).

This cell is then opened and extracted into a single row of data. 29 columns for each of 29 rows in the original data. This means 29 x 29 columns. 

WO1 $10 E1  E2  E3  E4  WO8    $10  E1  E2  E3  E4
WO2 $15 E1  E2          WO3    $15  E1  E2      
WO3 $20 E1  E2  E3      WO9    $20  E1  E2  E3  
WO4 $25 E1  E2  E3  E4  WO5    $25  E1  E2  E3  E4
WO5 $30 E1  E2          WO10   $30  E1  E2      
WO6 $35 E1  E2  E3  E4  WO11   $35  E1  E2  E3  E4
WO7 $40 E1  E2  E3      WO1    $40  E1  E2  E3

I would like to create a list that combines all the data for each WO into a single list 29 columns wide by 841 rows. It is possible that some jobs span more than 1 week and can have data in multiple rows so there may be duplicate WO's at this point.

WO1    $10  E1  E2  E3  E4
WO2    $15  E1  E2      
WO3    $20  E1  E2  E3  
WO4    $25  E1  E2  E3  E4
WO5    $30  E1  E2      
WO6    $35  E1  E2  E3  E4
WO7    $40  E1  E2  E3  
WO8    $10  E1  E2  E3  E4
WO3    $15  E1  E2      
WO9    $20  E1  E2  E3  
WO5    $25  E1  E2  E3  E4
WO10   $30  E1  E2      
WO11   $35  E1  E2  E3  E4
WO1    $40  E1  E2  E3

I was thinking that if I could get a list with every WO and associated data into a single list, I could create a unique list of WO's and other excel magic to refine the list to achieve what I need. This part I think I can handle. The challenge for me is putting it all into a list single list first.

I tried this code: Excel - Combine multiple columns into one column

=IF(ROW()<=COUNTA(A:A),INDEX(A:A,ROW()),IF(ROW()<=COUNTA(A:B),INDEX(B:B,ROW()-COUNTA(A:A)),IF(ROW()>COUNTA(A:C),"",INDEX(C:C,ROW()-COUNTA(A:B)))))

But this would require 29 nested if statements and would have to have all the data regrouped so that all the corresponding cells are adjacent to each other.

I would prefer to use formulas but will use VBA if that is the best way.

If interested, here is a sample version of the file: https://www.dropbox.com/s/58iex17wx3wuvug/stackoverflowsample.xlsx?dl=0

The first 30 columns are my effort to start working with the above formula.

I can likely lessen the number of original columns because the likelihood of more than 10-15 employees ever being on the same job is slim if not impossible but that just happened to be how many columns I originally allowed. This can be changed if beneficial. There could be more rows for WO's though.

I could extract differently too if that makes a difference.

Mesut Akcan
  • 899
  • 7
  • 19
DryBSMT
  • 51
  • 2
  • 12
  • Do you think the number of rows will change frequently? Or is it likely that they will change only occasionally? I've got a solution, but it will not work if the rows change frequently. (Also it still requires some manual steps, but should greatly reduce the manual effort. If you are wanting to convert >30 documents you'll want to use some sort of VBA method.) – Emily Alden Jan 02 '19 at 16:22
  • I don't expect the rows to change too often. My goal is to have this as automated and easy as possible so a person with very little skill can manage it with a little instruction. I am also looking at power query as an option too but still learning about it. I would welcome your suggestion @Emily Alden. – DryBSMT Jan 02 '19 at 22:42

1 Answers1

0

If you are doing this only a few times per day then do the following:

In a different tab put =Sheet1!A1 drag the formula over however many employees and drag it down for 29 rows. Then put =Sheet1!B1 drag over and drag down 29 rows etc.

This will take a long time to set up initially, but only has to be set up once. To add lines you would either insert a row or add them to the bottom of the column (depending on whether order is important).

Then you can copy paste-values that column of data wherever you would like it and remove duplicates.

This is intuitively simple for most Excel users to understand so it is ideal for handing off to other people.

If you need to do this many times per day then VBA would probably be a good solution. I can give you a code 'sketch' to get you started, but you would need to attempt to write it on your own first.

Emily Alden
  • 570
  • 3
  • 17