Forum Discussion

Ad2Mollie's avatar
Ad2Mollie
Copper Contributor
Feb 19, 2022

Get total sales per customer

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. 

Excel
Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Mar 25, 2024

    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))
  )

     

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Feb 19, 2022

    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.

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Feb 19, 2022

    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)

     

Resources