pivot(?) on number of *unique* order numbers by order taker?

[edjusted]

I have a report that shows each line item on open orders as it's own row.

So let's say there are 3 orders with 3 items each. The report would have 9 rows and might look something like this:

Order #	Order taker	Item	Qty
123	Bob	stuff	2
123	Bob	stuff	2
123	Bob	stuff	2
222	Jane	stuff	2
222	Jane	stuff	2
222	Jane	stuff	2
333	Bob	stuff	2
333	Bob	stuff	2
333	Bob	stuff	2

<tbody>
</tbody>

What I ultimately want to see is a table that shows me how many unique orders each order taker has. In this case, it might look something like:
Bob: 2
Jane: 1

I can do a pivot using Order Taker and Order Number as the rows, and see:
Bob
123
333
Jane
222

But I can't figure out how to see how many individual orders each person has. Is there a way to do this?

 

[AFPathfinder]

In the pivot table, you can just do Order taker in the rows and Order taker (count) in the values. That will give you a unique value count for all the people who are listed as taking orders.

 

[edjusted]

Thanks AFPathfinder. That doesn't seem to give me the right numbers. With my example, I'm looking for:
Bob: 2
Jane: 1

But putting Order taker (count) in the values (or Order *number* (count)) gives me the number of rows, i.e.:
Bob: 6
Jane: 3

..because Bob shows up in 6 rows and Jane shows up in 3.

Maybe I'm misinterpreting what you're saying?

 

[AFPathfinder]

Oops! I misread your post. I think you'd need to copy and paste a new table to perform the remove duplicates. However, if you plan on using those items and quantities, that wouldn't be the best option. If you don't have many names, you could just build a new table out of COUNTIFS/SUMIFS. VBA would be a viable option, as well.

 

[edjusted]

Ah, ok, I'll play around with Countifs/Sumifs instead of pivot tables. Thanks!

 

[Fluff]

If you're interested in a macro, how about

Sub GetQty()

   Dim Cl As Range
   Dim Itm As Variant
   Dim Rw As Long
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            .Add Cl.Value, Cl.Offset(, -1).Value
         ElseIf InStr(.Item(Cl.Value), Cl.Offset(, -1)) = 0 Then
            .Item(Cl.Value) = .Item(Cl.Value) & "," & Cl.Offset(, -1).Value
         End If
      Next Cl
      Range("G2").Resize(.Count).Value = Application.Transpose(.keys)
      Rw = 2
      For Each Itm In .items
         Range("H" & Rw).Value = UBound(Split(Itm, ",")) + 1
         Rw = Rw + 1
      Next Itm
   End With
         
End Sub

 

[Fluff]

Or maybe

Sub GetQty2()

   Dim Cl As Range
   Dim Ky As Variant
   Dim Rw As Long
   Dim Dic As Object
   Dim Nme As String
   Dim Ord As String
   
   Set Dic = CreateObject("scripting.dictionary")
   For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
      Nme = Cl.Value
      Ord = Cl.Offset(, -1).Value
      If Not Dic.exists(Nme) Then
         Dic.Add Nme, CreateObject("scripting.dictionary")
         Dic(Nme).Add Ord, Nothing
      ElseIf Not Dic(Nme).exists(Ord) Then
         Dic(Nme).Add Ord, Nothing
      End If
   Next Cl
   Rw = 2
   For Each Ky In Dic.keys
      Range("G" & Rw).Value = Ky
      Range("H" & Rw).Value = Dic(Ky).Count
      Rw = Rw + 1
   Next Ky

End Sub