Results 1 to 12 of 12
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert Footy Fixtures (2002)

    Evening all,

    I'm sure Somebody has done this before, or at least I hope they have. I want to convert the attached footy fixtures data, (copied from a website) into a table.
    Please see attached. Using formula's or code?

    Cheers
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert Footy Fixtures (2002)

    The attached workbook does most of what you want using formulas in the second worksheet. The only thing that goes wrong is the R1 for the Carling Cup.
    Attached Files Attached Files

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert Footy Fixtures (2002)

    Cheers Hans, spot on what I wanted. I want to duplicate this several times over and am unfarmiliar with the offset method. How do the formulas change if the data were to start in A1 on the first sheet?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert Footy Fixtures (2002)

    You'd have to decrease all the row offsets by 5 (since A6 moves to A1).
    The attached version also corrects the Carling Cup, R1 problem.
    Attached Files Attached Files

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert Footy Fixtures (2002)

    As Pop Larkin would haved said, Perfick!!

    I don't suppose that you have 'an idiot's guide to the offset syntax' lying around going spare? [cheekygrin]

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert Footy Fixtures (2002)

    The OFFSET function itself isn't complicated, see for example How to use the OFFSET function in Excel.
    To refer to the correct cell in Sheet1, I used a row offset from the fixed cell A1; the column offset is 0 in all formulas.
    The football matches occur at an interval of 4 rows, while the table in Sheet2 has 1 row for each match.
    So the row offset uses ROW()*4, i.e. 4 times the row number of the cell with the formula. Depending on whether the formula refers to the first, second or third row for a match on Sheet1, the value of ROW()*4 is decreased by 8, 7 or 6.

    Note: I didn't build the formulas in one go, I actually used several auxiliary columns with intermediate formulas to calculate the position of the comma or of " v " etc., then substituted the intermediate formulas in the ones you see now.

  7. #7

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert Footy Fixtures (2002)

    Here is yet another version in which the formulas use a set of defined names (you can view their definitions in Insert | Name | Define).
    This has the following advantages:
    - The formulas become shorter (and hopefully a teeny tiny bit clearer)
    - The formulas are easier to maintain.
    - The file size of the workbook decreases significantly.
    Attached Files Attached Files

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert Footy Fixtures (2002)

    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/clever.gif border=0 alt=clever width=15 height=15> <img src=/S/cool.gif border=0 alt=cool width=15 height=15> <img src=/S/hailpraise.gif border=0 alt=hailpraise width=27 height=22> [smartarse] <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    <img src=/S/thewave.gif border=0 alt=thewave width=225 height=33> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  10. #10
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It's that time of year again, hip hip.......

    Hans kindly assisted me with this last year. I'd like the date to show as "dd mmm yy" but formatting the date does not work.

    How can I show "=TheDate" which refers to "=MID(Row1,Pos1Comma1+2,100)" as "dd mmm yy"? Thanks

    [attachment=84329:FootyFixtures.xls]

    PS: What causes the 'privacy warning' when saving the file?
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The MID function returns a text value, not a date. To convert the result to a date, you can change the definition of TheDate (in Insert | Name | Define... or whatever its equivalent in Excel 2007 is) to

    =DATEVALUE(MID(Row1,Pos1Comma1+2,100))

    The date format will work correctly after that.

    The workbook has been set to remove personal information upon save. Sometimes Excel gets confused about what constitutes personal information and displays a bogus warning.

  12. #12

Posting Permissions

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