When Ranks change in a Range

[CPerry]

22854

I already have a sub routine which moves numbers down a range once a second from the top, in Exp 1 this would be Cells B5:E5. In example 1 you can see that there are no changes so at the bottom of the range I simply add up all of the red numbers. Same with the yellow, green and blue. The problem comes when these ranks change. (The colours are just for this example, they aren’t required or wanted for the final system as they will slow the code down)

The names at the top (Alpha, Bravo, Charlie and Delta) are returns of formulas in my actual system which appear in rank order. In example 2 you can see that 30 seconds ago up to 10 seconds ago Alpha was first but is now second so at the bottom of the range I sum all of the red numbers but this time they cross 2 columns. In example 3 Alpha drops from 1stto 3rdso the red numbers are across 3 columns.

My problem comes when trying to sum the numbers at the end. In example 2, I don’t just want to sum the entire second column because the bottom 20 numbers don't belong to Alpha, so the ranges get split; do you know what I mean? Can anybody help add to my existing code please to help me when cells in the 2ndrow change order?

Here is how the ranges ‘tick’ at the moment (note the first row is B40:K70 not B5:E5 in the actual system):



Sub Logger ()

Application.EnableEvents = False
Application.Calculation = xlCalculationManual

If Range(“B38”).Value <> Range(“B1”).Value Then Range(“B41:L71”).ClearContents: Range(“B1”).Value
If Range(“B39”).Value = “” Then Range(“B39”).Value = Time () – TimeValue(“00:00:01”)

If Time () >= TimeValue(Range(“B39”).Text) + TmeValue(“00:00:01”) Then
Range(“B41:K71”).Value = Range(“B40:K70”).Value
Range(“B39”).Value = Time ()

End if

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

End Sub

[p45cal]

99% of your question I'm at a loss to understand.
However, colours might come to your rescue even if you don't normally have them. A UDF can sum cells according to their font colour.
In the attached I've stolen a UDF from https://www.extendoffice.com/documents/excel/1418-excel-count-sum-by-font-color.html#a1
I've used it in cells H38:K38 where you can see the values match yours.
In addition, I've also used the UDF in cell D74. There's a button close by that cell which runs a version of your Logger macro. Click it a few times to watch what happens to the value in D74 and what's happening in B40:B71.
The UDF isn't especially efficient and it could be tweaked in many ways to make it faster, or even using its algorithm to place values in those totals cells when they need updating.

So if your existing code could be tweaked to add a font colour when the value is first introduced at the top of each column, it would keep its colour as it stepped down.

What's that : Range("B1").Value doing at the end of the 3rd line of code in your Logger sub?

[CPerry]

22877

I’ve had a thought, if I added ‘Alpha’, ‘Bravo’, ‘Charlie’ and ‘Delta’ to the moving range it would match the coloured numbers.

With this new tweak then (See exp 4 in new attached spreadsheet) is it possible to sum every value 4 to the right of the word ‘Alpha’ and return the total in Cell T37?

If I have weightings on the newer numbers so it goes like this 1/2*(Z5)+1/4*(Z6),1/8*(Z7)… would it be possible to add this to the VBA code too? I’m not sure how to do the vlookup and offset within a range ASWELL as having something multiplied to each term in VBA. Help please?

[p45cal]

In the attached, see T37:W37 and minor changes to T36:W36

[p45cal]

With weighting in attached.

[CPerry]

Thank you so much! Those are both such clear answers that show exactly what I am looking to do with my master code. Much appreciated p45cal! In the future I will use vba instead of formulas in each cells but can manage with this for now. Cheers again.