I have a inventory management excel model where my mastersheet has a column that records purchase price. Each row contains information of each inventory item. The purchase price column gets updated every week as invoices are received and prices are updated. I already have vba code that automatically timestamps the adjacent cell (price in cell c2 changes automatically updated date stamp in cell d2). I would like to have a separate worksheet that automatically records the history of price changes for analysis.
Ideally what I see in my head is each row would still be the inventory item but each column would be a date which an inventory item has changed that automatically expands each time with a date stamp header and a new price as the cell value in that column for the corresponding inventory item that price changed.
This means that if a inventory items price changes then the cell in the new column has the new price but if it does not the old price is recorded again for that date. See table picture inserted below for example. If there is any other data structure that would also work with a pivot table that is easier to do with vba then that is great as well.