Help with Editing Data

H

hoodie07

New Member
Joined
Apr 11, 2014
Messages
4
Hi

Below is my raw data file. Because the text string contains some information that I want to extract to other cells so I can analysis it. Could someone please help me?

1. Market info (column A) contains 4 types of information, Date,time, British standard time BST) and course. I want to extract all of these to a separate cell automaticly.

2. Lay Prices (column D) M5.00@3.25 is two pieces of information, M5.00 is a stake and @3.25 is a price. I want to list these separately, also the stake should read without the M for example 5.00 and the price without @ for example 3.25.

3. Profit/Loss (Column E) I want to count number of consecutive negative numbers and somehow detail the number of times that outcome occurred. For example

consecutive loss

1 occurred 7 times
2 occurred 4 times
3 occurred 3 times
4 occurred 2 times
5 occurred 0 times


If someone would kindly give me a hand with this I would be over the moon!


A B C D E
[TABLE="width: 616"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Market Info[/TD]
[TD]Selections[/TD]
[TD]Winner[/TD]
[TD]Lay Prices[/TD]
[TD]Profit/Loss[/TD]
[/TR]
[TR]
[TD]08 April 18:39 BST Sheff[/TD]
[TD]5. Meadowcroftbirch[/TD]
[TD]Swift Aideen[/TD]
[TD]M5.00@3.25[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]08 April 18:44 BST Newc[/TD]
[TD]4. Target Blaine[/TD]
[TD]Fagans Hi Ace[/TD]
[TD]M14.00@3.45[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]08 April 18:49 BST Kinsl[/TD]
[TD]4. Cals Opal[/TD]
[TD]Riverdale Bound[/TD]
[TD]M5.00@3.25[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]08 April 18:56 BST Sheff[/TD]
[TD]3. Tansys Termo[/TD]
[TD]Tansys Termo[/TD]
[TD]M5.00@2.28[/TD]
[TD="align: right"]-6.4[/TD]
[/TR]
[TR]
[TD]08 April 19:01 BST Newc[/TD]
[TD]5. Get There Clair[/TD]
[TD]Knoppogue Pearl[/TD]
[TD]M5.00@3.65[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]08 April 19:06 BST Kinsl[/TD]
[TD]5. Montana Express[/TD]
[TD]Boozed Sandy[/TD]
[TD]M8.20@4.90[/TD]
[TD="align: right"]8.2[/TD]
[/TR]
[TR]
[TD]08 April 19:11 BST Sheff[/TD]
[TD]2. Russanda Ronda[/TD]
[TD]Russanda Brax[/TD]
[TD]M8.20@3.50[/TD]
[TD="align: right"]8.2[/TD]
[/TR]
[TR]
[TD]08 April 19:16 BST Newc[/TD]
[TD]5. I See You[/TD]
[TD]Blackhouse Ben[/TD]
[TD]M8.20@3.75[/TD]
[TD="align: right"]8.2[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 616"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]08 April 19:22 BST Kinsl[/TD]
[TD]3. Leamaneigh Kevin[/TD]
[TD]Daisyfield Hope[/TD]
[TD]M5.00@3.60[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]08 April 19:27 BST Sheff[/TD]
[TD][/TD]
[TD]Final Hawk[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08 April 19:32 BST Newc[/TD]
[TD]6. Sarasota Jewel[/TD]
[TD]Mollys Bonnie[/TD]
[TD]M5.00@3.25[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]08 April 19:37 BST Kinsl[/TD]
[TD]3. Barbican Flame[/TD]
[TD]Salacresforlorna[/TD]
[TD]M5.00@2.98[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]08 April 19:43 BST Sheff[/TD]
[TD][/TD]
[TD]Ballymac Bobbyjo[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08 April 19:48 BST Newc[/TD]
[TD]2. Mollys Jesse[/TD]
[TD]Elwick Scoop[/TD]
[TD]M5.00@4.40[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]08 April 19:53 BST Kinsl[/TD]
[TD]3. Mayscarickfergus[/TD]
[TD]Mayscarickfergus[/TD]
[TD]M5.00@4.40[/TD]
[TD="align: right"]-17[/TD]
[/TR]
[TR]
[TD]08 April 19:58 BST Sheff[/TD]
[TD]5. Blue Bees Bolt[/TD]
[TD]Expert Jess[/TD]
[TD]M13.50@2.62[/TD]
[TD="align: right"]13.5[/TD]
[/TR]
[TR]
[TD]08 April 20:04 BST Newc[/TD]
[TD]3. Woodys Arizona[/TD]
[TD]Highclere Drift[/TD]
[TD]M13.50@3.90[/TD]
[TD="align: right"]13.5[/TD]
[/TR]
[TR]
[TD]08 April 20:09 BST Kinsl[/TD]
[TD]3. Deerview Blaze[/TD]
[TD]Catunda Usain[/TD]
[TD]M13.50@4.80[/TD]
[TD="align: right"]13.5[/TD]
[/TR]
[TR]
[TD]08 April 20:14 BST Sheff[/TD]
[TD][/TD]
[TD]Panc The Prank[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08 April 20:19 BST Newc[/TD]
[TD]5. Peggys Gift[/TD]
[TD]Bower Stan[/TD]
[TD]M5.00@4.40[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]08 April 20:24 BST Kinsl[/TD]
[TD]4. Big Tom[/TD]
[TD]Adamant Armina[/TD]
[TD]M5.00@3.90[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]08 April 20:31 BST Sheff[/TD]
[TD]2. High Approach[/TD]
[TD]Johnny Kwango[/TD]
[TD]M5.00@4.10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]08 April 20:36 BST Newc[/TD]
[TD]6. Blackhouse Linty[/TD]
[TD]Blackhouse Linty[/TD]
[TD]M5.00@3.80[/TD]
[TD="align: right"]-14[/TD]
[/TR]
[TR]
[TD]08 April 20:41 BST Kinsl[/TD]
[TD]6. Vita Nova[/TD]
[TD]Vita Nova[/TD]
[TD]M5.00@4.00[/TD]
[TD="align: right"]-15[/TD]
[/TR]
[TR]
[TD]08 April 20:47 BST Sheff[/TD]
[TD][/TD]
[TD]United Leeds[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08 April 20:52 BST Newc[/TD]
[TD]6. Piercestown Lady[/TD]
[TD]Piercestown Lady[/TD]
[TD]M19.50@3.20[/TD]
[TD="align: right"]-42.9[/TD]
[/TR]
[TR]
[TD]08 April 20:57 BST Kinsl[/TD]
[TD]1. Ballymac Stef[/TD]
[TD]Monemore Joker[/TD]
[TD]M19.50@5.70[/TD]
[TD="align: right"]19.5[/TD]
[/TR]
[TR]
[TD]08 April 21:02 BST Sheff[/TD]
[TD]1. Bit View Royal[/TD]
[TD]Bit View Royal[/TD]
[TD]M40.95@2.20[/TD]
[TD="align: right"]-49.14[/TD]
[/TR]
[TR]
[TD]08 April 21:07 BST Newc[/TD]
[TD]4. Pretty Honey[/TD]
[TD]Route Sixty Six[/TD]
[TD]M5.00@3.35[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]08 April 21:12 BST Kinsl[/TD]
[TD]2. Cill Dubh Marble[/TD]
[TD]What About Obama[/TD]
[TD]M5.00@3.45[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]08 April 21:17 BST Sheff[/TD]
[TD]5. Proper Fancy[/TD]
[TD]Popular Ronnoco[/TD]
[TD]M5.00@2.50[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]08 April 21:22 BST Newc[/TD]
[TD]3. Tullymurry Pedro[/TD]
[TD]Ludworth Blake[/TD]
[TD]M5.00@3.25[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 11:03 BST Kinsl[/TD]
[TD]6. Swift Coke[/TD]
[TD]Blown It[/TD]
[TD]M5.00@2.78[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 11:11 BST Hove[/TD]
[TD]3. Newtown Skipdots[/TD]
[TD]Tyrur Blanche[/TD]
[TD]M5.00@2.70[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 11:19 BST Kinsl[/TD]
[TD]6. Vigorous Gil[/TD]
[TD]Kincora Ace[/TD]
[TD]M5.00@3.80[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 11:28 BST Hove[/TD]
[TD]4. Torbal Spirit[/TD]
[TD]Clairkeith Sylva[/TD]
[TD]M5.00@3.00[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 11:34 BST Kinsl[/TD]
[TD]3. Boozed Aroma[/TD]
[TD]Bumblebee Julie[/TD]
[TD]M5.00@3.90[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 11:42 BST Hove[/TD]
[TD]2. Islas Giovanni[/TD]
[TD]Freya Said So[/TD]
[TD]M5.00@2.34[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 11:48 BST Kinsl[/TD]
[TD]3. Monbeg Kev[/TD]
[TD]Swithin Lady[/TD]
[TD]M5.00@4.00[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 11:57 BST Hove[/TD]
[TD]3. Warnham Shadow[/TD]
[TD]Oonah[/TD]
[TD]M5.00@2.48[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 12:04 BST Kinsl[/TD]
[TD]5. Ballycosh Jackie[/TD]
[TD]Big Tom[/TD]
[TD]M5.00@3.50[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 12:12 BST Hove[/TD]
[TD]5. Droopys Tamera[/TD]
[TD]Big Black Hawk[/TD]
[TD]M5.00@3.45[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 12:18 BST Kinsl[/TD]
[TD]3. Albans Blue Boy[/TD]
[TD]Born In Kerry[/TD]
[TD]M5.00@4.00[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 12:27 BST Hove[/TD]
[TD]4. Rathglass Charm[/TD]
[TD]Hollyoak Time[/TD]
[TD]M5.00@3.20[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 12:32 BST Kinsl[/TD]
[TD]3. Keady Dan[/TD]
[TD]Lefanta Music[/TD]
[TD]M5.00@3.45[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 12:42 BST Hove[/TD]
[TD]4. Questhouse Aura[/TD]
[TD]Stanetta[/TD]
[TD]M5.00@2.56[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 12:47 BST Kinsl[/TD]
[TD]3. Eyeoweuone[/TD]
[TD]Eyeoweuone[/TD]
[TD]M5.00@2.76[/TD]
[TD="align: right"]-8.8[/TD]
[/TR]
[TR]
[TD]11 April 12:58 BST Hove[/TD]
[TD]1. Roses Posh[/TD]
[TD]Westmead Ellie[/TD]
[TD]M5.00@3.10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 13:04 BST Kinsl[/TD]
[TD]3. Ayza Pete[/TD]
[TD]Ayza Pete[/TD]
[TD]M5.00@3.35[/TD]
[TD="align: right"]-11.75[/TD]
[/TR]
[TR]
[TD]11 April 13:12 BST Hove[/TD]
[TD]1. Express Hero[/TD]
[TD]Express Hero[/TD]
[TD]M5.00@3.05[/TD]
[TD="align: right"]-10.25[/TD]
[/TR]
[TR]
[TD]11 April 13:19 BST Kinsl[/TD]
[TD]6. Highview Smasher[/TD]
[TD]Leamaneigh Kevin[/TD]
[TD]M5.00@4.10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 13:27 BST Hove[/TD]
[TD]2. Designed Black[/TD]
[TD]Designed Black[/TD]
[TD]M5.00@2.68[/TD]
[TD="align: right"]-8.4[/TD]
[/TR]
[TR]
[TD]11 April 13:33 BST Kinsl[/TD]
[TD]5. Catunda Usain[/TD]
[TD]Catunda Usain[/TD]
[TD]M5.00@3.80[/TD]
[TD="align: right"]-14[/TD]
[/TR]
[TR]
[TD]11 April 13:44 BST Hove[/TD]
[TD]6. On A Cloud[/TD]
[TD]On A Cloud[/TD]
[TD]M5.00@3.70[/TD]
[TD="align: right"]-13.5[/TD]
[/TR]
[TR]
[TD]11 April 13:51 BST Kinsl[/TD]
[TD]6. Prince Of Swords[/TD]
[TD]Prince Of Swords[/TD]
[TD]M5.00@3.85[/TD]
[TD="align: right"]-14.25[/TD]
[/TR]
[TR]
[TD]11 April 13:58 BST Hove[/TD]
[TD]3. Our Saving Grace[/TD]
[TD]Janes Blaze[/TD]
[TD]M5.00@3.55[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 14:08 BST Swin[/TD]
[TD]2. Athboy Lucy[/TD]
[TD]Maigueside Sally[/TD]
[TD]M5.00@2.84[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 14:18 BST Monm[/TD]
[TD]5. Aero Bellagio[/TD]
[TD]Beatties Teddy[/TD]
[TD]M5.00@4.60[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 14:27 BST Swin[/TD]
[TD]1. Fermoy Lets Go[/TD]
[TD]Castlehill Spitz[/TD]
[TD]M5.00@5.00[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 14:37 BST Monm[/TD]
[TD]6. Chilly Molly[/TD]
[TD]Kenz White Night[/TD]
[TD]M5.00@3.45[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 14:47 BST Swin[/TD]
[TD]2. Valais Late[/TD]
[TD]Swift Anita[/TD]
[TD]M5.00@4.10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 14:57 BST Monm[/TD]
[TD]6. Base Rate Cap[/TD]
[TD]Rathclough Hawk[/TD]
[TD]M5.00@2.96[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 15:07 BST Swin[/TD]
[TD]3. Scaffs Misty[/TD]
[TD]Pookey[/TD]
[TD]M5.00@4.30[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 15:17 BST Monm[/TD]
[TD]3. Fallen Empire[/TD]
[TD]Fallen Empire[/TD]
[TD]M5.00@4.60[/TD]
[TD="align: right"]-18[/TD]
[/TR]
[TR]
[TD]11 April 15:28 BST Swin[/TD]
[TD]1. Midian Alfie[/TD]
[TD]Midian Alfie[/TD]
[TD]M5.00@3.50[/TD]
[TD="align: right"]-12.5[/TD]
[/TR]
[TR]
[TD]11 April 15:38 BST Monm[/TD]
[TD]2. Farloe Bashful[/TD]
[TD]Olafthelofty[/TD]
[TD]M5.00@3.25[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 15:48 BST Swin[/TD]
[TD]3. Hero Jack[/TD]
[TD]Reidys Scolari[/TD]
[TD]M5.00@3.50[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 15:58 BST Monm[/TD]
[TD]4. Primo Lance[/TD]
[TD]Pipwillow Magic[/TD]
[TD]M5.00@5.00[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]11 April 16:08 BST Swin[/TD]
[TD]5. Our Special One[/TD]
[TD]Madame Pompadour[/TD]
[TD]M5.00@4.50[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
1. I would use the built in Text to Columns function using the fixed width option.

2.

Excel 2010
A
1M5.00@4.50
2
35.00
44.50
Sheet1
Cell Formulas
RangeFormula
A3=MID(A1,2,(FIND("@",A1,1)-1)-1)
A4=RIGHT(A1,LEN(A1)-FIND("@",A1,1))


3. I'll need to think.
 
Upvote 0
For the third part these array formulas I found at Count Consecutive Numbers seem to give the right answer for the data set. I didn't include all 70 Rows

Excel Workbook
ABCDEFGH
1Market InfoSelectionsWinnerLay PricesProfit/Loss*Negatives in a RowCount
208 April 18:39 BST Sheff5. MeadowcroftbirchSwift AideenM5.00@3.255*15
308 April 18:44 BST Newc4. Target BlaineFagans Hi AceM14.00@3.4514*23
408 April 18:49 BST Kinsl4. Cals OpalRiverdale BoundM5.00@3.255*30
508 April 18:56 BST Sheff3. Tansys TermoTansys TermoM5.00@2.28-6.4*41
608 April 19:01 BST Newc5. Get There ClairKnoppogue PearlM5.00@3.655***
708 April 19:06 BST Kinsl5. Montana ExpressBoozed SandyM8.20@4.908.2***
Sheet1
 
Upvote 0
This was a challenge. Do not run this on you original until you have tested it on a copy.
Code: 
Sub reorg()
Dim sh As Worksheet, lr As Long, rng As Range, c As Range, dat As Variant
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A2:A" & lr)
sh.Columns("B:D").Insert
sh.Columns("H").Insert
    For r = 1 To 5
        sh.Cells(r + 1, 10) = "Times " & r & " occurred"
    Next
    For Each c In rng
        dat = Split(c.Value, " ")
        c = dat(0) & " " & dat(1)
        For i = 2 To 4
            sh.Cells(c.Row, i) = dat(i)
        Next
        Set v = sh.Range("G" & c.Row)
        Range("H" & c.Row) = Right(v.Value, Len(v) - InStr(v, "@"))
        v = Mid(v.Value, 2, 4)
        If Range("I" & c.Row).Value < 0 Then
            Select Case Left(Range("E" & c.Row).Value, 1)
                Case Is = 1
                    Range("K2") = Range("K2").Value + 1
                Case Is = 2
                    Range("K3") = Range("K3").Value + 1
                Case Is = 3
                    Range("K4") = Range("K4").Value + 1
                Case Is = 4
                    Range("K5") = Range("K5").Value + 1
                Case Is = 5
                    Range("K6") = Range("K6").Value + 1
            End Select
        End If
    Next
End Sub
 
Upvote 0
This will fix the column that did not break out the lay prices.
Code: 
Sub reorg()
Dim sh As Worksheet, lr As Long, rng As Range, c As Range, dat As Variant
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A2:A" & lr)
sh.Columns("B:D").Insert
sh.Columns("H").Insert
    For r = 1 To 5
        sh.Cells(r + 1, 10) = "Times " & r & " occurred"
    Next
    For Each c In rng
        dat = Split(c.Value, " ")
        c = dat(0) & " " & dat(1)
        For i = 2 To 4
            sh.Cells(c.Row, i) = dat(i)
        Next
        Set v = sh.Range("G" & c.Row)
        Range("H" & c.Row) = Right(v.Value, Len(v) - InStr(v, "@"))
        Range("G" & c.Row) = Mid(v.Value, 2, 4)
        Range("G" & c.Row).NumberFormat = "#0.00"
        If Range("I" & c.Row).Value < 0 Then
            Select Case Left(Range("E" & c.Row).Value, 1)
                Case Is = 1
                    Range("K2") = Range("K2").Value + 1
                Case Is = 2
                    Range("K3") = Range("K3").Value + 1
                Case Is = 3
                    Range("K4") = Range("K4").Value + 1
                Case Is = 4
                    Range("K5") = Range("K5").Value + 1
                Case Is = 5
                    Range("K6") = Range("K6").Value + 1
            End Select
        End If
    Next
End Sub
 
Upvote 0
WOW!! Overwhelmed with this fantastic response from both of you!! Thankyou so much, I will need a little time to digest and try and implement. Thanks Again
 
Upvote 0
Hi, This looks great! However, I'm not sure how I can implement this, would you be kind enough to give steps on implementation?

Thanks
 
Upvote 0
hoodie07 said:
Hi, This looks great! However, I'm not sure how I can implement this, would you be kind enough to give steps on implementation?

Thanks
Click to expand...
If you are referring to the VBA code, then copy the code to the standard code module1 in a workbook that has been saved as a macro enabled workbook. To open the code module, press Alt + F11. If the large pane is dark, click Insert>module, then paste the code into that pane and close the VB editor. To run the code, with the Excel sheet visible and active, press Alt + F8, click the macro name, then click run.

Be sure you have made a back up copy of your file first.
 
Upvote 0
thatoneguy650 said:
For the third part these array formulas I found at Count Consecutive Numbers seem to give the right answer for the data set. I didn't include all 70 Rows

Sheet1

*ABCDEFGH
Market InfoSelectionsWinnerLay PricesProfit/Loss*Negatives in a RowCount
08 April 18:39 BST Sheff5. MeadowcroftbirchSwift AideenM5.00@3.25*
08 April 18:44 BST Newc4. Target BlaineFagans Hi AceM14.00@3.45*
08 April 18:49 BST Kinsl4. Cals OpalRiverdale BoundM5.00@3.25*
08 April 18:56 BST Sheff3. Tansys TermoTansys TermoM5.00@2.28*
08 April 19:01 BST Newc5. Get There ClairKnoppogue PearlM5.00@3.65***
08 April 19:06 BST Kinsl5. Montana ExpressBoozed SandyM8.20@4.90***

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:160px;"><col style="width:140px;"><col style="width:138px;"><col style="width:89px;"><col style="width:74px;"><col style="width:64px;"><col style="width:64px;"><col style="width:126px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]14[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]-6.4[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]8.2[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
H2{=INDEX(FREQUENCY(FREQUENCY(IF($E$2:$E$70<0,ROW($E$2:$E$70)),IF($E$2:$E$70>=0,ROW($E$2:$E$70))),{0,1}),2)}
H3{=INDEX(FREQUENCY(FREQUENCY(IF($E$2:$E$70<0,ROW($E$2:$E$70)),IF($E$2:$E$70>=0,ROW($E$2:$E$70))),{1,2}),2)}
H4{=INDEX(FREQUENCY(FREQUENCY(IF($E$2:$E$70<0,ROW($E$2:$E$70)),IF($E$2:$E$70>=0,ROW($E$2:$E$70))),{2,3}),2)}
H5{=INDEX(FREQUENCY(FREQUENCY(IF($E$2:$E$70<0,ROW($E$2:$E$70)),IF($E$2:$E$70>=0,ROW($E$2:$E$70))),{3,4}),2)}

<tbody>
</tbody>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Hi, I have tried to copy the above formula into my worksheet, however I am getting the following, is there something I'm doing wrong?

[TABLE="width: 1152"]
<colgroup><col width="64" span="18" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Market Info[/TD]
[TD="class: xl66, width: 64"]Selections[/TD]
[TD="class: xl66, width: 64"]Winner[/TD]
[TD="class: xl66, width: 64"]Lay Prices[/TD]
[TD="class: xl66, width: 64"]Profit/Loss[/TD]
[TD="class: xl66, width: 64"]*[/TD]
[TD="class: xl66, width: 64"]Negatives in a Row[/TD]
[TD="class: xl66, width: 64"]Count[/TD]
[TD="class: xl67, width: 64"] [/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]08 April 18:39 BST Sheff[/TD]
[TD="class: xl63, width: 64"]5. Meadowcroftbirch[/TD]
[TD="class: xl63, width: 64"]Swift Aideen[/TD]
[TD="class: xl63, width: 64"]M5.00@3.25[/TD]
[TD="class: xl64, width: 64"]5[/TD]
[TD="class: xl63, width: 64"]*[/TD]
[TD="class: xl64, width: 64"]1[/TD]
[TD="class: xl73, colspan: 11"]{=INDEX(FREQUENCY(FREQUENCY(IF($E$2:$E$70<0,ROW($E$2:$E$70)),IF($E$2:$E$70>=0,ROW($E$2:$E$70))),{0,1}),2)}[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]08 April 18:44 BST Newc[/TD]
[TD="class: xl63, width: 64"]4. Target Blaine[/TD]
[TD="class: xl63, width: 64"]Fagans Hi Ace[/TD]
[TD="class: xl63, width: 64"]M14.00@3.45[/TD]
[TD="class: xl64, width: 64"]14[/TD]
[TD="class: xl63, width: 64"]*[/TD]
[TD="class: xl64, width: 64"]2[/TD]
[TD="class: xl68, width: 64"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]08 April 18:49 BST Kinsl[/TD]
[TD="class: xl63, width: 64"]4. Cals Opal[/TD]
[TD="class: xl63, width: 64"]Riverdale Bound[/TD]
[TD="class: xl63, width: 64"]M5.00@3.25[/TD]
[TD="class: xl64, width: 64"]5[/TD]
[TD="class: xl63, width: 64"]*[/TD]
[TD="class: xl64, width: 64"]3[/TD]
[TD="class: xl68, width: 64"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]08 April 18:56 BST Sheff[/TD]
[TD="class: xl63, width: 64"]3. Tansys Termo[/TD]
[TD="class: xl63, width: 64"]Tansys Termo[/TD]
[TD="class: xl63, width: 64"]M5.00@2.28[/TD]
[TD="class: xl64, width: 64"]-6.4[/TD]
[TD="class: xl63, width: 64"]*[/TD]
[TD="class: xl64, width: 64"]4[/TD]
[TD="class: xl68, width: 64"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]08 April 19:01 BST Newc[/TD]
[TD="class: xl63, width: 64"]5. Get There Clair[/TD]
[TD="class: xl63, width: 64"]Knoppogue Pearl[/TD]
[TD="class: xl63, width: 64"]M5.00@3.65[/TD]
[TD="class: xl64, width: 64"]5[/TD]
[TD="class: xl63, width: 64"]*[/TD]
[TD="class: xl63, width: 64"]*[/TD]
[TD="class: xl69, width: 64"]*[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl70, width: 64"]08 April 19:06 BST Kinsl[/TD]
[TD="class: xl70, width: 64"]5. Montana Express[/TD]
[TD="class: xl70, width: 64"]Boozed Sandy[/TD]
[TD="class: xl70, width: 64"]M8.20@4.90[/TD]
[TD="class: xl71, width: 64"]8.2[/TD]
[TD="class: xl70, width: 64"]*[/TD]
[TD="class: xl70, width: 64"]*[/TD]
[TD="class: xl72, width: 64"]*[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Track movement stock in listbox on userform by copy amounts from previous row to next row
Replies
8
Views
103
abdo meghari
A
D
  • Solved
Add content to empty cells in column - macro
Replies
6
Views
84
AFZAL SOHAIL
A
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
2K
phiero21
P
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
400
dshtesy
D
E
  • Question Question
Pulling together data from multiple MS Forms spreadsheets and displaying into a single league table
Replies
6
Views
339
excelnewbie999
E

Forum statistics

Threads
1,224,613
Messages
6,179,896
Members
452,948
Latest member
Dupuhini

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top