+ Reply to Thread
Results 1 to 12 of 12

Lowest price & Lowest lead time

  1. #1
    Registered User
    Join Date
    10-19-2012
    Location
    Houston, US
    MS-Off Ver
    Excel 2007
    Posts
    46

    Lowest price & Lowest lead time

    I'm new in this forum and need help to automate the work below:
    I have an excel file with 3 sheets:
    Sheet 1 has all data for Part #, Suppliers, prices, lead time.
    I want to have a list in sheet 2 with all lowest price regardless lead time and a list in sheet 3 with all lowest lead time regardless prices from the data from sheet 1. Please see attached file.
    Thank for your help.
    Thup_98
    Attached Files Attached Files

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Lowest price & Lowest lead time

    Use Pivot table:
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    10-19-2012
    Location
    Houston, US
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: Lowest price & Lowest lead time

    Thanks for your help zbor! but I need a list with all data in the row that pivot table pulls from sheet 1. Please see the ex. in sheet 1
    Thup_98

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Lowest price & Lowest lead time

    just click (+) near by each supplier and you will see every suplier value and minimum value as it's showed.
    You get even more

  5. #5
    Registered User
    Join Date
    10-19-2012
    Location
    Houston, US
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: Lowest price & Lowest lead time

    Price
    Part number Supplier Price Lead Time
    abc123 A 1 5
    def123 C 5 11
    ghi123 C 1 10
    jkl123 A 5 20
    Would you please show me how to put the data using your pivot table into above format?
    Thanks

  6. #6
    Registered User
    Join Date
    10-19-2012
    Location
    Houston, US
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: Lowest price & Lowest lead time

    Hi Zbor,
    Every day I have 2 to 3 files to be sorted out the lowest price and lowest lead time and each file has more than 300 rows sometime more than 500 rows if using pivot table then manually convert it to the required format will take a lot of time plus mistakes. Do you have another method to automate this task?
    Thank you for your help.
    Thup_98

  7. #7
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Lowest price & Lowest lead time

    A question.. from your example you say:

    all lowest price regardless lead time
    And then from values:

    Please Login or Register  to view this content.
    Your result is 2nd value (Supplier A). Can that be 1st value (Supplier B) since lead time is ignored?

  8. #8
    Registered User
    Join Date
    10-19-2012
    Location
    Houston, US
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: Lowest price & Lowest lead time

    Hi Zbor,
    In this case I prefer to choose supplier A because it shorter lead time but if somehow we cannot choose it then it's Ok to choose supplier B.
    Thank you for your help.
    Thup

  9. #9
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Lowest price & Lowest lead time

    Here, try this (it will take first supplier with minimum value, no matter of another value):
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    10-19-2012
    Location
    Houston, US
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: Lowest price & Lowest lead time

    Thank you so much! Could you send me the marco so I can learn more about excel.
    Thup

  11. #11
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Lowest price & Lowest lead time

    There is no macro :D

    It use 2 helper columns.

  12. #12
    Registered User
    Join Date
    10-19-2012
    Location
    Houston, US
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: Lowest price & Lowest lead time

    Thank you,
    Thup

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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