Re: Rank values with complex rules?
Carim, like a cryptologist working on the enigma code, you have validated well. Good work! Now to make it all happen, at least you have a good grasp of the intent. I will be going to bed soon in my time zone, so apologies if I don't return any posts till the morning.
Re: Rank values with complex rules?
No matter how great the talent, some things just take time. :drum:
Re: Rank values with complex rules?
Yes, everything is variable, races, horses, values
Re: Rank values with complex rules?
:rock:Go, you good thing!
Re: Rank values with complex rules?
OK, I'm working on this problem myself and have progressed a little more. I added a Helper column that assigns each value a count# 1,2,3... and a sumproduct function to rank. This works fine until a horses values run out compared to other horses. The formula then ranks as if that horse wasn't in the race. e.g. In Race1 the minimum number of values is two for Horse4, therefore when the sumproduct formula starts to look for value number 3's+ it leaves Horse4 out of the calculation. This worsens the further down the count of available values per horse goes.
Problems remaining:
1/ Absolute references needed for sumproduct for each race...time consuming to enter. Need a code to recognise the change in race and range of rows.
2/ An 'IF' condition needs to tell sumproduct that when you run out of value counts the last previous reference applies.
Re: Rank values with complex rules?
Thanks Carim for the referral. My issue is another order higher in difficulty as I'm comparing ranks with different variable length subsets and the consecutive values run out for some subsets. I know one person on here that is a Master craftsman, just got to entice him/her. In fact I'll dust off the virtual Excel Cup I issued several years ago to a forum braniac for help above and beyond and re engrave it.
In col A subsets of 'Races" are shown. In col B subsets of Horses within each race. The number of which varies from 3 to 24. In col C values left behind in time order after removing duplicates. The most recent value is at the top of each Horses subset. In col D shows the last/most recent value.
In col E the values in col C are ranked against each other, but this is where things get complex. Take cell E2. The value at C2 is ranked against itself, C5,C10,C15,C17,C23. This rank formula is repeated progressing down col E and ranking against the next consecutive value for each horse. However because the number of values for each horse varies, you run out of consecutive values, and in this case the formula uses the last value for each horse.
e.g. Take cell E4. It's ranked against itself, E7, E12,E16* (last value for horse4),E19,E25
The rank formulas are resident in col E, so you can follow what's going on. I've shown 3 races in this example, but in reality there are many tens of thousands of races, horses and values. I should've ranked as I recorded and will in the future. It's a matter of backtracking over old data to extract more information.
From my experience solving this issue with code or formula is nigh on impossible, and anyone that can solve this would be elevated to "Genuis" status IMHO!
Re: Stacking repeating grouped columns
Wow!! I'm just blown away by your mastery KjBox. You make it look so easy. Magnificent work. The auto "GIVE REPUTATION" won't allow me to award anymore stars to you, otherwise I'd give you 5 stars. Thanks for your help too nyatiaju.
:congrats:
Re: Stacking repeating grouped columns
nyatiaju, not working out! Usually happens when you upload a mini version of the actual work version. There are differences which might've thrown your code out? Here's the big picture spreadsheet. You must be close, a little further?
Thanks
From the attachment:
The set out on Sheet1 down to row 9 is the way I record data. At the end of each day I need to copy and paste cols G:M; T:Z;AG:AM.........GQ:GW (haven't shown the full extent on the attachment) one on top of the other using the same cols A:D as an index (at left) on another sheet2. The length of the columns are always the same, but vary in total length each day. Appreciate some help with a macro. Color formatting isn't a requirement! Thanks
Re: Insert and move down fail?
A true legend! Not enough to do it well, just have to do it better. The sort of person we need in this world!
Re: Insert and move down fail?
Thanks KjBox, that code is masterful! You're in another stratosphere of advanced coders. It's a tad slow, takes about 3 secs to paste, but that's liveable.
Cheers
Re: Insert and move down fail?
Sorry, I've sent another file, as the last one is missing modules.
Re: Insert and move down fail?
Thanks for your reply.
The workbook is connected to a 3rd party application that updates values. There are two sheets, Sheet1 and PLACE. They are updated several times a second and sheet DATA responds to them. For your purposes, you won't be able to see it run in auto mode unfortunately. If you look at sheet DATA, you will see that when I ticked 'tick to record' box, row 5 cells were populated with values after 5 seconds. These cell ranges were offset pasted to row 7 ranges, which then would push the previous row 7 contents down to row 8 and so-on and so-forth. Unfortunately it timed out with the error on doing that, and remained with values in row 7 only. I'm dubious as to your success in analysing the problem without the live feed. Good luck, and thanks for your interest.
I had the first code working fine, and amended it to the second. Now I get an "Insert method of Range class failed" message and the line "Selection insert..." is highlighted by the Debugger. Any solutions please? Basically every 5 seconds row 5 ranges are populated with fresh values. They get offset pasted to row 7 and previous pastes get shunted down by one row. The only other difference between the two versions is row 7 is now the next row after the header row of Tables. I did this so that formulas above become dynamic as the table expands.
Sub Record_data()
Sheets("Data").Select
Sheets("Data").Rows("5:5").Select
Sheets("Data").Range("B5").Activate
Selection.Copy
Sheets("Data").Rows("7:7").Select
Sheets("Data").Range("B7").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Data").Rows("7:7").Select
Sheets("Data").Range("B7").Activate
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End SubSub Record_tabledata()
Sheets("Data").Select
Dim Area
For Each Area In Range("C5:M5,S5:Y5,AE5:AK5,AQ5:AW5,BC5:BI5,BO5:BU5,CA5:CG5,CM5:CS5,CY5:DE5,DK5:DQ5,DW5:EC5,EI5:EO5,EU5:FA5,FG5:FM5,FS5:FY5,GE5:GK5,GQ5:GW5").Areas
Area.Offset(2, 0).Value = Area.Value
Next
Sheets("Data").Rows("7:7").Select
Sheets("Data").Range("B7").Activate
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End SubThe code below is in a module and called from worksheet code. It delays a copy and paste from Sheet1 to Sheet2 by 4 seconds when triggered. However during that 4 seconds, the macro prevents data being updated on Sheet1 from an external feed, so when the copy is pasted its 4 seconds old data. Is their a way, once the Sub Copy_Race is called, to delay the execution by 4 seconds but not affect the data updates and paste the current (after 4 secs) data?
I knew Application.Now and Sleep froze the data, but was assured this method didn't?
Cheers
Sub Copy_Race()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Timeout (4)
Set copySheet = Worksheets("Sheet1")
Set pasteSheet = Worksheets("Sheet2")
copySheet.Range("A1:AS21").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Sub Timeout(how_many_seconds As Double)
Starting_Time = Timer
Do
DoEvents
Loop Until (Timer - Starting_Time) >= how_many_seconds
End SubRe: Loop thru every combination of variables, calculate and paste
Yes you're right, these two will provide the answers for each individual combination:
=SUMPRODUCT((H2:H2000="1-1-2-2-W")*(G2:G2000))-SUMPRODUCT((H2:H2000="1-1-2-2-L")*(G2:G2000))
=SUMPRODUCT((H2:H2000="1-1-2-2-L")*(B2:B2000>MAX((H2:H2000="1-1-2-2-W")*(B2:B2000)))*(G2:G2000))
The only thing needed now is a loop to work out & insert all the possible combinations of the array into those two formula? Don't forget in the real life situation there is 8x8x8x8 (4096) possible combinations. Can you suggest a code to implement that?
Cheers
Re: Loop thru every combination of variables, calculate and paste
Well, if you call yourself two different websites, is it any wonder? I never knew that Ozgrid was Excel Help Forum, as I've got different passwords. VERY CONFUSING!! Is that my fault....methinks not?
Re: Loop thru every combination of variables, calculate and paste
Confused here, has GCExcel actually posted a reply?. Can see the blue shirt logo, but nothing else?
I have 4 cols C, V, S, N. Each column has two values, 1 or 2. The combination of those 4 column values makes 16 distinct records i.e. 1-1-1-1, 1-1-1-2, 1-1-2-1....2-2-2-2. Each record has a 5th value on the end, a "W" or "L", making 32 combinations. The list of data is added to each day. As required I need a vba code to trigger the auto filter to find all records for each combination and pair the "W" &"L" records side by side. i.e. say 1-1-2-2-W alongside 1-1-2-2-L. It then performs 3 calculations on the two subsets, and the data dragged with it from the data sheet, namely "P" and "$".
Once the 3 calculations are performed it will paste these to a new worksheet, then loop back to find the next consecutive combination, calculate, paste after the last paste and repeat till the cycle is complete. As the data is added to each day it will include the range extension each time it is run. The real life scenario for this comprises 8 variables for each column (1...8) and the number of rows is in the 200,000 plus category. That makes for 8000plus combinations if you include the "W" & "L" variants. You might like to incorporate the 1 thru 8 variables per column in your code or just use the 1 thru 2 as per the attached example, and I'll modify.
Cheers for reading this post.