How to automatically fill in dates in between already existing dates?

[DevAlex]

I have hundreds of products that I am trying to track the monthly price for. I currently have all the prices of these products but only for specific dates. What I need to do is fill in all the dates in between the existing dates by month up until the present (Dec. 2022).

For example, I have the following products and pricing:

Product 1	2/15/2022​	$1​
Product 1	6/30/2022​	$2​
Product 2	5/5/2022​	$5​
Product 2	7/27/2022​	$5.50​
Product 3	6/11/2022​	$4​

I want to fill in the in-between dates of these products monthly with the first date of the month up until the present date (December 1st, 2022). When the price changes for the product, I want the monthly pattern to still be followed. It should look like this:

Product 1	2/15/2022​	$1​
Product 1	3/1/2022​	$1​
Product 1	4/1/2022​	$1​
Product 1	5/1/2022​	$1​
Product 1	6/1/2022​	$1​
Product 1	6/30/2022​	$2​
Product 1	7/1/2022​	$2​
Product 1	8/1/2022​	$2​
Product 1	9/1/2022​	$2​
Product 1	10/1/2022​	$2​
Product 1	11/1/2022​	$2​
Product 1	12/1/2022​	$2​
Product 2	5/5/2022​	$5​
Product 2	6/1/2022​	$5​
Product 2	7/1/2022​	$5​
Product 2	7/27/2022​	$5.50​
Product 2	8/1/2022​	$5.50​
Product 2	9/1/2022​	$5.50​
Product 2	10/1/2022​	$5.50​
Product 2	11/1/2022​	$5.50​
Product 2	12/1/2022​	$5.50​
Product 3	6/11/2022​	$4​
Product 3	7/1/2022​	$4​
Product 3	8/1/2022​	$4​
Product 3	9/1/2022​	$4​
Product 3	10/1/2022​	$4​
Product 3	11/1/2022​	$4​
Product 3	12/1/2022​	$4​

Any help is appreciated!

	​	​
	​	​

 

[Micron]

You should include your version in your signature, or at least divulge in your post. I was considering using worksheetfunction.Unique to create an array of unique product values but during my struggle to adapt I found a post that says it's only useful in version 365. You'll help others who are smarter than me at this if they know your version. Regardless, I'm stumbling around in the dark with it and don't seem to be getting anywhere.

 

[Micron]

Re you Excel version - I now see your info says Office 2021 so I guess you have the latest in updates. Anyway, I figured out the function issue and continue to play.
Is your product data really sorted that way? That would be nice...

Also, might you have
Product1
Product1
Product1
?
You only show 2 of each.

 

[fjns]

Hi, see the linked file (with an auxiliary column) for a possible solution...

The formulas used in the table:
A3: =IF(A2="","",IF(INDEX(Sheet1!A:A,A2+1)="",IF(AND(B2<>"",C2<A$1),A2,""),IF(INDEX(Sheet1!A:A,A2+1)=B2,IF(AND(C2<A$1,EOMONTH(C2,0)+1<INDEX(Sheet1!B:B,A2+1)),A2,A2+1),IF(C2<A$1,A2,A2+1))))
B3: =IF(A3="","",IF(INDEX(Sheet1!A:A,A2+1)="",IF(AND(B2<>"",C2<A$1),B2,""),IF(INDEX(Sheet1!A:A,A2+1)=B2,B2,IF(C2<A$1,B2,INDEX(Sheet1!A:A,A2+1)))))
C3: =IF(A3="","",IF(INDEX(Sheet1!A:A,A2+1)="",IF(AND(B2<>"",C2<A$1),EOMONTH(C2,0)+1,""),IF(INDEX(Sheet1!A:A,A2+1)=B2,IF(AND(C2<A$1,EOMONTH(C2,0)+1<INDEX(Sheet1!B:B,A2+1)),EOMONTH(C2,0)+1,INDEX(Sheet1!B:B,A2+1)),IF(C2<A$1,EOMONTH(C2,0)+1,INDEX(Sheet1!B:B,A2+1)))))
D3: =IF(A3="","",IF(INDEX(Sheet1!A:A,A2+1)="",IF(AND(B2<>"",C2<A$1),D2,""),IF(INDEX(Sheet1!A:A,A2+1)=B2,IF(AND(C2<A$1,EOMONTH(C2,0)+1<INDEX(Sheet1!B:B,A2+1)),D2,INDEX(Sheet1!C:C,A2+1)),IF(C2<A$1,D2,INDEX(Sheet1!C:C,A2+1)))))

ProductsPrices.xlsx

 

[DevAlex]

Re you Excel version - I now see your info says Office 2021 so I guess you have the latest in updates. Anyway, I figured out the function issue and continue to play.
Is your product data really sorted that way? That would be nice...

Also, might you have
Product1
Product1
Product1
?
You only show 2 of each.

Some products have only the original price, some have 2 changes, some of many. Yes it is sorted as that original example.

 

[DevAlex]

Hi, see the linked file (with an auxiliary column) for a possible solution...

