Pivot Table with Text: How to View Data

[evanlamarr88]

I have 27 salesmen with customers in general geographic areas. However, I have two issues I'm hoping to address with this thread.

1. I'm afraid their geographic areas are becoming too diverse - not enough locality.
2. I want to be able to see an overview where salesmen have customers.

I've attached a spreadsheet with the general info, but it's not condensed enough. My problem is that there's three columns of data. I want something like a pivot table where I can better manage the data, but Pivot Tables just give numbers. I want to be able to use text instead of "Count, Sum, Average," etc.

• I want to be able to see how many states in which each salesmen have partners. A pivot table just sums the count of state, so I can't see the number of individual states.
• I want to be able to put the states as columns and put the owners as row values underneath each state. Pivot tables just put numbers.
• I want to see how which salesmen have partners in each state. Again, Pivot Tables just counts the number of times a state appears.

I'm also open to any other ways you may better visualize the data. I think INDEX or INDEXMATCH may be a good function to use here, but I've never used them, and Excel's tutorial about them doesn't seem to give a very good overview of their functionality.

Thanks for any insight you can give! I hope my question is not too vague.

 

[bobhc]

Why not use your data to make charts, pivots are there to work with numbers/data

 

[GraH - Guido]

evanlamarr88, Could you add a sample of how the output should look like? And care to share which version of Excel you are using?
Power Query can do a pivot "show" text and does have a count distinct group by. So I think it is do-able in that way.