This is my first post here. Hoping to get ideas that could solve my problem.
Here’s my setup:
I have a table where I upload CSV files to. Each row contains, among other information, a price and an attribution to both an item and a vendor. I decided to list the prices in rows and not in columns (as often seen in the forum).
Existing records are merged by a unique value consisting of the vendor’s name and the item’s name.
As you can imagine, there can be different prices for one item from different vendors. One item can “occupy” up to 5 rows. So although I only have 10’000 items, the limits of my base in PRO can easily be reached by this setup (by the way, if you have an idea how to set the whole thing up more effectively, please let me know .
To save space, I thought about only keeping the two lowest prices for each item. As for the more expensive prices, I want to create a view in which I can easily bulk-delete those rows.
I already have created a view that groups by items and sorts by prices (let’s call this view “all prices”). So basically, I could manually delete the redundant rows, but that would take a lot of time.
Then, I have created another table with a lookup, where I can see all prices in one cell, comma-separated. I managed to pull out the lowest price (MIN function and comparing the MIN result with the uploaded price).
Here is a screenshot of my filtered view:
So my main question is, if there is a way to define the “second-lowest” or maybe even third-lowest price per item in the above mentioned setup?
Any ideas are highly appreciated. If you need more information, please let me know.
Thank you in advance!