How do you summarise data without using Pivot table

[Joolakker]

Hi,

I've got a monthly AP download containing hundreds of lines accross 9 columns. These are a list of all payments made during the month. The problem is that some transactions have been entered on multiple lines. How can I get it to find all the rows where the Vendor name and invoice numbers are the same, add the amounts together and delete the duplicates leaving just 1 line for every invoice from every vendor? The easy way would be pivot tables, but pivot table won't allow that many fields. I could use 1 or 2 fields in the pivot table and then do a lookup for the remaining ones, but I thought there might be an easier way!!

CC ACC Vendor PO Description Invoice Number £ Invoice Date Invoice Batch Name Voucher Number
981100 112030 GRACE ARCHITECTS LTD PROVIDE SCHEDULE 05/193 76.21 20-Mar-06 AP020506 902 32178
981124 112010 LIFTSAFE MHGEN17774 1118 4,995.00 16-Mar-06 AP190406 871 30951
981124 112010 LIFTSAFE MHGEN17774 1118 874.13 16-Mar-06 AP190406 871 30951
981124 112010 LIFTSAFE MHGEN17774 1119 1,835.00 16-Mar-06 AP190406 871 30952
981124 112010 LIFTSAFE MHGEN17774 1120 4,110.00 16-Mar-06 AP190406 871 30953
981124 112010 LIFTSAFE MHGEN17774 1120 719.25 16-Mar-06 AP190406 871 30953
981405 112020 EURODATA SYSTEMS PLC MHGEN17726 272920 700.00 30-Mar-06 AP200406 873 31041
981314 112020 FORM DESIGN EUROPE LTD MHCAP90808 544/2 6,350.00 06-Jan-06 AP250406 886 31615
981300 112010 HORNAGOLD & HILLS MHCAP88001714 15385 WP 3,000.00 06-Mar-06 AP250406 887 31604
981000 112030 HORNAGOLD & HILLS MHSTO60697 15476 WP 5,652.00 31-Mar-06 AP250406 887 31605
981124 112020 KELWAY UK LTD PROFESSIONAL FEES PSIN166752 315.00 19-Apr-06 AP240406 880 31308
981124 112020 KELWAY UK LTD PROFESSIONAL FEES PSIN166752 759.00 19-Apr-06 AP240406 880 31308
981124 112020 KELWAY UK LTD PROFESSIONAL FEES PSIN166752 55.13 19-Apr-06 AP240406 880 31308
981124 112020 KELWAY UK LTD PROFESSIONAL FEES PSIN166752 132.83 19-Apr-06 AP240406 880 31308
981124 112020 KELWAY UK LTD PROFESSIONAL FEES PSIN166752 (.01) 19-Apr-06 AP240406 880 31308
981360 112030 MORGAN COLE SOLICITORS PROFESSIONAL FEES 90162609 1,967.87 29-Mar-06 AP120406 858 30512
981264 112030 BAILY GARNER LLP MHCAP90640 23811/23 2,821.00 10-Jan-05 AP180406 863 30923
981264 112030 BAILY GARNER LLP MHCAP90640 8350 1,350.00 24-Sep-04 AP180406 863 30918

 

[GlennUK]

Excuse me, but can you explain this:

but pivot table won't allow that many fields

statement? What was the problem exactly?

 

[Joolakker]

It comes up with an error message saying "there's too many row or column items".

 

[GlennUK]

What was the layout that you were attempting when the error message came up?

 

[Joolakker]

Thanks, but managed to solve it!! Used sumif followed by delete duplicate code.