My issue is complex (to me at least, and I've spent hours trying to figure it out), but I'll try to keep the initial explanation short. I'm guessing this has been 'solved' somewhere, but I'm just not using the right search phrases.
Simple version:
I have two spreadsheets. One from a system (SYSFILE), one my team creates and updates (TEAMFILE). The task is to compare the two and make updates until they match (i.e., a repetitive process).
The issue is that they are both "poorly formed," flat file spreadsheets, with several fields that are duplicates, with columns that are sometimes named differently.
Please see the attached with as simple an example as I could make, but here's the jist of it:
Both files have project related information, and I'll focus on cost.
I tried to put a simple text view in here, but it's a mess, so here's a way smaller set
SYSFILE:
Proj# ResType Role Jan Feb YTD FY
123 FTE App 1000 500 1000 1500
456 Consult QC 500 250 500 750
USERFILE:
Proj# ResType Role Jan Feb YTD FY
123 FTE AD1 1000 500 1000 1500
456 Consult Qual 400 850 400 1250
In each workbook I would create a pivot with (again, simple):
Proj# ResType Role YTD FY
123 FTE App 1000 1500
456 Consult QC 500 750
Then I will cut and paste that data, adding a column for the "Source":
Source Proj# ResType Role YTD FY
SYS 123 FTE App 1000 1500
SYS 456 Consult QC 500 750
USER 123 FTE AD1 1000 1500
USER 456 Consult Qual 400 1250
Then I will create a pivot from that table, and use a "Difference From" column to see what the variances are for YTD and FY numbers. Then go back, adjust the user file, re-update it and the pivots, and copy and paste the updated pivot data to the "merged" table, and refresh the final pivot, hoping to get all "Difference From" cells = $0.
So, I'm not sure what this is called, but I've looked in Rob Collie's book, Bill Jelen's book, Chandoo's fabulous web site, and other places and can't find out how to do "this" in PowerPivot, which I'm pretty sure is the ultimate solution.
Issues are:
* I can't directly relate the two base worksheets to each other, because they do not have a unique field on each row. (I.e., I'd love to "just" relate them by project code, but each table has many rows for each project. Same with Res Type or Role - many rows with the same values.
* I created a "lookup" table of all the project codes, and related the two that way, and that was a good start. But I'm hoping I don't have to create/maintain a "lookup" table for every column I'd like to compare in the two files.
OK, I think that's the simplified version. Apologies if I've not answered some obvious questions, but please do ask.
And hopefully the attached helps explain with a little more data some of the issues I'm trying to solve for.
Thanks for your time,
Jim
Simple version:
I have two spreadsheets. One from a system (SYSFILE), one my team creates and updates (TEAMFILE). The task is to compare the two and make updates until they match (i.e., a repetitive process).
The issue is that they are both "poorly formed," flat file spreadsheets, with several fields that are duplicates, with columns that are sometimes named differently.
Please see the attached with as simple an example as I could make, but here's the jist of it:
Both files have project related information, and I'll focus on cost.
I tried to put a simple text view in here, but it's a mess, so here's a way smaller set
SYSFILE:
Proj# ResType Role Jan Feb YTD FY
123 FTE App 1000 500 1000 1500
456 Consult QC 500 250 500 750
USERFILE:
Proj# ResType Role Jan Feb YTD FY
123 FTE AD1 1000 500 1000 1500
456 Consult Qual 400 850 400 1250
In each workbook I would create a pivot with (again, simple):
Proj# ResType Role YTD FY
123 FTE App 1000 1500
456 Consult QC 500 750
Then I will cut and paste that data, adding a column for the "Source":
Source Proj# ResType Role YTD FY
SYS 123 FTE App 1000 1500
SYS 456 Consult QC 500 750
USER 123 FTE AD1 1000 1500
USER 456 Consult Qual 400 1250
Then I will create a pivot from that table, and use a "Difference From" column to see what the variances are for YTD and FY numbers. Then go back, adjust the user file, re-update it and the pivots, and copy and paste the updated pivot data to the "merged" table, and refresh the final pivot, hoping to get all "Difference From" cells = $0.
So, I'm not sure what this is called, but I've looked in Rob Collie's book, Bill Jelen's book, Chandoo's fabulous web site, and other places and can't find out how to do "this" in PowerPivot, which I'm pretty sure is the ultimate solution.
Issues are:
* I can't directly relate the two base worksheets to each other, because they do not have a unique field on each row. (I.e., I'd love to "just" relate them by project code, but each table has many rows for each project. Same with Res Type or Role - many rows with the same values.
* I created a "lookup" table of all the project codes, and related the two that way, and that was a good start. But I'm hoping I don't have to create/maintain a "lookup" table for every column I'd like to compare in the two files.
OK, I think that's the simplified version. Apologies if I've not answered some obvious questions, but please do ask.
And hopefully the attached helps explain with a little more data some of the issues I'm trying to solve for.
Thanks for your time,
Jim