+ Reply to Thread
Results 1 to 4 of 4

Finding all parts that were only purchased by one customer

  1. #1
    Registered User
    Join Date
    08-16-2013
    Location
    Phoenix
    MS-Off Ver
    Excel 2007
    Posts
    2

    Finding all parts that were only purchased by one customer

    Hello All,

    For some reason (maybe because it's Monday), this one has got me stumped.

    I have a spreadsheet with invoice history from 1/1/2015-1/31/2016. I am trying to find a way to identify the parts that were purchased by only one customer (doesn't matter if they purchased multiple times within a month/year, just has to be that the part was purchased by only one person).

    If you see the example below, I only want to return part # ABC2 because Will is the only customer to purchase the product.

    Part # Customer Date
    ABC1 Kate 1/15/2015
    ABC2 Will 2/15/2015
    ABC2 Will 2/15/2015
    ABC1 Will 3/15/2015
    ABC1 John 3/15/2015
    ABC1 Sarah 4/15/2015
    ABC2 Will 5/15/2015


    The Pivot Table isn't working because if I use the count feature it simply counts the number of times the customer shows up for that part, so I don't have a way of filtering that easily.

    I'm not sure if this makes sense, but hopefully someone out there has an idea of what to do!

    Thanks.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,063

    Re: Finding all parts that were only purchased by one customer

    Assuming your data in A to C


    Helper column E

    in E2 and copy down

    =IF(IF(COUNTIFS($A$2:$A$15,$A2,$B$2:$B$15,$B2)=COUNTIF($A$2:$A$15,$A2),1,0)<>0,COUNTIFS($A$2:A2,A2,$B$2:$B2,B2),0)

    in F2

    =IFERROR(INDEX($A$2:$A$15,SMALL(IF($E$2:$E$15=1,ROW($A$2:$A$15)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

    Enter with Ctrl+Shift+Enter

  3. #3
    Registered User
    Join Date
    08-16-2013
    Location
    Phoenix
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Finding all parts that were only purchased by one customer

    Thanks so much! That seemed to do the trick. I'm trying to follow the formula you used in column E. Can you explain briefly what exactly this formula looks at?

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,063

    Re: Finding all parts that were only purchased by one customer

    It compares the COUNTS of Parts+Customer combination against Name so we find the count of ABC2 and Will combination =3 and the COUNT of Will =3 therefore Will is the only purchaser. If this match is found, the result is 1, otherwise zero.

    For each ABC2/Will match, 1 is (effectively) put in column E: this the inner IF ....

    IF(COUNTIFS($A$2:$A$15,$A2,$B$2:$B$15,$B2)=COUNTIF($A$2:$A$15,$A2),1,0)

    The Outer IF

    =IF(IF(.............) <>0,COUNTIFS($A$2:A2,A2,$B$2:$B2,B2),0)

    counts the 1s for the combination of part and name. As the formula moves down the rows, the counts for ABC2/Will are 1, then 2 then 3

    The formula in F2 downwards finds all the 1 counts i.e. first (or only) occurrence of a valid pair and returns the part number.

    You separate these out into 3 columns

    in E2

    =IF(COUNTIFS($A$2:$A$15,$A2,$B$2:$B$15,$B2)=COUNTIF($A$2:$A$15,$A2),1,0) will give list of 1s and 0s

    in F2

    =IF(E2<>0,COUNTIFS($A$2:A2,A2,$B$2:$B2,B2),0) will count (sum) 1s for a give pairing

    in G2

    =IFERROR(INDEX($A$2:$A$15,SMALL(IF($F$2:$F$15=1,ROW($A$2:$A$15)-ROW($A$2)+1,""),ROWS($A$2:A2))),"") will retrieve Part

    Hope this helps

+ 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. Replies: 2
    Last Post: 06-04-2015, 01:42 PM
  2. [SOLVED] Identify Customer Based on $ Amount Purchased
    By clockspring in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-07-2014, 11:02 PM
  3. Finding out how many products a customer permanently stocks.
    By Paxman71 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-25-2013, 10:15 AM
  4. Vlookup for finding parts of text in lookup range cells
    By brandnew22 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-18-2013, 11:20 PM
  5. Finding the right function, totalling customer recency.
    By alphabet_soup in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-20-2013, 08:10 PM
  6. Finding Average Cost per unit of Inventory Purchased
    By Gcolao0019 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-29-2013, 12:20 PM
  7. Replies: 4
    Last Post: 01-01-2012, 03:11 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