整理議會的支出資料、彙總並測試重複付款

This is Telford & Wrekin Council's spend data for December 2025 (sheet 'Transparency Report'). Please tidy it for analysis and add a summary, in this workbook: 1. Delete the unlabelled total row at the bottom (row 3163, a SUBTOTAL formula) so only the transactions remain. Keep the credit notes (negative amounts). 2. Trim leading/trailing spaces in Supplier Name - some suppliers show up twice because of a trailing space, e.g. 'British Gas Trading Ltd '. 3. Add a 'Date' column next to Trans Date holding just the date (Trans Date also carries a time). 4. Add a 'Summary' sheet with: a. Total spend by Service Delivery Area, largest first b. Top 10 suppliers by total spend, with their number of transactions c. A bar chart of spend by Service Delivery Area Use formulas on the Summary sheet where you can, and keep the original row order.

The Summary sheet is missing. Please add it to this workbook as I asked: total spend by Service Delivery Area (largest first), the top 10 suppliers with their number of transactions, and a bar chart of spend by area. Use formulas where you can.

Now run a duplicate-payment test on the same workbook: 1. Add a 'Possible duplicate' column to the data sheet that flags rows where Supplier Name, Amount and Date all match another row and the amount is £5,000 or more. 2. Add an Excel comment on each flagged row: 'Query AP: possible duplicate - obtain invoice nos.' 3. Virgin Media Business also has a credit of -13,500.00 on 23/12/2025. Add a comment on that row: 'Credit same month as flagged Virgin Media duplicates - check if it reverses one of them.' 4. List the flagged rows in a small table on the Summary sheet. Don't change any amounts, and keep the Summary tables and chart from before.