SPREADSHEET PROBLEM

[BERTRAND]

Hi
I need to insert blank rows in between 10,000 races. (Betfair historical data). I can use the Date/Time column for this but otherwise I can't figure it out. Any help would be greatly appreciated.
Bert

[osknows]

If your intending to insert a blank row between each existing row. Then run a for/next loop backwards and insert. Pseudo code would look something like

For i = 1000 to 1 step -1
'insert row
next i


If you intend to add a fixed block of empty rows. Eg say you want to add 500 empty rows between rows 999 and 1000 of 10,000 rows. Do the following

1. number rows 1 - 999: 1 - 999
2. number rows 1000 - 10,000: 1501 - 10,501
3. number blank rows 10,001 - 10,500: 1000 - 1500
4. Sort

[BERTRAND]

I just want to separate all the races from each other. A space between (blank row) each race. Each race has one row per runner in that race. So I will end up with 10,000 blocks of data, each seperated by a blank row above and below.
When the "time of the race" changes in the "time of race" column then that will be the start of the next race, so I need to insert a blank row in between the two races. IF(A2<>A1 then insert blank row.
Hope this is not too confusing!

[osknows]

Try this

Code: Select all

Sub InsertRows()
Application.Calculation = xlCalculationManual
    With Sheet1 'change to required Sheet Codename
   
    For i = 10000 To 2 Step -1
        If .Range("A" & i).Value <> .Range("A" & i - 1).Value Then
            .Rows(i).Insert Shift:=xlDown
        End If
    Next i
   
    End With
Application.Calculation = xlCalculationAutomatic
End Sub


[BERTRAND]

Many thanks Os
This will give me a chance to see if I can use VB with "Open Office". If not I can convert the sheet to Excel. My version of Excel is ancient (XP OFFICE). It's positively archaic compared to Open Office. But once the code has done the job then I can convert back again!

When using your code, do I need to put anything in a module?
Cheers
Bert

[BERTRAND]

Hi Os
Your code did the job.
I promise I won't ask for any more code after this next request. I will be studying VB.
I now have my blocks of data seperated by blank rows. I would like to "RANK" the SP column for each individual block(Race). The results of this function going in a new column unsorted.
Bert

[osknows]

Hi Bert,

The formulae in these 2 threads will contain what you need somewhere. You'll have to adapt them to suit whatever columns you are using

http://www.gruss-software.co.uk/forum/v ... highlight=

http://www.gruss-software.co.uk/forum/v ... highlight=

[BERTRAND]

Thanks Os
Expert.
Taught me a lot figuring it out.
If anybody else want's to use it I would make sure the initial ROW references are the same ie H2,A2,$A$2:

=RANK(H2,INDIRECT(ADDRESS(MATCH(A2,$A$1:$A$65000,0),8,1)&":"&ADDRESS(MATCH(A2,$A$1:$A$65000,0)+COUNTIF($A$1:$A$65000,A2)-1,8,1)),1)
Bert

[osknows]

or to make it easier

=RANK(H2,INDIRECT(ADDRESS(MATCH(A2,$A$1:$A$65000,0),8,1)&":"&ADDRESS(MATCH(A2,$A$1:$A$65000,0)+COUNTIF($A$1:$A$65000,A2)-1,8,1)),1)

A2,$A$1:$A$65000 column A contains the race name groupings

H - column H contains the data to rank

8 - is also column H but expressed as a column number