Loop through group of values, calculate a total

**JHizzal** · 03-07-2012, 11:22 AM

The Issue:
I have a fairly large excel file (2000+ rows) that contains groups of values in column A (that vary in length), then numerical values in columns C and D that are associate with those values. I need to go through each of these groups and calculate a total based on adding all the values for that group that are located in columns C and D.

The one nice thing is that the values of the groups are all together.

The Problem:
My problem is that I'm having a tough time trying to figure an algorithm to compute the proper total. In Java I could use maps and lists and associate it through creating dynamic classes. VBA is a bit different.

Any help would be greatly appreciated.

Attached is a sample workbook. Sheet 1 contains the data. Sheet 2 is where I'm trying to print the summary data and what I'm wishing the data to look like.

**Kelshaer** · 03-07-2012, 11:41 AM

It's kind of confusing trying to figure out how would you produce the Sheet2 from Sheet1.
Can you please go to Sheet2 and type real values reflecting what is in Sheet1 then reUpload the file?

**abousetta** · 03-07-2012, 11:53 AM

I did this with a pivot table:

Please Login or Register  to view this content.

This is what you are after?

**JHizzal** · 03-07-2012, 01:14 PM

abousetta,
In a way yes. I had considered pivot tables, but I need just one value for each group. So A should have a value of 59.285, B should have a value of 51.71.

Sheet2 then holds a department number for each group, and I take the total for each group for their department. This is easily calculated using pivot tables, and that's what I will do on sheet2

I just am a bit stumped on how to loop through sheet 1 and get a single value for Group A, Group B, Group C, etc until the end of a 2,000 row sheet from Sheet1.

Does that make more sense? Or do I need to clarify more?

**Kelshaer** · 03-07-2012, 01:36 PM

Here you go:

Please Login or Register  to view this content.

**abousetta** · 03-07-2012, 02:04 PM

The fastest way is to feed the whole range into an array and calculate everything in memory...

having said that, I still doesn't understand how you got a value of 59.285 for A and 51.71 for B.

If you could explain a bit more, maybe we can expedite the solution.

**Kelshaer** · 03-07-2012, 02:08 PM

Originally Posted by abousetta

The fastest way is to feed the whole range into an array and calculate everything in memory...

having said that, I still doesn't understand how you got a value of 59.285 for A and 51.71 for B.

If you could explain a bit more, maybe we can expedite the solution.

The numbers are the sum of columns C & D in Sheet1