Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts
    One of our users gave me the attached spreadsheet and said she needs to count the unique values in column B (wonum / aka work order number) for each column L (laborcode - aka foreman) within each column A (loc - aka location)

    I tried a pivot table, but it listed each labor code and the number of wonums for each one, not the number of unique.

    I tried subtotals, but that didn't work either.

    I tried using the frequency and match formulas, but I don't know how to use them and I gave myself a headache. Can you please help me?
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If it does not have to be a one step process (I not sure it can be a one step unless that step is a macro).

    First copy the three columns to another location, rearranging the columns to LOC-laborcode-wonum
    Next, while within your new columns, go to Data / Filter > Advanced Filter...
    Use the Copy to another location, choose unique records only and then choose you location.
    Do another Data / Filter > Advanced Filter... - using the LOC and laborcode columns (youll have to define the range and copy to location again.

    After the last filter you can get the unique counts by using the Sumproduct function.
    =sumproduct((LocRange=Loc)*(laborcoderRange=laborc ode))

    See attached for demonstration.
    Attached Files Attached Files

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That looks great. I sent it to the coworker who was asking for it. Thank you so much. And, thank you for including the instructions. That helps me learn how to do this on my own next time.

Posting Permissions

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