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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

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,701
What was the layout that you were attempting when the error message came up?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,129
Messages
5,857,537
Members
431,884
Latest member
Gcmoore63

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top