The formulas used in the table:
A3: =IF(A2="","",IF(INDEX(Sheet1!A:A,A2+1)="",IF(AND(B2<>"",C2<A$1),A2,""),IF(INDEX(Sheet1!A:A,A2+1)=B2,IF(AND(C2<A$1,EOMONTH(C2,0)+1<INDEX(Sheet1!B:B,A2+1)),A2,A2+1),IF(C2<A$1,A2,A2+1))))
B3: =IF(A3="","",IF(INDEX(Sheet1!A:A,A2+1)="",IF(AND(B2<>"",C2<A$1),B2,""),IF(INDEX(Sheet1!A:A,A2+1)=B2,B2,IF(C2<A$1,B2,INDEX(Sheet1!A:A,A2+1)))))
C3: =IF(A3="","",IF(INDEX(Sheet1!A:A,A2+1)="",IF(AND(B2<>"",C2<A$1),EOMONTH(C2,0)+1,""),IF(INDEX(Sheet1!A:A,A2+1)=B2,IF(AND(C2<A$1,EOMONTH(C2,0)+1<INDEX(Sheet1!B:B,A2+1)),EOMONTH(C2,0)+1,INDEX(Sheet1!B:B,A2+1)),IF(C2<A$1,EOMONTH(C2,0)+1,INDEX(Sheet1!B:B,A2+1)))))
D3: =IF(A3="","",IF(INDEX(Sheet1!A:A,A2+1)="",IF(AND(B2<>"",C2<A$1),D2,""),IF(INDEX(Sheet1!A:A,A2+1)=B2,IF(AND(C2<A$1,EOMONTH(C2,0)+1<INDEX(Sheet1!B:B,A2+1)),D2,INDEX(Sheet1!C:C,A2+1)),IF(C2<A$1,D2,INDEX(Sheet1!C:C,A2+1)))))

ProductsPrices.xlsx

I have a couple thousand of rows of products, do I need repeatedly copy this formula after each product price change? Maybe I'm interpreting this wrong?

 

[fjns]

You need copy these formulas once (Autofill down).

 

[DevAlex]

You need copy these formulas once (Autofill down).

Thank you, this worked exactly as needed.

 

[DevAlex]

Hi, see the linked file (with an auxiliary column) for a possible solution...

The formulas used in the table:
A3: =IF(A2="","",IF(INDEX(Sheet1!A:A,A2+1)="",IF(AND(B2<>"",C2<A$1),A2,""),IF(INDEX(Sheet1!A:A,A2+1)=B2,IF(AND(C2<A$1,EOMONTH(C2,0)+1<INDEX(Sheet1!B:B,A2+1)),A2,A2+1),IF(C2<A$1,A2,A2+1))))
B3: =IF(A3="","",IF(INDEX(Sheet1!A:A,A2+1)="",IF(AND(B2<>"",C2<A$1),B2,""),IF(INDEX(Sheet1!A:A,A2+1)=B2,B2,IF(C2<A$1,B2,INDEX(Sheet1!A:A,A2+1)))))
C3: =IF(A3="","",IF(INDEX(Sheet1!A:A,A2+1)="",IF(AND(B2<>"",C2<A$1),EOMONTH(C2,0)+1,""),IF(INDEX(Sheet1!A:A,A2+1)=B2,IF(AND(C2<A$1,EOMONTH(C2,0)+1<INDEX(Sheet1!B:B,A2+1)),EOMONTH(C2,0)+1,INDEX(Sheet1!B:B,A2+1)),IF(C2<A$1,EOMONTH(C2,0)+1,INDEX(Sheet1!B:B,A2+1)))))
D3: =IF(A3="","",IF(INDEX(Sheet1!A:A,A2+1)="",IF(AND(B2<>"",C2<A$1),D2,""),IF(INDEX(Sheet1!A:A,A2+1)=B2,IF(AND(C2<A$1,EOMONTH(C2,0)+1<INDEX(Sheet1!B:B,A2+1)),D2,INDEX(Sheet1!C:C,A2+1)),IF(C2<A$1,D2,INDEX(Sheet1!C:C,A2+1)))))

ProductsPrices.xlsx

If I wanted to do daily instead of monthly, what types of changes would I need to make to these formulas?

 

[fjns]

Hi, see the linked file for the new table...

The formulas used in the new table:
A3: =IF(A2="","",IF(INDEX(Sheet1!A:A,A2+1)="",IF(AND(B2<>"",C2<A$1),A2,""),IF(INDEX(Sheet1!A:A,A2+1)=B2,IF(AND(C2<A$1,C2+1<INDEX(Sheet1!B:B,A2+1)),A2,A2+1),IF(C2<A$1,A2,A2+1))))
B3: =IF(A3="","",IF(INDEX(Sheet1!A:A,A2+1)="",IF(AND(B2<>"",C2<A$1),B2,""),IF(INDEX(Sheet1!A:A,A2+1)=B2,B2,IF(C2<A$1,B2,INDEX(Sheet1!A:A,A2+1)))))
C3: =IF(A3="","",IF(INDEX(Sheet1!A:A,A2+1)="",IF(AND(B2<>"",C2<A$1),C2+1,""),IF(INDEX(Sheet1!A:A,A2+1)=B2,IF(AND(C2<A$1,C2+1<INDEX(Sheet1!B:B,A2+1)),C2+1,INDEX(Sheet1!B:B,A2+1)),IF(C2<A$1,C2+1,INDEX(Sheet1!B:B,A2+1)))))
D3: =IF(A3="","",IF(INDEX(Sheet1!A:A,A2+1)="",IF(AND(B2<>"",C2<A$1),D2,""),IF(INDEX(Sheet1!A:A,A2+1)=B2,IF(AND(C2<A$1,C2+1<INDEX(Sheet1!B:B,A2+1)),D2,INDEX(Sheet1!C:C,A2+1)),IF(C2<A$1,D2,INDEX(Sheet1!C:C,A2+1)))))

ProductsPricesDaily.xlsx