Hello everyone,
This is going to be my first post on this forums, I will try to make it as clear as possible.
I have a database with clients that we supply and transport companies that help us deliver the goods. It often happens that in certain location (city) there are different clients supplied by different companies, while we could use only one of these companies and make it much easier for us.
I want to create a macro that would suggest alternative delivery companies (transferring our services from the ones that do least to the ones that do most of the supply).
The database itself is more less like
Supplier name / supplier address / client name / client address
So far I have added a column with supplier status and suggested supplier for each of the clients objects.
I also generated a pivot table with all the suppliers and the amount of client objects they can work for.
I made another pivot table which could be the main source of information for the macro itself like:
City of the client // all the suppliers that can work in this city // number of clients that can be globally supplied by suppliers in clients city
Now about the macro itself. I want it to search for the lowest value of the globally supplied objects and for each city check if there are at least 2 suppliers in that city. If there are 1 or 2 suppliers in any city I want these suppliers to be given status "don't change" and suggested supplier to remain the same as the original one.
if there are more than 2 suppliers in a city I want the one with the globally lowest number to have suggested supplier (with the highest globally supplied objects) and set the status "change"
Make it a loop so all suppliers will be given status change / don't change and the proper suggested suppliers.
I'm totally open to any questions regarding this issue and I will be extremely grateful for any help.
Bookmarks