+ Reply to Thread
Results 1 to 14 of 14

Extract seven pieces of data .

  1. #1
    Forum Contributor
    Join Date
    12-12-2017
    Location
    Franklin , New Zealand
    MS-Off Ver
    2007
    Posts
    168

    Thumbs up Extract seven pieces of data .

    Hello all ,

    I am hoping to get functions or a macro that can give me the 7 x pieces of data I have in cols BK2:BO2

    The main problem I have is col BJ always varies in field size .

    There could be anywhere from 4 horses in a race up to 14 . This is very random .

    The seven results are all taken from below the words Dividends and Times .

    The mile rate I have changed to format of time so I can sort from lowest to highest times .

    The trick I believe is to obtain the results from below the two words mentioned above or at least getting the whole
    line , example BJ7 and BJ11 then splitting these .

    Any ideas would be great .

    Thanks .
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    12-14-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: Extract seven pieces of data .

    You can try bellow array formulas (finish with Ctrl + Shift + Enter):
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If your data is large, maybe you need a macro.

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    14,654

    Re: Extract seven pieces of data .

    try
    Please Login or Register  to view this content.
    Last edited by jindon; 05-17-2019 at 10:53 PM.

  4. #4
    Forum Contributor
    Join Date
    12-12-2017
    Location
    Franklin , New Zealand
    MS-Off Ver
    2007
    Posts
    168

    Re: Extract seven pieces of data .

    Humble apologies to you both .
    I should of said i need results to go with the 1 , 2 , and 3 placed horses only . Basically the second race result would start in cell BK12 .

    Jindon your macro is perfect apart from my major blunder of my above opening line .

    Huuthang_bd your functions are great but col BM is giving me all result of 0.0014 and also my blunder .

    Really happy at this point . Sorry again for my mistake .
    Last edited by I need excel help; 05-17-2019 at 11:09 PM. Reason: Spelling

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    14,654

    Re: Extract seven pieces of data .

    If you want only to 3rd, change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    12-14-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: Extract seven pieces of data .

    Quote Originally Posted by I need excel help View Post
    Huuthang_bd your functions are great but col BM is giving me all result of 0.0014 and also my blunder .
    Are you format column BM as time type (mm:ss.0)?
    Quote Originally Posted by I need excel help View Post
    I should of said i need results to go with the 1 , 2 , and 3 placed horses only . Basically the second race result would start in cell BK12 .
    Retry with below normal formulas

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Don't forget format column BM as time type.
    Last edited by huuthang_bd; 05-18-2019 at 02:25 AM.

  7. #7
    Forum Contributor
    Join Date
    12-12-2017
    Location
    Franklin , New Zealand
    MS-Off Ver
    2007
    Posts
    168

    Re: Extract seven pieces of data .

    Thanks huuthang_bd your second lot of functions are perfect . And yes i didn't format column to time , all good .

    Jindon i have included another version 2 which is what results should be .

    Thanks .
    Attached Files Attached Files
    Last edited by I need excel help; 05-18-2019 at 01:44 AM. Reason: forgot sheet .

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    14,654

    Re: Extract seven pieces of data .

    Change to
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    12-12-2017
    Location
    Franklin , New Zealand
    MS-Off Ver
    2007
    Posts
    168

    Re: Extract seven pieces of data .

    Thanks jindon , nearly there your latest macro is putting data in correct cells .

    All that's missing is the 3rd dividend which i have highlighted in yellow in the version 3 sheet enclosed .

    If possible are you able to change col BM results to match my col BS , as i need it in this type format for sorting later, example 01:59.5
    02:02.4
    01:59.8
    01:58.8

    Not 1-59.5 , 2-02.4 , 1-59.8 , 1-58.8

    I feel i will adding some more reputation soon .

    Thanks .
    Attached Files Attached Files
    Last edited by I need excel help; 05-18-2019 at 03:07 AM. Reason: forgot time question

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    14,654

    Re: Extract seven pieces of data .

    Then just change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    12-12-2017
    Location
    Franklin , New Zealand
    MS-Off Ver
    2007
    Posts
    168

    Re: Extract seven pieces of data .

    Thanks jindon your macro now works perfectly and i had a function to repair mile rate time .

    Thanks also to huuthang_bd for your functions which i can also use .

    Very happy thank you both .

  12. #12
    Forum Contributor
    Join Date
    12-12-2017
    Location
    Franklin , New Zealand
    MS-Off Ver
    2007
    Posts
    168

    Re: Extract seven pieces of data .

    Jindon , i went to test the macro on my larger sheet of 12734 rows and got this line of code in yellow .

    txt = Join(Filter([transpose(if(bj2:bj1000<>"",bj2:bj1000))], False, 0), vbLf)

    Run time error 13 type mismatch .

    Really only guessing here do i maybe have to extend the 1000 in this code .
    Thanks .
    I am grateful for all answers to my questions .

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    14,654

    Re: Extract seven pieces of data .

    2 possibilities.
    1) Error value within the cell
    2) any one cell has characters more than 255 characters.

    Othereise it should work changing by
    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    12-12-2017
    Location
    Franklin , New Zealand
    MS-Off Ver
    2007
    Posts
    168

    Re: Extract seven pieces of data .

    Thanks for that jindon .
    Your right i had 30 x cells >=255 characters . Now fixed .
    Found 6 x empty cells too .

    Also getting issue when two horses dead heat for place 1 , must check 2 and 3 also .

    I changed the deadheats for 1 and 2 and macro all started working . I can fix those couple later .

    I will give this some more time tomorrow and i think im one step away to perfection again . Learning .

    Thank you .

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1