•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Modified PIVOTBY or dynamic array formula to lookup across rows and columns
Hi
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
[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
#natural language processing for spreadsheets
#rows.com
#AI formula generation techniques
#big data management in spreadsheets
#conversational data analysis
#Excel compatibility
#real-time data collaboration
#intelligent data visualization
#dynamic array formula
#PIVOTBY
#spill
#lookup
#dynamic formula
#Target
#Actual
#Excel
#source data
#Project