I’ve put forth an honest couple of hours sorting through this message board for help on extracting data and such but without finding a successful, non-vba solution.
I have a list of “job” numbers and for any given job number I have several estimates I might receive. For any given estimate, I also have an approved amount. The approach thus far is to have a single line-item for a given job and put several repeat columns to the right, each group of columns being unique for a particular estimate. This make the spreadsheet rather messy with lots of left-right scrolling to see details.
I would rather take a more flat, data-centric (or “records”) approach and extract out the information I need as I need it, preferably with pivot tables or formulas. Pivot tables work well for me; it’s the simplest way I know of to generate a list of unique job numbers in a different worksheet. The challenge I face is two-fold:
1) How do I force the Pivot table to bring forward ONLY the most recent estimate’s hours, i.e., of the 5 estimates I might have for a job, I only want to see the most recent date and the estimated amount? The raw data has a date and we never get more than one estimate in the same day for the same job.
2) How might I pull forward the most recent approved amount right along side of the item 1 information above? This is tricky because the most recently approved amount may be on an older estimate (it takes time to obtain approvals while estimates continue to mature), i.e., on a different “record’ than the item pulled back with item 1 above.
I’m willing to restructure my data, including adding any “helper” columns to make this task simpler.
I have a list of “job” numbers and for any given job number I have several estimates I might receive. For any given estimate, I also have an approved amount. The approach thus far is to have a single line-item for a given job and put several repeat columns to the right, each group of columns being unique for a particular estimate. This make the spreadsheet rather messy with lots of left-right scrolling to see details.
I would rather take a more flat, data-centric (or “records”) approach and extract out the information I need as I need it, preferably with pivot tables or formulas. Pivot tables work well for me; it’s the simplest way I know of to generate a list of unique job numbers in a different worksheet. The challenge I face is two-fold:
1) How do I force the Pivot table to bring forward ONLY the most recent estimate’s hours, i.e., of the 5 estimates I might have for a job, I only want to see the most recent date and the estimated amount? The raw data has a date and we never get more than one estimate in the same day for the same job.
2) How might I pull forward the most recent approved amount right along side of the item 1 information above? This is tricky because the most recently approved amount may be on an older estimate (it takes time to obtain approvals while estimates continue to mature), i.e., on a different “record’ than the item pulled back with item 1 above.
I’m willing to restructure my data, including adding any “helper” columns to make this task simpler.
| Est File 0.1.xls | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | |||
| 1 | Job# | EstType | EstDate | EstHrs | Appr'dHrs | Notes | |||||||
| 2 | 12345 | Ph1 | 01/31/07 | 2,000.00 | 2,000.00 | ||||||||
| 3 | 12345 | Ph2 | 02/27/07 | 3,000.00 | 2,800.00 | Didn'tapprovetotalestimate | |||||||
| 4 | 12345 | Ph3 | 11/06/07 | 2,500.00 | |||||||||
| 5 | 54321 | Ph1 | 03/15/07 | 550.00 | 550.00 | ||||||||
| 6 | 54321 | Ph1 | 05/25/07 | 850.00 | Scopeincreased;newestimatebutnotnewestimatephase | ||||||||
| 7 | 54321 | Ph2 | 09/15/07 | 1,000.00 | |||||||||
| 8 | |||||||||||||
| 9 | |||||||||||||
| 10 | |||||||||||||
| 11 | ExampleofDesiredOutput-wantonadifferentworksheet | ||||||||||||
| 12 | Job# | CurrEstType | CurrEstDate | CurrEstHrs | Appr'dEstType | Appr'dEstDate | Appr'dHrs | ||||||
| 13 | 12345 | Ph3 | 11/06/07 | 2,500.00 | Ph2 | 02/27/07 | 2,800.00 | ||||||
| 14 | 54321 | Ph2 | 09/15/07 | 1,000.00 | Ph1 | 03/15/07 | 550.00 | ||||||
EDS-Active | |||||||||||||
