Help Transforming a spreadsheet in to readable data

[chelseariver90]

Hi,

We have hundreds of spreadsheets with budget information as a tab but formatted in a way the estimators use to view it. We want to pull all this information into one table but the way it is laid out and separated across so many spreadsheets it is difficult to do. I've attached the Spreadsheet below, basically, the values just get copied and pasted in to the tab by the estimators every time a job is awarded. If it could be made in to one table recognized by the Job Number from Column C cell 1 with columns for all of the categories and subcategories in column C but also with the costings for the Site & Workshop for each category too.

Hope this makes sense.

Thanks

[TonyA]

I'm having trouble understanding what you're looking for. Can you put together an example based on the sheet you provided showing how the output should look?

[chelseariver90]

Hi Tony,

the output i'd be hoping to get from that dummy sample sheet would look like the attached. I only included down to the Workshop section in row 16 but would like to have that layout for the full sheet down to Transport and deliveries. Hope this explains it a bit better. thanks for the help.

[danilang · Accepted solution]

Hi @chelseariver90 

Here's a workflow that takes the report and outputs all 123 columns in one row

These types of reports are always composed of multiple logical tables all printed on one page.  You have to separate the logical tables and treat these individually.  The heart of the process is the first Multi Row tool that determines the type for each row in your input based on values in key fields.  Once you've broken the report into 3 header type chunks(H1, H2 and H3) and one big data chunk (D), process each chunk separately.  Each of the processes is a variation on the transpose/crosstab method that we so often use to pivot data.  The Column number-Crosstab-Dynamic Rename sequence in the bottom two containers gets around the fact that the Cross Tab tool will often reorder columns alphabetically.  Using a numeric field as the Column Name field in the Cross Tab tool orders the column correctly, but then you need to rename them to get final column names.

Dan 

[danrh]

Here's another approach:

The basic idea is that each cell with a value gets a header consisting of the row label combined with the column label. It's fairly flexible, but the output isn't very easy on the eyes. The more dynamic you try to make something like this, the less you're able to do cleanup on it.

Key assumptions I made:

- Anything with a colon (:) has both the field name and the value in the same cell.

- The phrase "CONTRACT VALUE" will always be in the first column of the "meat" of the data.

- Data will ALWAYS be numbers and decimals, labels will NEVER be just numbers and decimals (other than the first assumption above).

Take a look, see if it gets you there/close.

Dan

[chelseariver90]

Hi Dan,

I've had to change my code as it is going to be pulling from a directory and changing a number of files all with the budget tab. Since it is pulling through a directory it seems to be making the first row in to headers which is causing the determine row tool to give an error. Would you know a way around this? See workflow below of how it will be read from a directory in to your flow. thanks

[danilang]

Hi @chelseariver90 

In your workflow, you reference a Macro "..\Macros\Macros\Budget sheets.yxmc"  which is where the files are being read.  In there, there is probably a Dynamic Input tool that's reading the actual files.  In the its configuration make sure that the First Row Contains Data is checked like this

 

This will stop the first row being used as field names.  

Since you've got multiple files coming in as well, you'll have to use the file name to group the various sections.  For instance, in the Determine Row Type multirow tool, group by the file name.  

You'll have to group by FileName in the Crosstab tools as well.  In the Transpose tools, add the FileName as a Key field like this

With out these changes you'll end up with the fields from all the files in a single row.

Dan

[chelseariver90]

Thanks Dan,

I tried doing the above but when changed the first row to have data and selected to group by file name in the Mulitirow formula none of the data got passed through and my transpose tools were empty. I've attached both workflows below if you could have a look, thanks so much for the time and effort really appreciate it.

Thanks

[danilang]

Hi @chelseariver90 

Here's the fixed copy.

In the macro you checked the First Row Contains Data in the Input tool the reads the sheet names, but you also had to make it in Dynamic Input (Tool 4) ,since this is the one that reads the data.

There is one change that I forgot to tell you about.  Any RecordID tool needs to be replaced by a MulitRow Formula grouped by file name.  This is because the RecordID doesn't support grouping.

I added a new file to the directory just to demonstrate and made the other changes that I mentioned in the previous post

Dan 

[chelseariver90]

Thanks Dan,

sorry but in the project Costs you have uploaded you havent included the Macro or the mulitrow tool inplace of the ID record tools. I found out how to set the first row to data in the dynamic input tool though thanks for that! You may have uploaded the wrong package since you have been helping me out with it so much 😕 

Cheers

J