Hello all!
I have very limited experience with programming. But i need to write a function for my spreadsheet. Let me try to explain.
In the spreadsheet there are several lists. Each list contain the price and location of one commodity. In column A there is a description of the location, and in column B the price is listed. Each list is sorted by price (low to high). For some commodities there are up to 50 locations and for some only a few. Since it's sorted by price, the same location can appear randomly through out the list if there are more then one supplier on that specific location.
Here's what i need to do. I'm only interested in 5 specific locations, and i'm only interested in the lowest price of each location. Since there is over 50 lists, and they are updated frequently, i can't go through all information manually. Let's call the locations "L1 - L2 - L3 - L4 - L5".
First of all, i have all the lists on one sheet and i want the function to go through the lists and get the lowest prices from each location, for each commodity, and put them on a separate sheet where i have it organised so i can get a good overview of the information.
So here is what i'm thinking in (what i think is) pseudo code.
___________________________________________________________________________________________
Start from the top of the Column A (location) of the first list
* Start loop
* * If (the cell contains the name of L1)
* * * Copy the price from the same line but column B (price) and paste it on a specified cell in the overview sheet.
* * * End loop
* * else if (the cell does not contain the name of L1)
* * * Step down one cell and repeat
* * else if (The cell contain no text) ;;;there was no match
* * * Stop searching for L1
* * * End loop
* Start loop
* * If (the cell contains the name of L2)
* * * Copy the price from the same line but column B (price) and paste it on a specified cell in the overview sheet.
* * * End loop
* * else if (the cell does not contain the name of L2)
* * * Step down one cell and repeat
* * else if (The cell contain no text) ;;;there was no match
* * * End loop
_____________________________________________________________________________
And so on for each of the 5 locations. I'm guessing it's not optimal - but it seems to be the most intuitive approach for someone with limited experience like me. When it's finished with one list i want it to move on to the next. But maybe if i can get going with this i will be able to figure that out by my self!!
Any help would be greatly appreciated!
Best Regards,
milmil
Bookmarks