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
Medium Priority
359 Views
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
Question by:gregfthompson
• 6
• 2

Author Comment

ID: 37010059
0

LVL 43

Expert Comment

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

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

Author Comment

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?

Thanks heaps,

Greg

0

LVL 43

Accepted Solution

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

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

ID: 37010483
THANK YOU!!
0

Author Comment

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

Author Comment

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

## Featured Post

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â€¦
###### Suggested Courses
Course of the Month11 days, 22 hours left to enroll