+ Reply to Thread
Results 1 to 7 of 7

Unique suppliers with oustanding PO's

  1. #1
    Registered User
    Join Date
    04-11-2016
    Location
    Daventry, England
    MS-Off Ver
    2016
    Posts
    24

    Unique suppliers with oustanding PO's

    Hi

    From a list of purchase order data, I want to determine 2 things:

    1 Number of unique suppliers with open purchase order quantities due in a specified week, and how many of them have outstanding PO's
    2 Number of unique suppliers from beginning of list right up to end of specified week, and how many of these still have outstanding PO's. So 1 is a subset of 2

    The attached excerpt better illustrates what I want and the correct answers I'm looking for. I'm fairly confident in Excel and have used SUM/IF/FREQUENCY/MATCH combinations as an array before but this has me stumped! I'm open to as many helper columns as needed. I could write a little VBA routine but would rather have a non-VBA solution.

    Regards
    Alan
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Unique suppliers with oustanding PO's

    Hi Alan,

    You have a nicely formatted table of data, so I think a Pivot Table answer might work for you. In a Pivot you can filter the data based on Dates and there is even a "Between" two dates ability. Then I think you can get the ordered, shipped and outstanding data into the values box in the Pivot to show what you need.

    See the attached, where I've shown the Date Between filter of your data. I've also counted the number of suppliers in the Pivot. Note - you need to Refresh Pivots after you change data. I'd also make your data a Dynamic Named Range so you can simply add data to the bottom. Then make the source of the Pivot the named range.
    I hope this helps you to a good answer to your problem.
    PT for outstanding shipments.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    04-11-2016
    Location
    Daventry, England
    MS-Off Ver
    2016
    Posts
    24

    Re: Unique suppliers with oustanding PO's

    Hi Marvin

    Thanks for reply. I had ruled out Pivot Table because I need to identify (count) the number of DIFFERENT suppliers. In a week, the same supplier can supply several part numbers, so I need to only count that unique supplier once. Hence my mention of SUM/IF/FREQUENCY/MATCH combination from the Excel Help screens. These show some examples of counting unique values in a list. But I cannot figure out a solution to my problem of including date range.

    Regards
    Alan

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Unique suppliers with oustanding PO's

    Helpers in H and I

    in H4

    =IF(AND($D4>=$K$3,$D4<=$K$3+6),COUNTIFS($A$4:A4,A4,$G$4:G4,">0"),"") fill down

    in I4

    =IF($D4<=$K$3+6,COUNTIFS($A$4:A4,A4,$G$4:G4,">0"),"")

    in J7

    =SUMIF($H$3:$H$17,1)

    in J11

    =SUMIF($I$3:$I$17,1)
    Attached Files Attached Files

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Unique suppliers with oustanding PO's

    Try these formulas:

    Array-enter in I7
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

    In J7:L7
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Array-entered in M7
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Array-entered in I11
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    In J11:L11
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    And array-entered in M11
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Dave

  6. #6
    Registered User
    Join Date
    04-11-2016
    Location
    Daventry, England
    MS-Off Ver
    2016
    Posts
    24

    Re: Unique suppliers with oustanding PO's

    Dave

    The IF/AND/COUNTIFS combination is very clever and one I hadn't thought of. problem now fully resolved.

    Many thanks
    Alan

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Unique suppliers with oustanding PO's

    You're welcome. Thanks for the kind words.

+ 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. How to rotate a suppliers list
    By Lilla Need Help in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-12-2016, 05:19 PM
  2. List Unique Suppliers in Summary
    By ChrisMattock in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-06-2015, 05:09 AM
  3. Suppliers Management database - Huge help need it!!!
    By Danny77 in forum Access Tables & Databases
    Replies: 6
    Last Post: 07-03-2014, 12:48 PM
  4. Sum Suppliers Activity
    By wilensky in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-28-2011, 08:26 AM
  5. selection of suppliers, need help
    By BartekBartek in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-07-2009, 02:59 AM
  6. Replies: 0
    Last Post: 12-08-2006, 02:13 PM
  7. shortages divided by suppliers
    By a94andwi in forum Excel General
    Replies: 3
    Last Post: 12-07-2006, 01:49 PM

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