How to make a ranking of the best buyers through Excel?

Question

I have an Excel table with 15 thousand lines. Each of these lines means a product sold.

The layout is like this:

Produto - Nome do comprador - Valor do produto

I need to make a ranking of the five people who spent the most money on the company.

For this, I need to perform a calculation that adds up the amount of 'buyer’s name' that there is and add the value of purchases, adding the column 'product value'.

That is, if there is a register João da Silva, I need to know if he bought more than once and what was the total value of products he bought.

I thought about using PROCV, but I don’t know how to do this count. Yeah, plus I need there to be a connection between the name and how much the person spent in the store.

Does anyone have any idea what formula I could use?

Answer 1

Dynamic tables can be used.

Solution

With a table as in the following example:

| Produto | Nome do Comprador | Valor do produto |
|---------|-------------------|------------------|
| A       | João              | R$ 30,00         |
| B       | João              | R$ 20,00         |
| A       | Maria             | R$ 15,00         |
| C       | Enzo              | R$ 40,00         |
| B       | Enzo              | R$ 19,00         |
| D       | José              | R$ 35,00         |
| E       | Valentina         | R$ 45,00         |
| D       | Joana             | R$ 35,00         |
| A       | Joana             | R$ 29,00         |

Select table data and "Insert" -> "Dynamic Table"

In the dynamic table fields select the "Buyer Name" and "Product Value"

And in values, select the Sum option.

In the filter select "More Sorting Options..."

In the sorting options select in order "Decreasing (from Z to A) by:"

and the item "Sum of Product Value"

Upshot

Answer 2

Do so:

1. Create a new tab.

2. In this new tab, make a column of unique buyers' names from their original tab (help source tip: https://www.extendoffice.com/pt/documents/excel/1627-excel-extract-unique-values-from-list.html)

3. Make another column with the sums per buyer, using the SOMASE function for each single column item created in item 2 (help source tip: https://support.office.com/pt-br/article/fun%C3%A7%C3%A3o-somase-169b8c99-c05c-4483-a712-1697a653039b).

4. Sort the values by the summation column in descending order. This is your ranking.