Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

How to create excel table with UNIQUE transactions?

Copper Contributor

Hello,

 

- I have a sheet contains 10,000 transactions (rows) - invoices

- Column A contains client name (which is most likely unique)

- Column B contains a unique client ID number

- Column C contains Invoice number, Column E contains Amount


I want to create a new sheet / or the same that includes the UNIQUE clients with their transactions and summary of each client total invoices.

 

It should look like:

Client A | 101101 | $5000

Client B | 101102 | $9000 

 

On the other sheet I want to see all transactions allocated to each client in descending order.

A                B           C           E

Client A | 101101 | 00001 | $3000

Client A | 101101 | 00002 | $2000 

Client B | 101102 | 00003 | $6000

Client B | 101102 | 00004 | $3000

and so on...

 

 

Thank you!

1 Reply

Hello @User2103,
You could try using a PivotTable. Here's how:

  1. Create a PivotTable:

    • Select the entire data (including headers).
    • Go to Insert > PivotTable.
    • In the Create PivotTable dialog box, choose New Worksheet and click OK.
  2. Set up the PivotTable:

    • Drag Client Name and Client ID to the Rows area.
    • Drag Invoice Number and Amount to the Values area.
    • For Amount, click on the drop-down arrow, select Value Field Settings, and choose Sum.
    • This will give you a new sheet with unique clients and the total of their invoices.
  3. Sort the transactions:
  • Go back to your original data sheet.
  • Select the entire data (including headers).
  • Go to Data > Sort.
  • In the Sort dialog box, sort by Client Name and then by Amount in descending order.

This will sort all transactions allocated to each client in descending order of the amount.