Ytukyg

I get a file with the numbers for a variety of different products and how many have been returned each month.

Raw Data

To make the data neater, I want to split this evenly by day. I have written some formulas to do this in Excel, but it now leaves me with lots of columns for each month.

Transformed Data

Instead of the same number of rows with multiple columns, I want a row for each date and product combination with one column for products returned and one column for the value of the return. I have turned my progress into a Google Sheet here

Final Data

Can anyone help me fill in this final sheet? It will need the date and product repeated the number of times of the number of days in the month, and then the right values put in the next two columns.

This solution uses array formulas. It may not be the best solution, but it works (albeit very slowly). I first created a summary table (you could workaround this step if you wanted to, but I think it makes the solution cleaner):

Columns A:D are your initial product table. Column F is a list of every date, this could be created manually or with a formula. Column G is =COUNTIF(A:A,F2)

Moving onto the new sheet:

Column A is a list of dates repeated X times for each product in that month.
I started by manually writing "6/1/2018" a few times then switched to this formula, which looks for the count of products for a given month and checks to see if the date has been repeated that many times already. If it has, it adds one additional day. Otherwise, it keeps repeating:

=IF(COUNTIF(A$2:A6,A6)=INDEX(Sheet3!G:G,MATCH(DATE(YEAR(A6),MONTH(A6),1),Sheet3!F:F,0)),A6+1,A6)

Column B looks up the product by seeing how many times a given date has been repeated thus far:

=INDIRECT("Sheet3!B"&MATCH(DATE(YEAR(A2),MONTH(A2),1),Sheet3!A:A,0)+COUNTIF(A$2:A2,A2)-1)

Column C is an array formula (commit it with Ctrl + Shift + Enter), which gets the total products and divides by number of days in each month:

=INDEX(Sheet3!C:C,MATCH(1,INDEX((B2=Sheet3!B:B)*(DATE(YEAR(A2),MONTH(A2),1)=Sheet3!A:A),0,1),0))/DAY(EOMONTH(A2,0))

Column D is the same as C but gets the total product value instead:

=INDEX(Sheet3!C:C,MATCH(1,INDEX((B2=Sheet3!B:B)*(DATE(YEAR(A2),MONTH(A2),1)=Sheet3!A:A),0,1),0))/DAY(EOMONTH(A2,0))

I realize that these may be hard to follow. I'd be happy to send you the workbook if you give me your email address. Also, someone else may have an easier to implement solution.

Thanks!