Forum Discussion
How to determine if a range exists?
Hello,
Thanks in advance for helping me out.
Problem: Products that have multiple prices
I have a spreadsheet that contains product numbers, the variants under this which each have a unique number assigned to them (e.g. colour and size combinations), prices, as well as a lot of other detailed information.
I would like to be able to have a formula that searches the prices of all the variants under a product number and lets me know if there is a range of prices found.
If you want to get even more technical, it would be cool to include another column (called next price) if possible. Or to only search within a certain product-colour (i.e. search within Jacket-Red (but ignore Jacket-Black).
I have uploaded a sample data sheet. Any ideas?
Josie, you didn't show the desired result in your sample workbook.
So, I did a guess and made a pivot table. Product # in row area and Price twice in values area. Change the first to Min and the second to Max. And there is your range of prices for each product.
Product #Min. von PriceMax. von Price
1001 100 120 1002 80 80 1003 50 52 1004 25 25 1005 95 105 Thanks for the reply!
I was able to replicate your Pivot Table Results and then added a column to calculate the difference between the ranges. This means I can easily filter out products that do not have any price ranges.
Do you have any ideas about how to incorporate the Next Price column too?I thought about some Conditional Formatting rules + filtering that could work but the issue is that I am dealing with tens of thousands of products so I am hoping for something as automatic as possible.
You still have not provided your desired result in the workbook and how "Next Price" should be incorporated.
