Massive Spreadsheet Problem

Aargh!!

New Member
Joined
Feb 18, 2002
Messages
37
Hello, I use Excel 2003 working on a very large spreadsheet that details all the items sold by the company I work for over a 12 month period. The data is exported from Crystal and is so large that it automatically groups the pages (68,000 rows, 6 columns).
The problem I have is the number of identical entries but cost and values are debit & credit(see below for example). I would like to delete all of the negative and matching positive rows to free up space and possibly allow whole lot to fit onto one page. I use this data to create a pivot table so others can use it but excel crashes when I attempt to so do with a group so I need to remove some of the items that are inter company transfers.

Date Invoiced Customer Product No. Cost Value
23/10/07 A Green CA24 19 16.2 307.8
23/10/07 J Smith CA24 16 33.20 531.20
23/10/07 J Smith CA24 16 (33.20) (531.20)
23/10/07 P Black CA12 16 12.53 200.48
24/10/07 J White CA36 35 16.75 586.25

If you can help with this it would be much appreciated.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

lalbatros

Well-known Member
Joined
Sep 5, 2007
Messages
659
Don't delete anything.
Just add a column to indicate the type of entry (like >0 or <0).
Use the pivot table to select the data you want.
If the dataset is really too large, then go back to the database.
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
So what would be a unique match in this case?

Would it only be these 3 for a unique match or can J Smith buy product CA24 twice in one day?

Date Invoiced
Customer
Product No.
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
I assume you are using Excel 2007 since you said there are 68,000 rows. You can use this formula in a helper column, but I would set calculation to manual, input the formula copy it all the way down and use shift+F9 to calculate just that sheet (Note this may take quite a bit of time). Then filter on the helper column for 0 and delete those rows.

=sumproduct(--($A$1:$A$70000=A1),--($B$1:$B$70000=B1),--($C$1:$C$70000=C1),$E$1:$E$70000)

Change A1, B1, C1 to the correct starting row. Hope that helps.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

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