+ Reply to Thread
Results 1 to 2 of 2

Create a macro optimizing the number of contractors

  1. #1
    Registered User
    Join Date
    07-24-2020
    Location
    Warsaw, Poland
    MS-Off Ver
    365
    Posts
    2

    Create a macro optimizing the number of contractors

    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.

  2. #2
    Registered User
    Join Date
    07-24-2020
    Location
    Warsaw, Poland
    MS-Off Ver
    365
    Posts
    2

    Re: Create a macro optimizing the number of contractors

    Surprisingly someone else has helped me write macro solving the issue. I will mark thread as solved. If anyone wants to discuss the problem we can still do it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Optimizing a number matrix
    By Ian McPherson in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-16-2020, 09:35 AM
  2. Need help optimizing formula that returns row number
    By trickyricky in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-07-2018, 03:26 PM
  3. [SOLVED] Need Help Optimizing Macro
    By calebjcook in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-28-2015, 11:08 PM
  4. Optimizing Excel macro to find/replace with the list of data contained in the macro
    By Iceyburnz in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-17-2008, 08:41 PM
  5. Balance from multiple contractors
    By bkbid in forum Excel General
    Replies: 1
    Last Post: 01-15-2008, 11:30 PM
  6. [SOLVED] Any cost estimating templates for contractors available?
    By Joe in forum Excel General
    Replies: 0
    Last Post: 04-15-2005, 03:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1