Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Macro to sum excel

Posted on 2011-10-21
8
Medium Priority
?
359 Views
Last Modified: 2012-05-12
I have a very large spreadsheet containing 38,000 rows and 93 columns.
There are 2,600 sections within those 38,000 rows.  These rows contain sections of up to 60 rows with the same identifying number in the first column.  I need to have each of these sections summed by column for 93 columns and included in a new worksheet as a separate row.
Attached file contains full spreadsheet and example of second worksheet containing first three sections.
0
Comment
Question by:gregfthompson
  • 6
  • 2
8 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37010162
Enter this formula in D7 and then copy it down and across

=SUMIF('AUS CCD'!$A$7:$A$38703,'AUS POSTCODE'!$A7,'AUS CCD'!D$7:D$38703)
0
 

Author Comment

by:gregfthompson
ID: 37010209
Thanks.

Looked good for the first three rows, but then all other rows showed zero.  

The rest showed zero. What did I do wrong?

And I also need the first postcode and first postcode name in columns A and B in thw main worksheet to be copied into the second worksheet.

Thanks,

Greg
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:gregfthompson
ID: 37010227
Thanks I workd out the problem.  Whens there's no postcode and there's nothing to relate to.

Can you provide a way to select first postcode and first postcode name in columns A and B in the main worksheet to be copied into the second worksheet?

Then your formula will work.

Thanks heaps,

Greg

0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 2000 total points
ID: 37010425
For column A here are two methods (Without VBA)

Method 1: Advanced filter

Data menu > Sort & filter group > Advanced
Copy to another location
Give the column A of the AUS CCD sheet
Copy to some blank range
Check unique records only

when you click ok you can get the list of postcodes at the copy to range. You can copy this range to the Postcode sheet

Method 2: Array formula

Enter the first postcode (800) in A7

use this array formula in A8 and copy it down
=OFFSET('AUS CCD'!$A$1,MATCH(TRUE,OFFSET('AUS CCD'!$A$1,MATCH(A7,'AUS CCD'!A:A,0),0,1000)>A7,0)+MATCH(A7,'AUS CCD'!A:A,0)-1,0)


For column b enter this formula in B7 and copy it down.
=VLOOKUP('AUS POSTCODE'!A7,'AUS CCD'!$A:$B,2,0)
0
 

Author Closing Comment

by:gregfthompson
ID: 37010483
THANK YOU!!
0
 

Author Comment

by:gregfthompson
ID: 37013045
PS: How can I filter the Postcode and Subname lists so there is a suburbname against the single postcode?
0
 

Author Comment

by:gregfthompson
ID: 37013055
PPS: Thanks I saw your final line -- all fixed .  Thanks heaps again!!!
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question