•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Dynamic Spill Formula for Lookup To Array With Fixed Week Columns
Our take
Are legacy spreadsheets slowing you down? Transforming your data into a dynamic, accessible format shouldn't be a chore. With the right approach, you can efficiently restructure your GROUPBY results into a clean layout with fixed week columns. This guide explores a powerful Dynamic Spill Formula that simplifies your process, enhances performance, and ensures clarity. Dive in to discover how to streamline your data presentation, making it not only faster but also more insightful and user-friendly. Your journey to a more efficient spreadsheet starts here!
Hi, I have a source table which is a spill from a GROUPBY result in the following format:
| Item | Area | Week | Measure 1 | Measure 2 | Measure 3 |
|---|---|---|---|---|---|
| PC | Area 1 | Week 1 | 50 | 100 | 150 |
| PC | Area 1 | Week 2 | 100 | 200 | 250 |
| PC | Area 1 | Week 3 | 150 | 250 | 300 |
| PC | Area 2 | Week 2 | 20 | 40 | 60 |
| PC | Area 2 | Week 3 | 80 | 100 | 120 |
I need to present this info in another sheet with weeks in fixed column headers . The desired output is as follows:
| Item | Area | Measure | Week 1 | Week 2 | Week 3 |
|---|---|---|---|---|---|
| PC | Area 1 | Measure 1 | 50 | 100 | 150 |
| PC | Area 1 | Measure 2 | 100 | 200 | 250 |
| PC | Area 1 | Measure 3 | 150 | 250 | 300 |
| PC | Area 2 | Measure 1 | - | 20 | 80 |
| PC | Area 2 | Measure 2 | - | 40 | 100 |
| PC | Area 2 | Measure 3 | - | 60 | 120 |
For each item-area block, all the measures will be repeated for all weeks. Tried to do a PIVTOBY but hit a block with fixed columns as some weeks might be missing in the data.
Currently using a cell-by cell FILTER formula which is very slow, hoping to improve the performance with a single DA formula
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Modified PIVOTBY or dynamic array formula to lookup across rows and columnsHi I have the following source data: Week Project Area Target Actual 4-Apr-25 Proj 01 North 50 100 11-Apr-25 Proj 01 North 150 120 18-Apr-25 Proj 01 North 300 50 2-May-25 Proj 01 North 500 70 4-Apr-25 Proj 02 South 10 200 I need a single dynamic formula to spill across rows and columns to give this result : Project Area Measure 4-Apr-25 11-Apr-25 18-Apr-25 2-May-25 Proj 01 North Target 50 100 300 500 Proj 01 North Actual 100 120 50 70 Proj 02 South Target 10 - - - Proj 02 South Actual 200 - - - I tried a PIVOTBY solution but couldn't quite achieve this result, or another option is to go for fixed values for the first three columns & dates and a spilling formula for the values submitted by /u/land_cruizer [link] [comments]
- Dynamic function to broadcast sum based on filtered criteriaHi I have the following source table: Product Region Month Target Actual Comp N Mar-26 100 50 Comp N Jan-26 60 120 PC S Feb-26 100 20 PC S Apr-26 10 200 Mobile W May-26 20 100 I have another table used for presentation with fixed rows and columns arranged this way : Region Col Jan-26 Feb-26 Mar-26 Apr-26 May-26 N Target S Actual I need a dynamic formula to spill across this grid. The rows and columns can increase so the solution should be scalable. Also there is another range with required list of products. for e.g range A1:A2 with items "Comp" and "PC", so the formula should be able to filter the sum based on items in this list. So the expected result is : Region Col Jan-26 Feb-26 Mar-26 Apr-26 May-26 N Target 60 0 100 0 0 S Actual 0 20 0 200 0 submitted by /u/land_cruizer [link] [comments]