The traveling salemen who got sick. Sorting the suspected customers after counting in a pivot table.

[Tommy2Tables]

The salesmen who visited these customers all got sick. I want to find which are the most common customers in the list. I want to sort the count of how frequently a customer shows up in a COUNT in the Values part of a pivot table, on just the customers' names, then sort that list. But with only one column to sort, the Sort & Filter part of the OPTIONS tab for the pivot table is not available.
I tried to do a previous, non Pivot Table method, of selecting the list, Data, Filter, Advanced Filter, filter in place, unique values only and then put a column next to the filter list like =COUNTIF($B$2:$B$32,B2). That would work and brought the most infected customer, Ines, to the top of my practice list, but in my real data, which is over 100k rows per territory, it crashes, so I hoped to do it with pivot table.

List of customers who were visited by salesmen who got sick Who was visited most often?
[TABLE="width: 116"]
<colgroup><col></colgroup><tbody>[TR]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 116"]
<colgroup><col></colgroup><tbody>[TR]
[TD]CUSTOMERS[/TD]
[/TR]
[TR]
[TD]James Wheat[/TD]
[/TR]
[TR]
[TD]Joey Flory[/TD]
[/TR]
[TR]
[TD]Ross Beckemeyer[/TD]
[/TR]
[TR]
[TD]Ines Lobaugh[/TD]
[/TR]
[TR]
[TD]Ines Lobaugh[/TD]
[/TR]
[TR]
[TD]Brian Matias[/TD]
[/TR]
[TR]
[TD]Twila Leimbach[/TD]
[/TR]
[TR]
[TD]Andy Kysar[/TD]
[/TR]
[TR]
[TD]Ines Lobaugh[/TD]
[/TR]
[TR]
[TD]Ramona Borges[/TD]
[/TR]
[TR]
[TD]Belinda Roby[/TD]
[/TR]
[TR]
[TD]Ines Lobaugh[/TD]
[/TR]
[TR]
[TD]Ramona Borges[/TD]
[/TR]
[TR]
[TD]Michael Amato[/TD]
[/TR]
[TR]
[TD]Ines Lobaugh[/TD]
[/TR]
[TR]
[TD]Ramona Borges[/TD]
[/TR]
[TR]
[TD]Ines Lobaugh[/TD]
[/TR]
[TR]
[TD]Ramona Borges[/TD]
[/TR]
[TR]
[TD]Bridgett Althaus[/TD]
[/TR]
[TR]
[TD]Belinda Roby[/TD]
[/TR]
[TR]
[TD]Ines Lobaugh[/TD]
[/TR]
[TR]
[TD]Ramona Borges[/TD]
[/TR]
[TR]
[TD]Bridgett Althaus[/TD]
[/TR]
[TR]
[TD]Concetta Tasso[/TD]
[/TR]
[TR]
[TD]Belinda Roby[/TD]
[/TR]
[TR]
[TD]Ines Lobaugh[/TD]
[/TR]
[TR]
[TD]Ramona Borges[/TD]
[/TR]
[TR]
[TD]Stuart Phaup[/TD]
[/TR]
[TR]
[TD]Willard Bolan[/TD]
[/TR]
[TR]
[TD]John Gottard[/TD]
[/TR]
</tbody>[/TABLE]

The method of Data Filter Advanced
Filter list in place
Unique records only
gives
[TABLE="width: 116"]
<colgroup><col></colgroup><tbody>[TR]
[TD]CUSTOMERS[/TD]
[/TR]
[TR]
[TD]James Wheat
[/TD]
[/TR]
[TR]
[TD]Joey Flory
[/TD]
[/TR]
[TR]
[TD]Ross Beckemeyer
[/TD]
[/TR]
[TR]
[TD]Ines Lobaugh[/TD]
[/TR]
[TR]
[TD]Brian Matias[/TD]
[/TR]
[TR]
[TD]Twila Leimbach[/TD]
[/TR]
[TR]
[TD]Andy Kysar[/TD]
[/TR]
[TR]
[TD]Ramona Borges[/TD]
[/TR]
[TR]
[TD]Belinda Roby[/TD]
[/TR]
[TR]
[TD]Michael Amato[/TD]
[/TR]
[TR]
[TD]Bridgett Althaus[/TD]
[/TR]
[TR]
[TD]Concetta Tasso[/TD]
[/TR]
[TR]
[TD]Stuart Phaup[/TD]
[/TR]
[TR]
[TD]Willard Bolan[/TD]
[/TR]
[TR]
[TD]John Gottard
and the
=COUNTIF($B$2:$B$32,B2)
gives[TABLE="width: 116"]
<tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
which can then be sorted to bring the infectious Inez and Romona to the top,
but this method crashes with big files.

I got help here for a more involved system that I wanted to automatically subtract out discounted the customers who did not make salesmen sick, but the nifty solutions provided by shawnhet and Marcelo Branco were beyond my abilities to understand at this time in my excel development, so I am hoping pivot tables is the way.

Thank you.
Tommy Two Tables

[/TD]
[/TR]
</tbody>[/TABLE]

 

[dspel79082]

Tommy, yes I think pivot table is the way to go. All you need to do is use the customer list as both the row lables and the values. You can reuse the same field as many times as you like by dragging it from the top in the Field List into the area at the bottom - filter, columns,rows, values.

[TABLE="width: 241"]
<tbody>[TR]
[TD]Row Labels
[/TD]
[TD]Count of Customer
[/TD]
[/TR]
[TR]
[TD]Ines Lobaugh
[/TD]
[TD="align: right"]8
[/TD]
[/TR]
[TR]
[TD]Ramona Borges
[/TD]
[TD="align: right"]6
[/TD]
[/TR]
[TR]
[TD]Belinda Roby
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Bridgett Althaus
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]John Gottard
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Andy Kysar
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Brian Matias
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Michael Amato
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Concetta Tasso
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Ross Beckemeyer
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Stuart Phaup
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Twila Leimbach
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Willard Bolan
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]James Wheat
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Joey Flory
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Grand Total
[/TD]
[TD="align: right"]30
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Debbie S

 

[Tommy2Tables]

How did you get the Count of Customers to Sort? I know that you can reuse the field CUSTOMERS in both the PT Row Labels and Values (as Count of CUSTOMERS), but when I try to Sort on the Sort & Filter tab of the Options tab on the ribbon, it is not active. Is the answer that you were able to get the row label for Values to be CUSTOMER instead of CUSTOMERS by typing it into some field address?
Thank you.

 

[Tommy2Tables]

Ooops. I got it . In PT I need to pick a value in the Count column, then, sort; outside of Pivot Table, you can pick the whole column. Derp.
I am still getting crashing because of the size of the list. I believe I have removed blank rows from what is being picked for PT. I wonder if I am keeping too much data in the worksheet because of previously deleted rows and columns still being manipulated? How can I make sure that I have completely deleted blank rows and column?
Thank you.
T2T

 

[Tommy2Tables]

Thank you Debbie S.
T2T

 

[dspel79082]

Tommy, what version of excel are you using?

 

[Tommy2Tables]

excel 2010. I got it to run. I don't know if it was deleting excess columns or deleting blank spaces or rebooting the computer, but I have had success. Thanks