•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
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]
Want to read more?
Check out the full article on the original site
Tagged with
#Excel alternatives for data analysis
#generative AI for data analysis
#financial modeling with spreadsheets
#natural language processing for spreadsheets
#AI formula generation techniques
#rows.com
#big data management in spreadsheets
#conversational data analysis
#Excel compatibility
#real-time data collaboration
#intelligent data visualization
#Dynamic Spill Formula
#Lookup
#GROUPBY
#Fixed Week Columns
#Array
#Measure
#Source Table
#PIVTOBY
#Weeks