Hi all - having difficulty with the following. I've read all the related posts I can find and tried many different formulas, but can't figure this out.
We have Sheet 1 that consists of unique rows of data with columns that are as follows:
ID, Original Price, Date, Price, Date, Price .... Price
These dates are the dates between 10/16 and 12/2 on which a specific object's price changed. The dates also have timestamps because they were originally in epoch time.
Example Data On Sheet 1
<tbody>
</tbody>
I need to get this data onto another sheet (Sheet 2), which is organized by the actual date name, with rows of prices for that date below it. I'm going to use that sheet to run analysis on average prices by day, etc. So the column titled 10/16 has all the prices of all the objects on that date, and so on from there. The problem is that individual objects only have data on dates where their price changed (and no data on any other dates), and in some cases, objects have different price values for the same date, because its price was changed more than once in a day. I've outlined correct answers for Object X in the Chart below.
Where objects didn't have a price specifically called out in Sheet 1, I need to show the previous date's price. Then if there are repeats (i.e. two different price values on the same day), I calculate the average. So from the data above, on 10/20 the price changed twice, so the price for that day was $41. Then on 10/21, it needs to reference the last time it was changed, in this case $40 and use that value until it runs into another price change.
Example Data On Sheet 2
<tbody>
</tbody>
We've tried the following formula and gotten pretty close:
Sheet1 = Original Data Set w/ repeats and blanks
Sheet2 = New sheet where data will be organized by date
=IF((SUMIF(Sheet1$A2:$BY2,">="&Sheet2AP$1,Sheet1$B2:$BZ2)-SUMIF(Sheet1$A2:$BY2,">="&Sheet2AQ$1,Sheet1$B2:$BZ2))=0,(IF(Sheet1$E2:$BY2<sheet2aq$1,offset(sheet1$e2,0,match(sheet2ap$1,sheet1$e2:$cq2,1)-1),sheet2$c2)),(averageifs(sheet1$b2:$bz2,sheet1$a2:$by2,">="&Sheet2AP$1,Sheet1$A2:$BY2,"<"&Sheet2AQ$1)))
But the match occasionally breaks down, it shows a value higher than we'd expect to see. I think that's because our array is technically not exactly in ascending order....but it can't be sorted.
Essentially, we need a way, to bring this data over to sheet 2, with the formula calculating averages for any days with more than one value, and filling in the previous day's values if there is no price change.
We can't clean up sheet 1 at all as there are 70K+ objects with all different assorted dates.
Let me know how we can do this! Thanks!</sheet2aq$1,offset(sheet1$e2,0,match(sheet2ap$1,sheet1$e2:$cq2,1)-1),sheet2$c2)),(averageifs(sheet1$b2:$bz2,sheet1$a2:$by2,">
We have Sheet 1 that consists of unique rows of data with columns that are as follows:
ID, Original Price, Date, Price, Date, Price .... Price
These dates are the dates between 10/16 and 12/2 on which a specific object's price changed. The dates also have timestamps because they were originally in epoch time.
Example Data On Sheet 1
| ID | Original Price | Date | Price | Date | Price | Date | Price | Date | Price |
| X | $40 | 10/16 4:00am | $38 | 10/17 3:00pm | $30 | 10/20 6:00pm | $42 | 10/20 9:00pm | $40 |
| Y | $50 | 10/18 5:00 am | $40 | 10/20 6:00 am | $55 |
<tbody>
</tbody>
I need to get this data onto another sheet (Sheet 2), which is organized by the actual date name, with rows of prices for that date below it. I'm going to use that sheet to run analysis on average prices by day, etc. So the column titled 10/16 has all the prices of all the objects on that date, and so on from there. The problem is that individual objects only have data on dates where their price changed (and no data on any other dates), and in some cases, objects have different price values for the same date, because its price was changed more than once in a day. I've outlined correct answers for Object X in the Chart below.
Where objects didn't have a price specifically called out in Sheet 1, I need to show the previous date's price. Then if there are repeats (i.e. two different price values on the same day), I calculate the average. So from the data above, on 10/20 the price changed twice, so the price for that day was $41. Then on 10/21, it needs to reference the last time it was changed, in this case $40 and use that value until it runs into another price change.
Example Data On Sheet 2
| ID | 10/16 | 10/17 | 10/18 | 10/19 | 10/20 | 10/21 | 10/22 |
| X | $38 | $30 | $30 | $30 | $41 | $40 | $40 |
<tbody>
</tbody>
We've tried the following formula and gotten pretty close:
Sheet1 = Original Data Set w/ repeats and blanks
Sheet2 = New sheet where data will be organized by date
=IF((SUMIF(Sheet1$A2:$BY2,">="&Sheet2AP$1,Sheet1$B2:$BZ2)-SUMIF(Sheet1$A2:$BY2,">="&Sheet2AQ$1,Sheet1$B2:$BZ2))=0,(IF(Sheet1$E2:$BY2<sheet2aq$1,offset(sheet1$e2,0,match(sheet2ap$1,sheet1$e2:$cq2,1)-1),sheet2$c2)),(averageifs(sheet1$b2:$bz2,sheet1$a2:$by2,">="&Sheet2AP$1,Sheet1$A2:$BY2,"<"&Sheet2AQ$1)))
But the match occasionally breaks down, it shows a value higher than we'd expect to see. I think that's because our array is technically not exactly in ascending order....but it can't be sorted.
Essentially, we need a way, to bring this data over to sheet 2, with the formula calculating averages for any days with more than one value, and filling in the previous day's values if there is no price change.
We can't clean up sheet 1 at all as there are 70K+ objects with all different assorted dates.
Let me know how we can do this! Thanks!</sheet2aq$1,offset(sheet1$e2,0,match(sheet2ap$1,sheet1$e2:$cq2,1)-1),sheet2$c2)),(averageifs(sheet1$b2:$bz2,sheet1$a2:$by2,">
