+ Reply to Thread
Results 1 to 39 of 39

how to get the chess notations in columns?

  1. #1
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    how to get the chess notations in columns?

    1.e4 c5 2.Nf3 d6 3.d4 cxd4 4.Nxd4 Nf6 5.Nc3 a6 6.f3 e5 7.Nb3
    Be6 8.Be3 Nbd7 9.Qd2 b5 10.O-O-O Nb6 11.Qf2 Nc4 12.Bxc4 bxc4
    13.Na5 Qd7 14.Rd2 Be7 15.Rhd1 Rb8 16.Bc5 Qc7 17.Rxd6 Qxa5
    18.Rxe6 fxe6 19.Bxe7 Rb7 20.Bd6 Nd7 21.Qh4 Qd8 22.Qh5+ g6
    23.Qh6 Qf6 24.Ne2 Kf7 25.h4 g5 26.hxg5 Qxh6 27.gxh6 Rg8 28.g4
    Rg6 29.Rh1 Rb6 30.Ba3 Rf6 31.Rh3 Kg6 32.Kd2 Rf7 33.Ke3 Nf6
    34.Nc3 Rd7 35.Rh1 Rc6 36.Na4 Rb7 37.Nc3 Rb8 38.Nd1 Ng8 39.Rh5
    Nxh6 40.Rxe5 Nf7 41.Rh5 Rb5 42.Rh1 e5 43.Nc3 Rb7 44.Nd5 Re6
    45.Bb4 Kg7 46.Rh2 Ng5 47.Bc3 Kg8 48.Rf2 Rf7 49.Rf1 Re8 50.Ke2
    Ref8 51.Bxe5 Nxe4 52.Ke3 Nc5 53.f4 Re8 54.Kd4 Nd7 55.Re1 Re6
    56.Re2 Nxe5 57.fxe5 Rg7 58.Nf6+ Kf7 59.Kxc4 Rg5 60.Kd4 Rb6
    61.c4 Ke6 62.b3 Rb8 63.Re4 h6 64.Nd5 Rbg8 65.Nf4+ Ke7 66.e6
    1-0
    I want to get each move into a column.
    See the attachment
    .
    the option> text to columns- cannot be applied due to absence of any particular character after each move. how can i accomplish the task?

    anybody please help me with a formula /macro to get a @ before each move number .
    after applying the macro it should look like
    @1.e4 c5 @2.Nf3 d6 @3.d4 cxd4 @4.Nxd4 Nf6
    My idea is after getting the spl character before the move number i may be able to adopt the text to columns options in excel so that each move .both white's and black's ,will come into a column.
    if all these processes can be included in a macro then i would like to have five columns in a row. see the attachment please.

    see the sheet named original data in which i have shown the data imported from the pgn file. from this sheet i have to get the sheet named output which is shown as sheet number two.
    i want each move both black's and white's come into a single column with three spaces in between.
    i want five moves in each rows.
    i want the details of the game be restricted to three rows with necessary items only that too in combined form.
    the font size be 20 pixels.
    kindly provide a vba to achieve this task. thanks
    Attached Files Attached Files
    Last edited by sumesh56; 04-18-2014 at 12:27 AM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: how to get the chess notations in columns?

    Hi Sumesh,

    After discovering how to translate moves do you want us to write a little VBA to win any possible game too?
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: how to get the chess notations in columns?

    Sumesh,

    Sorry for the delay. I started this the other day then got distracted on something else. try the attached. I put the long string of moves from your original post into cell B3 and if you run the macro it seperates the string into each move. I didn't bother trying to put the '@' symbol infront of each move as I think you were only doing this so youcould then delimit. the macro doesn't need that. Once you have your long string, paste it into b3 and hit the button.
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: how to get the chess notations in columns?

    the option> text to columns- cannot be applied due to absence of any particular character after each move
    Looks to me like there is a space each time

    (also a number and a .)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: how to get the chess notations in columns?

    Fdibbins, yes I just used the pattern of the sequence number and dot in the macro

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: how to get the chess notations in columns?

    do you mean like this?
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  7. #7
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: how to get the chess notations in columns?

    Quote Originally Posted by FDibbins View Post
    Looks to me like there is a space each time

    (also a number and a .)
    1.E3 Nf6 2.
    I want the move number to be copied. then what about move number one. it is preceded by a number and a dot.

  8. #8
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: how to get the chess notations in columns?

    Crooze & Martindwilson thanks for the responses. let me check.

  9. #9
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: how to get the chess notations in columns?

    Quote Originally Posted by Crooza View Post
    Sumesh,

    Sorry for the delay. I started this the other day then got distracted on something else. try the attached. I put the long string of moves from your original post into cell B3 and if you run the macro it seperates the string into each move. I didn't bother trying to put the '@' symbol infront of each move as I think you were only doing this so youcould then delimit. the macro doesn't need that. Once you have your long string, paste it into b3 and hit the button.
    Crooza,thanks a lot for the solution.
    I would like to ask you something more. can I?
    In B i have five records besides the chess moves. they are the details of the game.
    i want that records 1,2,and 3 be joined with a double dash character in between.
    likewise records 4 and 5 be joined .

    the macro should be based on the first word of the string in each row AND NOT on rownumber.

    the words are site, date ,results, white ,black
    ( i have got so many game moves in the file. these five records will be on the upper side of each game moves. so finding the rownumbers is difficult.
    the same words will repeat in each cluster in the same order.

    the outcome will be like this--5 rows will come to 2 rows. has to replace in B itself.
    as a result three rows will come up.
    Site "Monte Carlo MCO"-- Date "1902.02.17"---Result "1-0"
    White "Adolf Albin"----Black "Louis R Eisenberg"




    with the excecution of the macro 5 records will be cut short into 2 records.if the records were from B1:B5 now it will be from B1:B2.

    an afterthought Crooza,
    Is your macro based on B2? you have shown all the moves in B2. if it is mandatory i can do that with your advice. no problem with that.
    I normally import games from PGN which is opened in Notepad.So if it is easy for you to edit your macro based on B2 column as the source, please tell me how to do that? I don't know how to import the text file contents to a single cell.
    Attached Files Attached Files
    Last edited by sumesh56; 04-20-2014 at 02:44 AM.

  10. #10
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: how to get the chess notations in columns?

    Sumesh,

    To get the 5 rows into 2 just use the concatenate function or & symbol. It looks like you're using the full cell contents and just joining them so you don't even need to extract sub strings from the cell contents. You can add your dashes in between too. Ie =B1&" --- "&B2 entered into cell B3 will give you the two cells in B1 and B2 with the dashes between.

    In regards to the macro for the moves, you can put the string of moves anywhere if you don't want it in B3. I just used a convenient location. I did put the full string in one cell and I guess from your latest post the data import you use breaks this up. Again you can join them into one cell using the concatenate or & function I described above. If you edit the macro you'll see the reference to B3. If you change the location, then change the macro accordingly.

    How many of these do you have to convert? I'm guessing there are more than one or two and you want a fully automated conversion. I assumed initially you only wanted something to convert the moves but am I right in thinking you have a very large file with lots of these that need converting? If so post the full file so I can see what you have.

  11. #11
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: how to get the chess notations in columns?

    Quote Originally Posted by Crooza View Post
    Sumesh,

    To get the 5 rows into 2 just use the concatenate function or & symbol. It looks like you're using the full cell contents and just joining them so you don't even need to extract sub strings from the cell contents. You can add your dashes in between too. Ie =B1&" --- "&B2 entered into cell B3 will give you the two cells in B1 and B2 with the dashes between.

    In regards to the macro for the moves, you can put the string of moves anywhere if you don't want it in B3. I just used a convenient location. I did put the full string in one cell and I guess from your latest post the data import you use breaks this up. Again you can join them into one cell using the concatenate or & function I described above. If you edit the macro you'll see the reference to B3. If you change the location, then change the macro accordingly.

    How many of these do you have to convert? I'm guessing there are more than one or two and you want a fully automated conversion. I assumed initially you only wanted something to convert the moves but am I right in thinking you have a very large file with lots of these that need converting? If so post the full file so I can see what you have.
    thank you crooza for the suggestion.
    as you suggested , i tried the following and failed to get the result. the data is in col A and I put the formula in B1.

    even if this formula will join the first three rows content, the content in col A will remain. In that case, after gettting the result, i have to copy paste the contents from col B to col A. so this is not a solution to my issue,
    CONCATENATE(A1&"---")*(A2&"---")*(A3)

    the file length will vary depending upon the number of games i want to include. it may be from A1:A999 (some 50 to 300 games). i have taken a sample of two games.if the macro can convert it properly then it could be able to do for larger files.
    only thing is the macro should not be based on specified range but on col A.

    I think everything is detailed in my previous post.
    Last edited by sumesh56; 04-20-2014 at 07:03 AM.

  12. #12
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: how to get the chess notations in columns?

    concatenate >I just succeeded in putting the formula correctly and got the desired result in joining three strings of col A in col B. this is not a solution to my issue since i want the original contents in col A is replaced with the result.nothing should be displayed in col B.
    Last edited by sumesh56; 04-21-2014 at 02:02 AM.

  13. #13
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: how to get the chess notations in columns?

    Quote Originally Posted by Crooza View Post
    Sumesh,
    In regards to the macro for the moves, you can put the string of moves anywhere if you don't want it in B3. I just used a convenient location. I did put the full string in one cell
    how can i put all the moves in a single cell as you did? when i import the moves from a text file it is spread out in many rows in the same column say A14:A22.

  14. #14
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: how to get the chess notations in columns?

    Sumesh,
    If you've mastered the concatenate function you can use that to join them.

    I'll have a look at it again for you in the morning. I'm tied up at present

  15. #15
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: how to get the chess notations in columns?

    Quote Originally Posted by Crooza View Post
    Sumesh,
    If you've mastered the concatenate function you can use that to join them.

    I'll have a look at it again for you in the morning. I'm tied up at present
    thanks for the response.kindly see the attached file. i have manually done so many things. you can assume how many times i copy pasted,deleted, each and everything. in the final output i applied your macro, don't know what to do next. since the macro is designed to apply to a single file.please see to it.I have recorded a few macros(can it be called macro?) the module of which is also attached to the file. it may give you some idea as to my request to create a macro as far as the details of the games are concerned.
    Attached Files Attached Files
    Last edited by sumesh56; 04-21-2014 at 06:28 AM.

  16. #16
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: how to get the chess notations in columns?

    Sumesh,

    Try the attached.

    It's still semi manual but I'll talk to you about a fully automated version in a minute. Firstly to get you going now, simply select the cells in column A that you want to break up into columns and run the joinmoves macro. It will take whatever cells are selected, and put the moves into the 5 adjoining columns in the format you requested. You still need to select and run the macro for each individual game though at present. I've done the first dozen or so. You can delete columns c through to g and work your way through from the first game or simply scroll down to where I stopped and start selecting the game move information in column A and running the macro.

    In order to get this and the game header information fully automated I'll need you to send me a completely raw file that you have not modified - the last one appeared to have been modified with your header data chnaged from the raw format to your desired format. Send me a larger file as I need to test that it will work in a number of different situations.
    Attached Files Attached Files

  17. #17
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: how to get the chess notations in columns?

    Quote Originally Posted by Crooza View Post
    Sumesh,

    Try the attached.


    In order to get this and the game header information fully automated I'll need you to send me a completely raw file that you have not modified - the last one appeared to have been modified with your header data chnaged from the raw format to your desired format. Send me a larger file as I need to test that it will work in a number of different situations.
    thank you for the interest shown in my subject.

    well if you want a large file untouched, it is in the attachment sheet 1 named imported text untouched. to work the macro efficiently we have to extract the comments within brackets to column c.(or any other col). then only we will get moves only in col B.
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: how to get the chess notations in columns?

    Quote Originally Posted by Crooza View Post
    Sumesh,

    Try the attached.

    It's still semi manual but I'll talk to you about a fully automated version in a minute. Firstly to get you going now, simply select the cells in column A that you want to break up into columns and run the joinmoves macro. It will take whatever cells are selected, and put the moves into the 5 adjoining columns in the format you requested. You still need to select and run the macro for each individual game though at present. I've done the first dozen or so. You can delete columns c through to g and work your way through from the first game or simply scroll down to where I stopped and start selecting the game move information in column A and running the macro.
    thanks for the macro. I am very happy to see that we are half way gone. at present if i add some manual work, your macro will give the intended result with some editing in the code.
    kindly see the attached file in which i have applied your macro. see the output in col C17,21,22.there is no space between the white&black moves.
    so the first thing i request you to do is-edit the code to provide at least three spaces between each white/black move.

    i
    n your file with the macro, please see that E32:H32 and G112:G115.
    it crosses the fifth col boundary.it is because of the comments in between the moves. we have to separate the comments from the moves to col C or D or E.then we will get the moves only in col A then the macro will give somewhat a decent product in columns.
    Attached Files Attached Files
    Last edited by sumesh56; 04-22-2014 at 10:55 AM.

  19. #19
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: how to get the chess notations in columns?

    Sumesh,

    The reason there is no space between the white and black move in some instances is because there is no space in the raw data. This occurs due to your import file separating the long string of moves at 'random' positions. Sometimes the end of a line will finish with a white move and the next line of data will commence with the corresponding black move. In these instances there won't be a space. I'll give some thought to how we might overcome this. Is there a reason you want three spaces between moves or were you thinking this would fix the above problem. I think I can add three spaces BUT it will probably be by replacing every single space with a triple space so you'll also get a triple space after the move number.

    The issue of the comments is harder. It will be difficult to remove the comments from the moves unless I simply remove all text after the third group of spacings which might not hold true for every example you've provided. I'm only on the iPad at the moment so will have a look in the morning when I'm back in front of my PC.

  20. #20
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: how to get the chess notations in columns?

    Quote Originally Posted by Crooza View Post
    Sumesh,

    I'll give some thought to how we might overcome this. Is there a reason you want three spaces between moves
    so you'll also get a triple space after the move number.
    thanks for the response crooza.
    I don"t want three spaces after the move number.It should be single. but if it is possible, I want tripple spaces between the white-black moves.




    It will be difficult to remove the comments from the moves unless which might not hold true for every example you've provided. I'm only on the iPad at the moment so will have a look in the morning when I'm back in front of my PC.
    The issue of the comments is harder.
    --if it is so hard then leave it that is what i can say.
    if I simply remove all text after the third group of spacings
    --please note that the comments are in parenthesis.I have googled so many examples in which they remove text within brackets. i even tried all the formulas (mid-----substitute----,udf,codes) but failed to get result.
    http://www.excelforum.com/excel-gene...-brackets.html
    http://www.excelforum.com/excel-gene...n-bracket.html
    http://www.excelforum.com/excel-gene...ther-cell.html

  21. #21
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: how to get the chess notations in columns?

    [QUOTE=Crooza;[/QUOTE]

    Is there a reason you want three spaces between moves

    thanks for asking ,yes I do.

  22. #22
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: how to get the chess notations in columns?

    Sumesh,

    Try the attached. If you run the clean macro first (by highlighting all cells in Column A down to the "=======" then run clean, it will firstly remove all the blank lines. You need to do this first to clean the dataset

    Then as you did before select the group of moves you want to convert to 5 columns and run the chess macro. This will do what it was doing previously but I've improved it so that the three spaces are included between the white and black moves and the. all the space issues seem to be fixed now.

    It also creates the two header lines removing the square brakets too.

    I haven't removed the comments field as sometimes they are in curly brackets"{}" following a space, other times they're not so there's no 'easy' way to remove them at this stage.

    I associated the macros with control functions, you should do the same for convenience then it's just a matter of selecting which text you want and hitting ctrl + X to run it.

    It's not fully automated but probabaly as close as we'll get it. I'll be away for the ANZAC weekend so won't get backto you for a while. Good luck.

    test-chess notations(1)v6.xlsm

  23. #23
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: how to get the chess notations in columns?

    Quote Originally Posted by Crooza View Post
    Sumesh,

    Try the attached. If you run the clean macro first (by highlighting all cells in Column A down to the "=======" then run clean, it will firstly remove all the blank lines. You need to do this first to clean the dataset

    Then as you did before select the group of moves you want to convert to 5 columns and run the chess macro. This will do what it was doing previously but I've improved it so that the three spaces are included between the white and black moves and the. all the space issues seem to be fixed now.

    It also creates the two header lines removing the square brakets too.

    I haven't removed the comments field as sometimes they are in curly brackets"{}" following a space, other times they're not so there's no 'easy' way to remove them at this stage.

    I associated the macros with control functions, you should do the same for convenience then it's just a matter of selecting which text you want and hitting ctrl + X to run it.

    It's not fully automated but probabaly as close as we'll get it. I'll be away for the ANZAC weekend so won't get backto you for a while. Good luck.

    Attachment 313477
    thanks crooza for the response.

  24. #24
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: how to get the chess notations in columns?

    Crooza;3670945]Sumesh,
    thanks crooza for the edited code

    This will do what it was doing previously but I've improved it so that the three spaces are included between the white and black moves and the. all the space issues seem to be fixed now.
    not yet. there are instances where the three spaces are not added.after running the code.FOR a few examples , pls see the following ranges

    C177 : E81
    C134 : C140
    C210 : E210
    C264 : F267
    C280 : F282

    It also creates the two header lines removing the square brakets too.
    yes , as desired.this is great.

    It's probabaly as close as we'll get it.
    yes i agree,though i am a little more greedy.

    I'll be away for the ANZAC weekend
    happy journey and a pleasant stay.

  25. #25
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: how to get the chess notations in columns?

    sumesh56, it is not necessary to quote every post you respond to. If you really need to reference something specific in a post, then quote that part. Otherwise no quote is needed at all.

    Please take the time to review our rules. There aren't many, and they are all important. See rule #12. Thanks,
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  26. #26
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: how to get the chess notations in columns?

    Sumesh,

    The ranges you give as examples above are where the imported data is in a different pattern to the earlier patterns. That is, the earlier patterns all had a space after the number and the dot, the other ones don't. The macro looks for the second group of spacings in the pattern to convert to the triple spacing. When the first space after the number and the dot aren't there it can't find a second spacing group to convert to triple spaces.

    It's surprising that the program you're using to export this isn't exporting in the same format unless it's simply a long text file that your exporting, in which case you're relying on the person entering the original data to be consistent in the format.

    While the macro is able to extract the data despite the lengths, the comments and the differing start formats makes it difficult to test for every individual circumstance. It isbe possible to test each string and if there isn't a space after the first dot then add one in.

    I've just made a quick change to the macro to now differentiate bewteen the different formats of raw data. see attached test-chess notations(1)v6.xlsm

  27. #27
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: how to get the chess notations in columns?

    thanks Jeff for the direction. I will take care of that in future.have a nice day.

  28. #28
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: how to get the chess notations in columns?

    thanks Crooza for the edited code.


    Please see that the PGN files are being downloaded from various sources. the format of the headers are the same but the order of the items varies. as a result when the code works i will not get the correct headers from some files. There are 12 items in the header. we are taking five of them namely.SITE,EVENTDATE,RESULT,WHITE PLAYED BY,BLACK PLAYED BY.
    you have prepared the code as per the sample attached. it is working correctly in that file.

    now please see the attached file. the order of the items in the header is different from the old. as a result , i get the wrong result as far as the headers are concerned.

    so, either edit the macro so that it takes the correct headers irrespective of the order of its occurance in the file. or you please instruct me how to edit those line which affect the headers. so that if the format changes i can edit the macro accordingly.
    i want site,date of event,result as header #1
    i want white played by, black played by as header #2

    Kindly see the attachment.
    Attached Files Attached Files

  29. #29
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: how to get the chess notations in columns?

    thanks Crooza.
    Eureka!!!
    after five hours effort, i could find out what to do to deal with the headers.it is there at the 9th line above the second macro sub delrows. I can manipulate the headers in my own way now.

  30. #30
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: how to get the chess notations in columns?

    hi Crooza,
    I want a small edit on the macro chess.when we activate the macro we get the result in col D. the moves are distributed into five columns from D.When there are more number of moves in a game, the comsumption of the rows will be more, as a result a kind of overlapping may arise.(inside the movelist of the previous game comes the header of the second game) to avoid this, kindly give five blank rows at the end of each table of contents.

    when i refer to rows, there may arise a question. on the leftmost part, there lies the header rows. so when we allot some blank rows in col D the contents in col A will be divided. this should not happen. then how can we give the gap?
    let me list some suggestions

    let the code create blank rows at the end of the table of contents in a way that it is added before the next header row.

    or shall we separate each table of contents with a gap of a row so that it will be easier for the code to work? Initially your second code deletes all the gaps. Instead of that can we create blank rows after each games movelists?
    when we run the macro we will get each game notation in a cluster with moves and its headers,

  31. #31
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: how to get the chess notations in columns?

    Sumesh,

    I don't follow your concern with not having enough space. There should be plenty of space.

    Anyway try the attached.

    It addresses all the issues you've got with spaces or lack thereof, it gets the headers right regardless of the order you export them in, it puts all the spaces where you want them, and separates into the 5 columns.

    You'll need to highlight all the data in column A down to the row including the === and then run the clean macro. This will get the data in the proper format.

    Then simply highlight the moves only in the first game and run the chess macro. It will progressively work down the page finding each game and doing the transformation
    Attached Files Attached Files

  32. #32
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: how to get the chess notations in columns?

    hi Crooza ,

    thank you very much for the codes. may i ask you , there are three codes. one is clean,chess, and sort. you have not mentioned about the last one. no need to run it?

  33. #33
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: how to get the chess notations in columns?

    No the sort macro is incorporated onto the chess macro. You don't need it.

  34. #34
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: how to get the chess notations in columns?

    hi Crooza,thanks for the response.
    kindly see the attached.
    it gives RTE 9.
    subscript out of range
    when checked the following line is in yellow
    Active workbook.worksheets(imported text untouched").sort.sortfields.clear
    what i assume is the code requires the worksheet name.if possible make it universal so that it need not require the particular filename. or instruct me how to edit the code lines according to the file.

    bear with me. as i quoted earlier, these files being downloaded from various sources. so it differs in style
    Attached Files Attached Files

  35. #35
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: how to get the chess notations in columns?

    Sumesh,

    You've changed the name of the worksheet. Either edit the code and change "imported text untouched" to " sheet 1" or copy and paste your data each time into the worksheet I wrote the macro on. Also I note you haven't run the clean macro or if you have you've added lines in again. If you add extra lines you won't get the correct result as the headers will be out of order.

  36. #36
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: how to get the chess notations in columns?

    Sumesh,

    here's the final version now which will also remove any comments. Provided they are in parenthesis, then they get removed.
    Attached Files Attached Files

  37. #37
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: how to get the chess notations in columns?

    hi Crooza,thanks for the response.
    please see that i have attached a file . it is raw. i have done nothing.the latest macro in incorporated into it. please run it and see yourself.


    there is blank rows in 23,39,53,71,93,119.
    first select all the rows in A and run clean.
    even after running the clean macro i get blank rows at 38,69,116.
    there is a patterm.these are alternate blanks. the macro deletes the alternate blank rows only.

    may be this the reason, chess doesn't give the perfect result.

    btw,if you succeed in separating the comments, is it possible to put them under the moves with a gap of a row?
    Attached Files Attached Files
    Last edited by sumesh56; 04-30-2014 at 12:20 PM.

  38. #38
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: how to get the chess notations in columns?

    Sumesh,

    The blank rows were actually not blank but had spaces. I've corrected the macro to account for this too.

    The comments included in the data this time though were not encapsulated in parenthesis. I'm struggling to see how the consistent downloading of this data is continually in a different format each time it is downloaded.

    With inconsistent formatting it's difficult to write the code in such a way as to capture every possibility as each time I see a list of data, new unbefore seen formats crop up.

    Is there a way to ensure more consistent downloads?

  39. #39
    Forum Contributor
    Join Date
    02-28-2012
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    542

    Re: how to get the chess notations in columns?

    Quote Originally Posted by Crooza View Post
    Sumesh,

    The comments included in the data this time though were not encapsulated in parenthesis. I'm struggling to see how the consistent downloading of this data is continually in a different format each time it is downloaded.

    With inconsistent formatting it's difficult to write the code in such a way as to capture every possibility as each time I see a list of data, new unbefore seen formats crop up.

    Is there a way to ensure more consistent downloads?
    Crooza,thanks for the response. I apologize for the effort you have been taking to solve my issue.
    I fully agree with you on the second para above
    .

    Actually the files you have seen in different format, have been taken from various sources that too in various styles of presentations. I get them either as PGN or as text with commentaries. i import them as text in excel. hence the difference.

    PGN files are the primary source of my excel it contains comments within parenthesis.
    comments with moves comes in various places. when i copy down , then the style will be entirely different. I can only try to do as suggested earlier by you , i can take one of your finalised macro template, then try to copy paste the data in to it.


    btw,please see that the comments are not being deleted, instead they be put under the moves of the particular game.

    one among this community helped me to extract the comments and put under the moves. again i wanted to apply it to another style, he could not find time to help me further(i remember him with gratitude for the help).thus i was left in the middle of the sea in a boat unanchored.

    so if it is of any help to you, please see to it. the sheet one works well with the code. but sheet two doesnot. I don't remember how i made the style of the sheet1. i wanted to bring other files into this style but could not. so this is at half way. you might do something with this . kindly incorporate this code with editing in your batch of codes, thanks
    Attached Files Attached Files
    Last edited by sumesh56; 05-01-2014 at 08:48 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Combining multiple rows from 2 columns into sorted columns depending on 1st columns value
    By Dexamphetamine in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-11-2013, 10:00 AM
  2. MAcro to - copy, transpose, text to columns, remove column A, combine all columns in 1
    By galvinpaddy in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 02-07-2013, 08:48 AM
  3. Replies: 6
    Last Post: 12-26-2012, 01:43 PM
  4. Excel formula grammer in BNF or other notations
    By Ramesh561 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-07-2012, 02:15 PM
  5. Removing ridiculous scientific notations
    By Xmosis in forum Excel General
    Replies: 2
    Last Post: 03-01-2011, 03:10 PM

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