Help Transforming a spreadsheet in to readable data

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

Dummy Budget.xlsx

Transformation

Accepted answers

danilang

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

ProjectCosts.yxzp

All comments

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.

Hopeful Layout.xlsx

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

solution.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703