I'm trying to figureout a straight forward way to group rows based on values above/below a columncell, then combine the values that are being grouped with various mathoperations. I'm using Excel 2010.
For example, saycolumn A has values values that range from 0 to 100.
Logic would be:
- starting at thefirst row, if column A has a value over 50, following rows will be grouped withthis row if they have a column A value under 50
- when rows arecombined, column A will be the value over 50, and a second associated column Bwill take the min/max/sum/other types of simple math operations
For example, assume we want the following data to be grouped in this way, with the math operation being to sum the associated column B:
A B
90 1
12 1
14 2
24 1
65 3
13 1
63 1
70 2
would become:
A B
90 5
65 4
63 1
70 2
Ideally, it would benice to use options in Data> Filter, or Data>Group, but it seems thethese tools do not have enough options - at least at first glance(?). Also, Iwould like to avoid VBA. I figure that any kinds of sorting approaches wontwork since the order of the rows matters (unless there is a resort to theoriginal order at the end somehow).
Thanks for the help
For example, saycolumn A has values values that range from 0 to 100.
Logic would be:
- starting at thefirst row, if column A has a value over 50, following rows will be grouped withthis row if they have a column A value under 50
- when rows arecombined, column A will be the value over 50, and a second associated column Bwill take the min/max/sum/other types of simple math operations
For example, assume we want the following data to be grouped in this way, with the math operation being to sum the associated column B:
A B
90 1
12 1
14 2
24 1
65 3
13 1
63 1
70 2
would become:
A B
90 5
65 4
63 1
70 2
Ideally, it would benice to use options in Data> Filter, or Data>Group, but it seems thethese tools do not have enough options - at least at first glance(?). Also, Iwould like to avoid VBA. I figure that any kinds of sorting approaches wontwork since the order of the rows matters (unless there is a resort to theoriginal order at the end somehow).
Thanks for the help