Get total sales per customer

[Ad2Mollie]

I have an excel spreadsheet of sales for the last couple of years. Is there a way to merge the individual customer's sales to remove the duplicate listing of the customer but retain the total sales for that customer with his or her record?

There are over 3,200 sales, some customers have made 2-12 purchases, each purchase is listed separately with all of the customer data. I would like to combine put all of the sales for each customer with one record for them. So the name, address, city, email, and ph number are only listed once, but the total value of their purchases would be with that one record. Hopefully, this makes sense. 

[#2]

@Ad2Mollie Perhaps a simple pivot table is enough. Customer ID and Name as Row labels, Sales amount in the Value field and perhaps the Date (grouped by year) as the Column label.

I would NOT create a pivot table with all the address information included. Couldn't it be that customer information changed over the years? Perhaps they moved or changed phone number. I presume you have assigned unique customer ID's, so a pivot table with just the ID and a name (for legibility) should suffice. Current customer information is best kept in a separate database/table.

[#3]

@Ad2Mollie 

As Mr. Riny_van_Eekelen informed you, a simple pivot table would work.

Alternatively, you could also use the filter function or the existing templates from Microsoft, which are available to everyone.

Sales report templates

Use these templates to track and report your weekly, monthly, and quarterly sales

Finally, with your permission, if I can recommend you, add a file (without sensitive data) to your project. Explain your plans in relation to this file.

So you can get a solution that is tailored to your needs much faster.

*Knowing the Excel version and operating system would also be an advantage.

At the same time, it is much easier for someone who wants to help to understand the subject.

A win-win situation for everyone :).

NikolinoDE

I know I don't know anything (Socrates)

[#4 · Accepted solution]

@Ad2Mollie 

You probably already have an answer but I found a good solution and thought it might help others.

Use the Consolidate feature on the data tab. 

Good video here https://www.youtube.com/watch?v=IGaq_biQ1B8

[#5]

@Teresa1810 Thanks for the tip. Was very helpful.

[#6]

@Ad2Mollie 

This is a simple exploration of what is possible with the latest (insider beta) version of Excel.

= LET(
    combined, VSTACK(PreventionTbl, EAPTbl, RecoveryTbl, DigitalTbl),
    GROUPBY(TAKE(combined,,1), DROP(combined,,1), SUM, 0, 1)
  )

Without GROUPBY life gets a little harder

= LET(
    combined,     VSTACK(PreventionTbl, EAPTbl, RecoveryTbl, DigitalTbl),
    corporations, TAKE(combined,,1),
    distinctCorp, UNIQUE(corporations),
    amounts,      DROP(combined,,1),
    item, MAKEARRAY(COUNTA(distinctCorp), COUNTA(header) - 1,
      LAMBDA(r,c,
        LET(
          yrAmount, CHOOSECOLS(amounts,c),
          distinct, INDEX(distinctCorp, r),
          SUM(FILTER(yrAmount, corporations=distinct)))
        )
      ),
    SORT(HSTACK(distinctCorp, item))
  )