How do you summarise data without using Pivot table

Joolakker

New Member
Joined
Sep 19, 2006
Messages
7
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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Joolakker

New Member
Joined
Sep 19, 2006
Messages
7
It comes up with an error message saying "there's too many row or column items".
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
What was the layout that you were attempting when the error message came up?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,406
Messages
5,547,759
Members
410,811
Latest member
adustin42
Top