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

submitted by /u/land_cruizer
[link] [comments]

Want to read more?