Forum Discussion
How to create excel table with UNIQUE transactions?
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: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.
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.
- 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.
