Fill out a matrix with a user defined value which has variable start and end points

{ 0 Comments }

Here is a sample dataset with Item and Number of buckets in the row labels and Year-Month in the column labels.  In the value area section are some numbers.

Item Number of Buckets 2016-10 2016-11 2016-12 2017-1 2017-2 2017-3 2017-4 2017-5
ABC 8 1500
PQR 12 40
RPS 4 100
CHA 11 30
MUM 12 90
CHE 2 24

The objective is to repeat the number in every row of the value area section as many times as the number mentioned in the Number of buckets column.  As a case in point, for RPS, 100 should appear 3 more times in that row (till 2017-7).  The result should look like this

Item Number of Buckets 2016-10 2016-11 2016-12 2017-1 2017-2 2017-3 2017-4 2017-5
ABC 8 1500
PQR 12 40 40 40 40 40
RPS 4 100 100
CHA 11 30 30 30 30
MUM 12 90 90 90 90 90 90 90 90
CHE 2 24

For want of space I have deleted the columns from the right.

I have solved this problem using Power Query a.k.a. Get & Transform in Excel 2016 (available under Data).  You may download my workbook from here.

Leave a Comment

Your email address will not be published.

*