IT SSM
asked on
Batch Total of numbers between end of batch flags
I have a spreadsheet with 2 columns. No and Flag. The No column is a series of numbers which I need to count in batchs. The Flag columns denotes the end of a batch with a -1 the rest of the time the flag is 0. I need to total the numbers corresponding to the zero's and the -1 then the zero's to the next -1 this list may be 20 rows long or 65000 rows long. The -1 will not always be evenly spaced. But the zeros and the next -1 determine a batch. The series can start on on a -1 or 0 depending on when the data snapshot is taken.
I dont care if the count is incremental for the batch but must restart the count on the next zero after the -1. IE if the first matching number and 0 flag is 11 then the count is 11.
I have attached a spreadsheet with some sample data. I dont have time to muck around working this out and will be solving a more complex issue. I would prefer output example 1 but can work with output example 2 in the sample spreadsheet. I have saved the sample data spreadsheet in 97-2003 format but am using 2010 if that helps.
Thanks for your help in advance.
SampleData.xls
I dont care if the count is incremental for the batch but must restart the count on the next zero after the -1. IE if the first matching number and 0 flag is 11 then the count is 11.
I have attached a spreadsheet with some sample data. I dont have time to muck around working this out and will be solving a more complex issue. I would prefer output example 1 but can work with output example 2 in the sample spreadsheet. I have saved the sample data spreadsheet in 97-2003 format but am using 2010 if that helps.
Thanks for your help in advance.
SampleData.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You're very welcome.
Good luck with the rest of your project.
Good luck with the rest of your project.
ASKER