Hi
The situation is I have a list of products with their prices. As time progresses prices ge updated via contract variations, and the new rates are added to a new column to the right of the products - this over time gives a history of the individual products.
Note - not all products update at the same time, and they can be plus or minus from the original.
The issue is I need to do some calls based on the price history - i.e. what was the Max price or the Min, Average etc….
The column headers are chosen by the users so they may use something like Variation1, or 5/7/2022 or Summer22 - so basically no regular values.
How then can I find all the columns with pricing (lets say I know the first two columns are and index, and the product), and carry out the necessary calculations?
Im on a train at the moment using my phone so cannot upload a workbook until later, if that would help.
Thanks in advance for any comments
GreenBoy
A Workbook would be very handy. I am guessing that your data should be normalized by unpivoting your table and doing a Group By on the products to find the Min, Max and Average.
Hi Alan
Sorry for the delay in responding - the holiday season seems to make everything busier...
Please see a sample sheet below (cant add files to this forum)
I have played with pivoting and unpivoting... but I am obviously missing something.
Any help gratefully received.
Thanks
GreenBoy
Product ID Product Unit Rate Var 1 RPI Var 2
| 1 | Widget | Nr | 100 | 98 | 102.9 | 105 |
| 2 | Metric Widget | Nr | 80 | 83 | 87.15 | 85 |
| 3 | Boxes | Nr | 25 | 26.25 | 30 | |
| 4 | Meters | m | 17 | 17 | 17.85 | 18 |
Hi Dave,
Copied your example in a empty sheet and played around with PQ a bit. Perhaps that's what you had in mind. All it does is unpivot all columns except for the first three (ID, Product and Unit). Then you can group by ID with three separate aggregations (min, max and average).
By the way, you can upload files here. Befor you press "Submit Reply" press "Attachments" at the far left-hand side of the editor window. Select the file(s) "Add File" and then press "Start Upload".
If I missed the point completely, try uploading a file again and include what you have tried so far and what you would want as the end result.
Riny
Riny - thank you, this is absolutely spot on.
I think merging the table with itself is amazing.
Thank you again.
David
Glad I could help, and indeed, I forgot to mention that merging the table with itself was the third important step in the process.
