PDA

View Full Version : Performing count and max commands on multiple groups of rows



heydude
02-02-2018, 01:21 PM
Hi, Let's say you have rows:
0.000
0.000
0.000
5.000
6.000
10.000
0.000
0.000
0.000
5.000
...
There is no consistency between the sets, just groups of zeros and non-zeros.
I need a set of commands that can count the number of zeros between each set of numbers and report it in the next column, count the number of non-zero cells in each group and report it, and find the max in each set of non-zero numbers i.e.:
A B(count) C(max)
0.000
0.000
0.000 3
5.000
6.000
10.000 3 10.000
0.000
0.000
0.000 3
8.000
20.000 50.000 3 50.000
0.000
...
For clarity I want to do this programmatically. So if I load up 1,000,000 rows with patterns like the above, column B and C automatically populate
Thanks in advance for the excel fu!

p45cal
02-02-2018, 07:27 PM
First, and ultimately more important for you; you have sinned. Either by not reading the rules, or ignoring them.
You've cross-posted this question to other sites without including links to them.
Have a read of http://www.excelguru.ca/content.php?184
You need to add those links here pronto (if your post count is low you might not be able to include links, in which case drop the http part). Most sites like this have the same rules.
Ignoring all this will result in you yourself being ignored.

High horse dismounted, onto the possible solutions:

In the attached there are two offerings, both using the same user-defined function.
Method 1 is to select the cells that you want to analyse (1 column wide, more than 1 row deep, which should only contain numbers or blanks) and run the macro test (or click the button (which calls test)). This will populate the 2 adjacent columns to the right of the selection.
Method 2 is to array-enter the following formula, anywhere on the sheet in a range that has the same number of rows as the data you want get the results from, but 2 columns wide. As an example in the attached I selected cells F1:G30 and array-entered:
=blah(A1:A30)
Array-entering in this case means committing the formula to the sheet when all those cells are selected, by pressing Ctrl+Shift+Enter (not just Enter).
When this formula is in place it will recalculate itself if any of the values change. This is NOT the case with Method 1.

Get those links added.

For those interested the code of the function in the attached is:
Function blah(theRng As Range)
x = theRng
ReDim y(1 To UBound(x), 1 To 2)
For i = 1 To UBound(x)
y(i, 1) = "": y(i, 2) = ""
If x(i, 1) = 0 Then
ZeroCount = ZeroCount + 1
If NonZeroCount > 0 Then
y(i - 1, 1) = NonZeroCount
y(i - 1, 2) = MaxNonZero
NonZeroCount = 0
End If
Else 'not = 0
NonZeroCount = NonZeroCount + 1
If ZeroCount > 0 Then
y(i - 1, 1) = ZeroCount
MaxNonZero = x(i, 1)
ZeroCount = 0
Else
MaxNonZero = Application.Max(MaxNonZero, x(i, 1))
End If
End If
Next i
If NonZeroCount > 0 Then
y(i - 1, 1) = NonZeroCount
y(i - 1, 2) = MaxNonZero
End If
If ZeroCount > 0 Then
y(i - 1, 1) = ZeroCount
End If
blah = y
End Function

p45cal
02-07-2018, 11:02 AM
just a few cross-posts:
https://techcommunity.microsoft.com/t5/Formulas-and-Functions/Performing-count-and-max-commands-on-multiple-groups-of-rows/td-p/151979
https://stackoverflow.com/questions/48590151/performing-count-and-max-commands-on-multiple-groups-of-rows-in-excel
https://www.reddit.com/r/excel/comments/7utu59/performing_count_and_max_commands_on_multiple/?st=jddddd79&sh=e4b0bd86
https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1198940-performing-count-and-max-commands-on-multiple-groups-of-rows
http://if-statement21.blogspot.co.uk/2018/02/performing-count-and-max-commands-on.html