 # Sum through a column and group the rows found

• Hello to all:

Hello

I have a worksheet with over 35,000 rows. I need to find a the target sum of “52800” or the closest to the target sum in Column “G”, insert a row below it and insert the value of the first row & the Value of the last Row In Column (“H?”). On my worksheet column (“H”) will be column (“T”).
Then I will need to group the ranges of the found sums.
I have given an example in the attached workbook Sheet1. Can you help me with coding that will duplicate this in a Macro?

• Re: Sum through a column and group the rows found

Can you clarify the following:

In your description you say 'closest to the target sum', I would interpret this as when ABS(52800-SUM(CurrentRange)) reaches a minimum, where CurrentRange is from the row after the last insertion (or start) to the current row. However in your example Workbook at lines 198, 199, 200 it seems you are inserting the row before SUM(CurrentRange)>52800. Can you confirm which of these is the requirement?

• Re: Sum through a column and group the rows found

Hello Rob

Thank you for your responce to my question. 52800 repesents 10 mile intreviles. I need to be somewhere with 200 <> that total. I could round up or down depending on rather the sum is <> my target.The grouping is set that I may chart and graph the sum points in the next step.

I hope this helps I have been working on this for a week and can't get past this point.

• Re: Sum through a column and group the rows found

Hmm,

It seems to me that column G is a series of increasing numbers, as in possibly cumulative distance from a start point. It does not seem to make sense to sum this again.

Can I suggest you explain the whole problem from the beginning. The issue may be that the approach needs to be revised.

• Re: Sum through a column and group the rows found

Okay Rob I see what is wrong with my example in my haste to copy and paste I omitted a Column. Please see the new attachment.

The distance from last annomaly column is the one which needs to be sum of “52800” or the closest to the target sum <200 or >200 of 52800 I can round up or down to produce the graph.

## Files

• Re: Sum through a column and group the rows found

Hello Rob have you gotten a chance to look at the follow up Post I sent

• Re: Sum through a column and group the rows found

What you are describing here on the forums and what you have provided as an example do not match up. The nature of your data is such that it is not possible to gaurantee that the sum will be within 200 units of 52800.

Try Autofilling the following formula down column J:
=ABS(SUM(\$G\$2:G2)-52800)

Your first new row occurs at 216.42, which is the minimum but not < 200. If you then restart the above formula as:
=ABS(SUM(\$G\$144:G144)-52800)

Autofill that down to the next inserted row, that occurs at 2162.74 which is certainly not < 200 and is not even the minimum, which would be if you included the figures from row 200.
If the same process is carried out on column H instead of G then the sum never gets close to 52800.

You need to look at this again yourself, work out what you actually need then explain that accurately.

• Re: Sum through a column and group the rows found

Thanks Rob for your help, but I think you missed the Thread posted from the start.
"I have a worksheet with over 35,000 rows. I need to find a the target sum of “52800” or the closest to the target sum in Column “G”, insert a row below it and insert the value of the first row & the Value of the last Row In Column (“H?”). On my worksheet column (“H”) will be column (“T”).
Then I will need to group the ranges of the found sums.
I have given an example in the attached workbook Sheet1. Can you help me with coding that will duplicate this in a Macro? "
Thats all I need.The answer you supplied will not work because the row numbers will never be the same.

• Re: Sum through a column and group the rows found

I have read your post from the start. I understand completely that it is not fixed row numbers that it is based on the data in your columns. The point you are missing is that what you are describing as criteria for when to insert the row and your example do not match and when I have asked for clarification your explanations have made less sense not more.

• Re: Sum through a column and group the rows found

Thanks

• Re: Sum through a column and group the rows found

To any one that may need this the answer was given to me by xxxxxxxx@xxxxxx [EMAIL ADDRESS REMOVED BY MODERATOR]

I thing it is very cool...

• Re: Sum through a column and group the rows found

I'm glad you were able to find a solution. Please remember that for any future posts you make any VBA code must be wrapped in [noparse]

Code

[/noparse] tags.

Also, public forums like this are constantly scanned by Bots to harvest email addresses. The person who wrote your code for you is unlikely to be pleased if their email address is added to the list of these Bots as a target for Spam. I have therefore removed the email address.

• Re: Sum through a column and group the rows found

Thanks Your help got me on the right track. However now that I am able to handle the grouping I have run into a problem with the Charts because the Sheet names for the data range change with each new Import.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!