Identify Top Customer by Region

December 1998 Challenge: When you have a table listing sales by region and customer, how can you quickly produce a report of each region’s top customer and the sales for that customer?

Congratulations to Kevin from PageGetter who provided this month’s answer.

• Sort the range by ascending region and descending sales.
• In Cell D1, enter the heading “Top?”. In cell D2, enter the formula =A2<>A1
• Copy this formula from D2 to D2:D9.
• Click on Data > Filter > Autofilter
• Click the arrow that appears at the top of column D.
• Select the value True from the dropdown list
• You will now see just the top customer from each region and the sales from that region
• Bonus steps from MrExcel: To quickly copy just these top cells, highlight A1:D8
• Select Edit > Go to > Special > Visible Cells Only > OK
• Hit C to copy the visible cells.
• Click in cell A1 and hit V to paste the top customers in a second section of the sheet

Kevin will be impressing his co-workers at Amerimail with his new MrExcel Guru of the Month coffee mug! Thanks to everyone who submitted an answer.

Jason Szumlanski found MrExcel.com in February and submitted his elegant answer to this month’s problem. While I won’t try to wrestle the coffee mug back from Kevin at Amerimail, I thought you might like to see another approach:Jason used a pivot table and used some very powerful obscure pivot table options.

Let’s walk through the steps Jason used:

• He clicked anywhere in the data and chose Data>Pivot Table.
• He clicked Next> twice to accept the default list of A1:C9.
• He dragged “Sales” to the Data section of the pivot table.
• He double clicked Sales and chose the Max function.
• He dragged Region to the Row section of the pivot table.
• He double clicked Region and under subtotals, he selected None.
• Drag Customer to the Row section of the pivot table.
• Double click Customer. Click Advanced. Under AutoShow options, click Automatic, Top 1. OK, OK
• Click Finish to finish the pivot table. You get the pivot table shown below. Note that the Customer button has a font in blue to alert you that it has the Auto Show selected.

This is an excellent solution, Jason! Thanks for writing.