I have a dataset in Google Sheets that is a single row; each data instance is 22 cells wide. I would like to transform the sheet into multiple rows, with each row containing the 22 cells for a single instance. I would like to be able to do this for varying sizes of the single-row dataset (these are monthly chess game summaries pulled from a plaintext .pgn file).
Example dataset (truncated to three instances, 66 total cells in length):
[Event "Live Chess"] [Site "Chess.com"] [Date "2022.06.17"] [Round "-"] [White "Cyroscep"] [Black "SuperCarp"] [Result "1-0"] [CurrentPosition "r3k1nr/ppq2ppp/2n5/1BbQPp2/8/8/PPP2PPP/RNB2RK1 b kq -"] [Timezone "UTC"] [ECO "B12"] [ECOUrl "https://www.chess.com/openings/Caro-Kann-Defense-Advance-Botvinnik-Carls-Defense"] [UTCDate "2022.06.17"] [UTCTime "20:10:39"] [WhiteElo "1291"] [BlackElo "1227"] [TimeControl "900 10"] [Termination "Cyroscep won by resignation"] [StartTime "20:10:39"] [EndDate "2022.06.17"] [EndTime "20:17:57"] [Link "https://www.chess.com/game/live/49235435455"] 1. e4 {[%clk 0:15:00.5]} 1... c6 {[%clk 0:15:06.7]} 2. d4 {[%clk 0:15:05.1]} 2... d5 {[%clk 0:15:14.1]} 3. e5 {[%clk 0:15:10.3]} 3... c5 {[%clk 0:15:20.9]} 4. Nf3 {[%clk 0:15:09.3]} 4... Bf5 {[%clk 0:15:07.4]} 5. dxc5 {[%clk 0:14:28.4]} 5... Nc6 {[%clk 0:15:12.5]} 6. Bb5 {[%clk 0:14:07.9]} 6... e6 {[%clk 0:14:59.9]} 7. Nd4 {[%clk 0:13:11.5]} 7... Qc7 {[%clk 0:14:42.2]} 8. Nxf5 {[%clk 0:13:04.9]} 8... exf5 {[%clk 0:14:30.6]} 9. O-O {[%clk 0:13:13.2]} 9... Bxc5 {[%clk 0:13:40.6]} 10. Qxd5 {[%clk 0:12:32.8]} 1-0 [Event "Live Chess"] [Site "Chess.com"] [Date "2022.06.14"] [Round "-"] [White "dervogel09"] [Black "SuperCarp"] [Result "0-1"] [CurrentPosition "r1b1n1k1/1p1n4/p2pN1p1/3Pp3/1P2P1rq/3B4/P2Q1P1K/RN3R2 w - -"] [Timezone "UTC"] [ECO "B06"] [ECOUrl "https://www.chess.com/openings/Modern-Defense-with-1-e4-2.d4"] [UTCDate "2022.06.14"] [UTCTime "12:12:11"] [WhiteElo "1268"] [BlackElo "1234"] [TimeControl "900 10"] [Termination "SuperCarp won by checkmate"] [StartTime "12:12:11"] [EndDate "2022.06.14"] [EndTime "12:34:16"] [Link "https://www.chess.com/game/live/48947924239"] 1. d4 {[%clk 0:15:10]} 1... g6 {[%clk 0:15:06.7]} 2. e4 {[%clk 0:15:15.5]} 2... Bg7 {[%clk 0:15:13.5]} 3. c4 {[%clk 0:15:21]} 3... d6 {[%clk 0:15:15.5]} 4. Nf3 {[%clk 0:15:13.7]} 4... Nf6 {[%clk 0:14:22.5]} 5. Bd3 {[%clk 0:14:53]} 5... e5 {[%clk 0:14:03.9]} 6. d5 {[%clk 0:14:53.3]} 6... c6 {[%clk 0:13:56]} 7. O-O {[%clk 0:14:48.9]} 7... cxd5 {[%clk 0:13:17.7]} 8. cxd5 {[%clk 0:14:58.8]} 8... a6 {[%clk 0:13:20.2]} 9. h3 {[%clk 0:13:45.6]} 9... Nbd7 {[%clk 0:13:16.2]} 10. Bg5 {[%clk 0:13:06.8]} 10... O-O {[%clk 0:12:57.7]} 11. b4 {[%clk 0:12:41.5]} 11... h6 {[%clk 0:12:16.6]} 12. Be3 {[%clk 0:12:36.9]} 12... Ne8 {[%clk 0:11:54.4]} 13. Qd2 {[%clk 0:11:45.4]} 13... f5 {[%clk 0:11:30.3]} 14. Bxh6 {[%clk 0:09:37.6]} 14... f4 {[%clk 0:11:17.2]} 15. Bg5 {[%clk 0:08:28.5]} 15... Bf6 {[%clk 0:10:53.1]} 16. h4 {[%clk 0:08:11.3]} 16... Rf7 {[%clk 0:09:38.6]} 17. g3 {[%clk 0:07:34.1]} 17... Bxg5 {[%clk 0:09:21.7]} 18. Nxg5 {[%clk 0:07:41.5]} 18... Rf6 {[%clk 0:08:58.5]} 19. gxf4 {[%clk 0:06:17.5]} 19... Rxf4 {[%clk 0:08:54.6]} 20. Ne6 {[%clk 0:06:23.8]} 20... Rg4 {[%clk 0:08:59]} 21. Kh2 {[%clk 0:06:01]} 21... Qxh4# {[%clk 0:09:05.4]} 0-1 [Event "Live Chess"] [Site "Chess.com"] [Date "2022.06.11"] [Round "-"] [White "bluesman176"] [Black "SuperCarp"] [Result "1-0"] [CurrentPosition "8/2k1K3/3P4/8/8/8/8/8 b - -"] [Timezone "UTC"] [ECO "A40"] [ECOUrl "https://www.chess.com/openings/Modern-Defense-with-1-d4"] [UTCDate "2022.06.11"] [UTCTime "20:19:53"] [WhiteElo "1237"] [BlackElo "1225"] [TimeControl "900 10"] [Termination "bluesman176 won by resignation"] [StartTime "20:19:53"] [EndDate "2022.06.11"] [EndTime "20:55:35"] [Link "https://www.chess.com/game/live/48717627003"] 1. d4 {[%clk 0:15:06.9]} 1... g6 {[%clk 0:15:05.8]} 2. c3 {[%clk 0:15:14]} 2... Bg7 {[%clk 0:15:11.1]} 3. e3 {[%clk 0:15:21.1]} 3... Nf6 {[%clk 0:15:12.9]} 4. Bd3 {[%clk 0:15:29.3]} 4... O-O {[%clk 0:15:14.5]} 5. Nf3 {[%clk 0:15:37.4]} 5... b6 {[%clk 0:14:40.7]} 6. O-O {[%clk 0:15:22.9]} 6... Bb7 {[%clk 0:14:46.4]} 7. Re1 {[%clk 0:15:29.8]} 7... d5 {[%clk 0:14:20.5]} 8. Nbd2 {[%clk 0:15:26]} 8... Nbd7 {[%clk 0:14:17.2]} 9. e4 {[%clk 0:15:30.6]} 9... dxe4 {[%clk 0:13:35.4]} 10. Nxe4 {[%clk 0:15:31.1]} 10... Nxe4 {[%clk 0:13:40.5]} 11. Bxe4 {[%clk 0:15:39]} 11... Bxe4 {[%clk 0:13:44.5]} 12. Rxe4 {[%clk 0:15:46.3]} 12... Nf6 {[%clk 0:13:39.2]} 13. Re2 {[%clk 0:15:42.9]} 13... c5 {[%clk 0:12:51.7]} 14. Qe1 {[%clk 0:15:39.9]} 14... e6 {[%clk 0:12:48]} 15. Bg5 {[%clk 0:15:37.9]} 15... h6 {[%clk 0:12:23.1]} 16. Bxf6 {[%clk 0:15:41.5]} 16... Bxf6 {[%clk 0:12:13.5]} 17. Rd1 {[%clk 0:15:47.3]} 17... c4 {[%clk 0:11:51.9]} 18. Qd2 {[%clk 0:15:41.1]} 18... Bg7 {[%clk 0:11:07.6]} 19. Ne5 {[%clk 0:14:57.2]} 19... Bxe5 {[%clk 0:10:41.8]} 20. Rxe5 {[%clk 0:14:57]} 20... Kg7 {[%clk 0:10:38.7]} 21. Qf4 {[%clk 0:14:47.1]} 21... Qf6 {[%clk 0:09:49.8]} 22. Qxf6 {[%clk 0:14:43]} 22... Kxf6 {[%clk 0:09:57]} 23. Rde1 {[%clk 0:14:50.7]} 23... h5 {[%clk 0:09:23.1]} 24. h4 {[%clk 0:14:54.6]} 24... Rac8 {[%clk 0:09:02.5]} 25. R1e3 {[%clk 0:14:32.2]} 25... Rfd8 {[%clk 0:09:00.2]} 26. Rf3 {[%clk 0:14:32.2]} 26... Kg7 {[%clk 0:08:58.3]} 27. Re4 {[%clk 0:14:36.1]} 27... Rd5 {[%clk 0:08:49]} 28. g4 {[%clk 0:14:39.8]} 28... b5 {[%clk 0:08:36.8]} 29. gxh5 {[%clk 0:14:36.8]} 29... Rxh5 {[%clk 0:08:40.8]} 30. Ref4 {[%clk 0:14:39.6]} 30... Rc7 {[%clk 0:08:01.3]} 31. Rg4 {[%clk 0:13:58]} 31... a5 {[%clk 0:07:04.5]} 32. Kg2 {[%clk 0:13:45.5]} 32... a4 {[%clk 0:06:41.4]} 33. Kh3 {[%clk 0:13:39.6]} 33... Rd7 {[%clk 0:06:23.8]} 34. Rfg3 {[%clk 0:13:35.8]} 34... Rdd5 {[%clk 0:06:13.3]} 35. f4 {[%clk 0:13:35.6]} 35... Kh6 {[%clk 0:04:49.5]} 36. Rg5 {[%clk 0:13:09]} 36... Rf5 {[%clk 0:03:44.4]} 37. Rxf5 {[%clk 0:13:07.9]} 37... Rxf5 {[%clk 0:03:37.7]} 38. Rg5 {[%clk 0:13:11.7]} 38... Rxf4 {[%clk 0:03:06]} 39. Rxb5 {[%clk 0:13:10.8]} 39... Rf3 {[%clk 0:03:04.3]} 40. Kg4 {[%clk 0:13:16.2]} 40... Rf2 {[%clk 0:03:10.7]} 41. Rb4 {[%clk 0:13:14.3]} 41... f5 {[%clk 0:03:11.4]} 42. Kg3 {[%clk 0:13:20.3]} 42... Re2 {[%clk 0:02:51.6]} 43. Rxc4 {[%clk
Example desired new format (3 rows of 22 cells):
[Event "Live Chess"] [Site "Chess.com"] [Date "2022.06.17"] [Round "-"] [White "Cyroscep"] [Black "SuperCarp"] [Result "1-0"] [CurrentPosition "r3k1nr/ppq2ppp/2n5/1BbQPp2/8/8/PPP2PPP/RNB2RK1 b kq -"] [Timezone "UTC"] [ECO "B12"] [ECOUrl "https://www.chess.com/openings/Caro-Kann-Defense-Advance-Botvinnik-Carls-Defense"] [UTCDate "2022.06.17"] [UTCTime "20:10:39"] [WhiteElo "1291"] [BlackElo "1227"] [TimeControl "900 10"] [Termination "Cyroscep won by resignation"] [StartTime "20:10:39"] [EndDate "2022.06.17"] [EndTime "20:17:57"] [Link "https://www.chess.com/game/live/49235435455"] 1. e4 {[%clk 0:15:00.5]} 1... c6 {[%clk 0:15:06.7]} 2. d4 {[%clk 0:15:05.1]} 2... d5 {[%clk 0:15:14.1]} 3. e5 {[%clk 0:15:10.3]} 3... c5 {[%clk 0:15:20.9]} 4. Nf3 {[%clk 0:15:09.3]} 4... Bf5 {[%clk 0:15:07.4]} 5. dxc5 {[%clk 0:14:28.4]} 5... Nc6 {[%clk 0:15:12.5]} 6. Bb5 {[%clk 0:14:07.9]} 6... e6 {[%clk 0:14:59.9]} 7. Nd4 {[%clk 0:13:11.5]} 7... Qc7 {[%clk 0:14:42.2]} 8. Nxf5 {[%clk 0:13:04.9]} 8... exf5 {[%clk 0:14:30.6]} 9. O-O {[%clk 0:13:13.2]} 9... Bxc5 {[%clk 0:13:40.6]} 10. Qxd5 {[%clk 0:12:32.8]} 1-0
[Event "Live Chess"] [Site "Chess.com"] [Date "2022.06.14"] [Round "-"] [White "dervogel09"] [Black "SuperCarp"] [Result "0-1"] [CurrentPosition "r1b1n1k1/1p1n4/p2pN1p1/3Pp3/1P2P1rq/3B4/P2Q1P1K/RN3R2 w - -"] [Timezone "UTC"] [ECO "B06"] [ECOUrl "https://www.chess.com/openings/Modern-Defense-with-1-e4-2.d4"] [UTCDate "2022.06.14"] [UTCTime "12:12:11"] [WhiteElo "1268"] [BlackElo "1234"] [TimeControl "900 10"] [Termination "SuperCarp won by checkmate"] [StartTime "12:12:11"] [EndDate "2022.06.14"] [EndTime "12:34:16"] [Link "https://www.chess.com/game/live/48947924239"] 1. d4 {[%clk 0:15:10]} 1... g6 {[%clk 0:15:06.7]} 2. e4 {[%clk 0:15:15.5]} 2... Bg7 {[%clk 0:15:13.5]} 3. c4 {[%clk 0:15:21]} 3... d6 {[%clk 0:15:15.5]} 4. Nf3 {[%clk 0:15:13.7]} 4... Nf6 {[%clk 0:14:22.5]} 5. Bd3 {[%clk 0:14:53]} 5... e5 {[%clk 0:14:03.9]} 6. d5 {[%clk 0:14:53.3]} 6... c6 {[%clk 0:13:56]} 7. O-O {[%clk 0:14:48.9]} 7... cxd5 {[%clk 0:13:17.7]} 8. cxd5 {[%clk 0:14:58.8]} 8... a6 {[%clk 0:13:20.2]} 9. h3 {[%clk 0:13:45.6]} 9... Nbd7 {[%clk 0:13:16.2]} 10. Bg5 {[%clk 0:13:06.8]} 10... O-O {[%clk 0:12:57.7]} 11. b4 {[%clk 0:12:41.5]} 11... h6 {[%clk 0:12:16.6]} 12. Be3 {[%clk 0:12:36.9]} 12... Ne8 {[%clk 0:11:54.4]} 13. Qd2 {[%clk 0:11:45.4]} 13... f5 {[%clk 0:11:30.3]} 14. Bxh6 {[%clk 0:09:37.6]} 14... f4 {[%clk 0:11:17.2]} 15. Bg5 {[%clk 0:08:28.5]} 15... Bf6 {[%clk 0:10:53.1]} 16. h4 {[%clk 0:08:11.3]} 16... Rf7 {[%clk 0:09:38.6]} 17. g3 {[%clk 0:07:34.1]} 17... Bxg5 {[%clk 0:09:21.7]} 18. Nxg5 {[%clk 0:07:41.5]} 18... Rf6 {[%clk 0:08:58.5]} 19. gxf4 {[%clk 0:06:17.5]} 19... Rxf4 {[%clk 0:08:54.6]} 20. Ne6 {[%clk 0:06:23.8]} 20... Rg4 {[%clk 0:08:59]} 21. Kh2 {[%clk 0:06:01]} 21... Qxh4# {[%clk 0:09:05.4]} 0-1
[Event "Live Chess"] [Site "Chess.com"] [Date "2022.06.11"] [Round "-"] [White "bluesman176"] [Black "SuperCarp"] [Result "1-0"] [CurrentPosition "8/2k1K3/3P4/8/8/8/8/8 b - -"] [Timezone "UTC"] [ECO "A40"] [ECOUrl "https://www.chess.com/openings/Modern-Defense-with-1-d4"] [UTCDate "2022.06.11"] [UTCTime "20:19:53"] [WhiteElo "1237"] [BlackElo "1225"] [TimeControl "900 10"] [Termination "bluesman176 won by resignation"] [StartTime "20:19:53"] [EndDate "2022.06.11"] [EndTime "20:55:35"] [Link "https://www.chess.com/game/live/48717627003"] 1. d4 {[%clk 0:15:06.9]} 1... g6 {[%clk 0:15:05.8]} 2. c3 {[%clk 0:15:14]} 2... Bg7 {[%clk 0:15:11.1]} 3. e3 {[%clk 0:15:21.1]} 3... Nf6 {[%clk 0:15:12.9]} 4. Bd3 {[%clk 0:15:29.3]} 4... O-O {[%clk 0:15:14.5]} 5. Nf3 {[%clk 0:15:37.4]} 5... b6 {[%clk 0:14:40.7]} 6. O-O {[%clk 0:15:22.9]} 6... Bb7 {[%clk 0:14:46.4]} 7. Re1 {[%clk 0:15:29.8]} 7... d5 {[%clk 0:14:20.5]} 8. Nbd2 {[%clk 0:15:26]} 8... Nbd7 {[%clk 0:14:17.2]} 9. e4 {[%clk 0:15:30.6]} 9... dxe4 {[%clk 0:13:35.4]} 10. Nxe4 {[%clk 0:15:31.1]} 10... Nxe4 {[%clk 0:13:40.5]} 11. Bxe4 {[%clk 0:15:39]} 11... Bxe4 {[%clk 0:13:44.5]} 12. Rxe4 {[%clk 0:15:46.3]} 12... Nf6 {[%clk 0:13:39.2]} 13. Re2 {[%clk 0:15:42.9]} 13... c5 {[%clk 0:12:51.7]} 14. Qe1 {[%clk 0:15:39.9]} 14... e6 {[%clk 0:12:48]} 15. Bg5 {[%clk 0:15:37.9]} 15... h6 {[%clk 0:12:23.1]} 16. Bxf6 {[%clk 0:15:41.5]} 16... Bxf6 {[%clk 0:12:13.5]} 17. Rd1 {[%clk 0:15:47.3]} 17... c4 {[%clk 0:11:51.9]} 18. Qd2 {[%clk 0:15:41.1]} 18... Bg7 {[%clk 0:11:07.6]} 19. Ne5 {[%clk 0:14:57.2]} 19... Bxe5 {[%clk 0:10:41.8]} 20. Rxe5 {[%clk 0:14:57]} 20... Kg7 {[%clk 0:10:38.7]} 21. Qf4 {[%clk 0:14:47.1]} 21... Qf6 {[%clk 0:09:49.8]} 22. Qxf6 {[%clk 0:14:43]} 22... Kxf6 {[%clk 0:09:57]} 23. Rde1 {[%clk 0:14:50.7]} 23... h5 {[%clk 0:09:23.1]} 24. h4 {[%clk 0:14:54.6]} 24... Rac8 {[%clk 0:09:02.5]} 25. R1e3 {[%clk 0:14:32.2]} 25... Rfd8 {[%clk 0:09:00.2]} 26. Rf3 {[%clk 0:14:32.2]} 26... Kg7 {[%clk 0:08:58.3]} 27. Re4 {[%clk 0:14:36.1]} 27... Rd5 {[%clk 0:08:49]} 28. g4 {[%clk 0:14:39.8]} 28... b5 {[%clk 0:08:36.8]} 29. gxh5 {[%clk 0:14:36.8]} 29... Rxh5 {[%clk 0:08:40.8]} 30. Ref4 {[%clk 0:14:39.6]} 30... Rc7 {[%clk 0:08:01.3]} 31. Rg4 {[%clk 0:13:58]} 31... a5 {[%clk 0:07:04.5]} 32. Kg2 {[%clk 0:13:45.5]} 32... a4 {[%clk 0:06:41.4]} 33. Kh3 {[%clk 0:13:39.6]} 33... Rd7 {[%clk 0:06:23.8]} 34. Rfg3 {[%clk 0:13:35.8]} 34... Rdd5 {[%clk 0:06:13.3]} 35. f4 {[%clk 0:13:35.6]} 35... Kh6 {[%clk 0:04:49.5]} 36. Rg5 {[%clk 0:13:09]} 36... Rf5 {[%clk 0:03:44.4]} 37. Rxf5 {[%clk 0:13:07.9]} 37... Rxf5 {[%clk 0:03:37.7]} 38. Rg5 {[%clk 0:13:11.7]} 38... Rxf4 {[%clk 0:03:06]} 39. Rxb5 {[%clk 0:13:10.8]} 39... Rf3 {[%clk 0:03:04.3]} 40. Kg4 {[%clk 0:13:16.2]} 40... Rf2 {[%clk 0:03:10.7]} 41. Rb4 {[%clk 0:13:14.3]} 41... f5 {[%clk 0:03:11.4]} 42. Kg3 {[%clk 0:13:20.3]} 42... Re2 {[%clk 0:02:51.6]} 43. Rxc4 {[%clk
I am able to manually accomplish this using simple formulas into the rows/cells below the dataset but it is quite tedious and not practical for the dataset which may have hundreds of instances.
[Event "Live Chess"] [Site "Chess.com"] [Date "2022.06.17"] [Round "-"] [White "Cyroscep"] [Black "SuperCarp"] [Result "1-0"] [CurrentPosition "r3k1nr/ppq2ppp/2n5/1BbQPp2/8/8/PPP2PPP/RNB2RK1 b kq -"] [Timezone "UTC"] [ECO "B12"] [ECOUrl "https://www.chess.com/openings/Caro-Kann-Defense-Advance-Botvinnik-Carls-Defense"] [UTCDate "2022.06.17"] [UTCTime "20:10:39"] [WhiteElo "1291"] [BlackElo "1227"] [TimeControl "900 10"] [Termination "Cyroscep won by resignation"] [StartTime "20:10:39"] [EndDate "2022.06.17"] [EndTime "20:17:57"] [Link "https://www.chess.com/game/live/49235435455"] 1. e4 {[%clk 0:15:00.5]} 1... c6 {[%clk 0:15:06.7]} 2. d4 {[%clk 0:15:05.1]} 2... d5 {[%clk 0:15:14.1]} 3. e5 {[%clk 0:15:10.3]} 3... c5 {[%clk 0:15:20.9]} 4. Nf3 {[%clk 0:15:09.3]} 4... Bf5 {[%clk 0:15:07.4]} 5. dxc5 {[%clk 0:14:28.4]} 5... Nc6 {[%clk 0:15:12.5]} 6. Bb5 {[%clk 0:14:07.9]} 6... e6 {[%clk 0:14:59.9]} 7. Nd4 {[%clk 0:13:11.5]} 7... Qc7 {[%clk 0:14:42.2]} 8. Nxf5 {[%clk 0:13:04.9]} 8... exf5 {[%clk 0:14:30.6]} 9. O-O {[%clk 0:13:13.2]} 9... Bxc5 {[%clk 0:13:40.6]} 10. Qxd5 {[%clk 0:12:32.8]} 1-0 [Event "Live Chess"] [Site "Chess.com"] [Date "2022.06.14"] [Round "-"] [White "dervogel09"] [Black "SuperCarp"] [Result "0-1"] [CurrentPosition "r1b1n1k1/1p1n4/p2pN1p1/3Pp3/1P2P1rq/3B4/P2Q1P1K/RN3R2 w - -"] [Timezone "UTC"] [ECO "B06"] [ECOUrl "https://www.chess.com/openings/Modern-Defense-with-1-e4-2.d4"] [UTCDate "2022.06.14"] [UTCTime "12:12:11"] [WhiteElo "1268"] [BlackElo "1234"] [TimeControl "900 10"] [Termination "SuperCarp won by checkmate"] [StartTime "12:12:11"] [EndDate "2022.06.14"] [EndTime "12:34:16"] [Link "https://www.chess.com/game/live/48947924239"] 1. d4 {[%clk 0:15:10]} 1... g6 {[%clk 0:15:06.7]} 2. e4 {[%clk 0:15:15.5]} 2... Bg7 {[%clk 0:15:13.5]} 3. c4 {[%clk 0:15:21]} 3... d6 {[%clk 0:15:15.5]} 4. Nf3 {[%clk 0:15:13.7]} 4... Nf6 {[%clk 0:14:22.5]} 5. Bd3 {[%clk 0:14:53]} 5... e5 {[%clk 0:14:03.9]} 6. d5 {[%clk 0:14:53.3]} 6... c6 {[%clk 0:13:56]} 7. O-O {[%clk 0:14:48.9]} 7... cxd5 {[%clk 0:13:17.7]} 8. cxd5 {[%clk 0:14:58.8]} 8... a6 {[%clk 0:13:20.2]} 9. h3 {[%clk 0:13:45.6]} 9... Nbd7 {[%clk 0:13:16.2]} 10. Bg5 {[%clk 0:13:06.8]} 10... O-O {[%clk 0:12:57.7]} 11. b4 {[%clk 0:12:41.5]} 11... h6 {[%clk 0:12:16.6]} 12. Be3 {[%clk 0:12:36.9]} 12... Ne8 {[%clk 0:11:54.4]} 13. Qd2 {[%clk 0:11:45.4]} 13... f5 {[%clk 0:11:30.3]} 14. Bxh6 {[%clk 0:09:37.6]} 14... f4 {[%clk 0:11:17.2]} 15. Bg5 {[%clk 0:08:28.5]} 15... Bf6 {[%clk 0:10:53.1]} 16. h4 {[%clk 0:08:11.3]} 16... Rf7 {[%clk 0:09:38.6]} 17. g3 {[%clk 0:07:34.1]} 17... Bxg5 {[%clk 0:09:21.7]} 18. Nxg5 {[%clk 0:07:41.5]} 18... Rf6 {[%clk 0:08:58.5]} 19. gxf4 {[%clk 0:06:17.5]} 19... Rxf4 {[%clk 0:08:54.6]} 20. Ne6 {[%clk 0:06:23.8]} 20... Rg4 {[%clk 0:08:59]} 21. Kh2 {[%clk 0:06:01]} 21... Qxh4# {[%clk 0:09:05.4]} 0-1 [Event "Live Chess"] [Site "Chess.com"] [Date "2022.06.11"] [Round "-"] [White "bluesman176"] [Black "SuperCarp"] [Result "1-0"] [CurrentPosition "8/2k1K3/3P4/8/8/8/8/8 b - -"] [Timezone "UTC"] [ECO "A40"] [ECOUrl "https://www.chess.com/openings/Modern-Defense-with-1-d4"] [UTCDate "2022.06.11"] [UTCTime "20:19:53"] [WhiteElo "1237"] [BlackElo "1225"] [TimeControl "900 10"] [Termination "bluesman176 won by resignation"] [StartTime "20:19:53"] [EndDate "2022.06.11"] [EndTime "20:55:35"] [Link "https://www.chess.com/game/live/48717627003"] 1. d4 {[%clk 0:15:06.9]} 1... g6 {[%clk 0:15:05.8]} 2. c3 {[%clk 0:15:14]} 2... Bg7 {[%clk 0:15:11.1]} 3. e3 {[%clk 0:15:21.1]} 3... Nf6 {[%clk 0:15:12.9]} 4. Bd3 {[%clk 0:15:29.3]} 4... O-O {[%clk 0:15:14.5]} 5. Nf3 {[%clk 0:15:37.4]} 5... b6 {[%clk 0:14:40.7]} 6. O-O {[%clk 0:15:22.9]} 6... Bb7 {[%clk 0:14:46.4]} 7. Re1 {[%clk 0:15:29.8]} 7... d5 {[%clk 0:14:20.5]} 8. Nbd2 {[%clk 0:15:26]} 8... Nbd7 {[%clk 0:14:17.2]} 9. e4 {[%clk 0:15:30.6]} 9... dxe4 {[%clk 0:13:35.4]} 10. Nxe4 {[%clk 0:15:31.1]} 10... Nxe4 {[%clk 0:13:40.5]} 11. Bxe4 {[%clk 0:15:39]} 11... Bxe4 {[%clk 0:13:44.5]} 12. Rxe4 {[%clk 0:15:46.3]} 12... Nf6 {[%clk 0:13:39.2]} 13. Re2 {[%clk 0:15:42.9]} 13... c5 {[%clk 0:12:51.7]} 14. Qe1 {[%clk 0:15:39.9]} 14... e6 {[%clk 0:12:48]} 15. Bg5 {[%clk 0:15:37.9]} 15... h6 {[%clk 0:12:23.1]} 16. Bxf6 {[%clk 0:15:41.5]} 16... Bxf6 {[%clk 0:12:13.5]} 17. Rd1 {[%clk 0:15:47.3]} 17... c4 {[%clk 0:11:51.9]} 18. Qd2 {[%clk 0:15:41.1]} 18... Bg7 {[%clk 0:11:07.6]} 19. Ne5 {[%clk 0:14:57.2]} 19... Bxe5 {[%clk 0:10:41.8]} 20. Rxe5 {[%clk 0:14:57]} 20... Kg7 {[%clk 0:10:38.7]} 21. Qf4 {[%clk 0:14:47.1]} 21... Qf6 {[%clk 0:09:49.8]} 22. Qxf6 {[%clk 0:14:43]} 22... Kxf6 {[%clk 0:09:57]} 23. Rde1 {[%clk 0:14:50.7]} 23... h5 {[%clk 0:09:23.1]} 24. h4 {[%clk 0:14:54.6]} 24... Rac8 {[%clk 0:09:02.5]} 25. R1e3 {[%clk 0:14:32.2]} 25... Rfd8 {[%clk 0:09:00.2]} 26. Rf3 {[%clk 0:14:32.2]} 26... Kg7 {[%clk 0:08:58.3]} 27. Re4 {[%clk 0:14:36.1]} 27... Rd5 {[%clk 0:08:49]} 28. g4 {[%clk 0:14:39.8]} 28... b5 {[%clk 0:08:36.8]} 29. gxh5 {[%clk 0:14:36.8]} 29... Rxh5 {[%clk 0:08:40.8]} 30. Ref4 {[%clk 0:14:39.6]} 30... Rc7 {[%clk 0:08:01.3]} 31. Rg4 {[%clk 0:13:58]} 31... a5 {[%clk 0:07:04.5]} 32. Kg2 {[%clk 0:13:45.5]} 32... a4 {[%clk 0:06:41.4]} 33. Kh3 {[%clk 0:13:39.6]} 33... Rd7 {[%clk 0:06:23.8]} 34. Rfg3 {[%clk 0:13:35.8]} 34... Rdd5 {[%clk 0:06:13.3]} 35. f4 {[%clk 0:13:35.6]} 35... Kh6 {[%clk 0:04:49.5]} 36. Rg5 {[%clk 0:13:09]} 36... Rf5 {[%clk 0:03:44.4]} 37. Rxf5 {[%clk 0:13:07.9]} 37... Rxf5 {[%clk 0:03:37.7]} 38. Rg5 {[%clk 0:13:11.7]} 38... Rxf4 {[%clk 0:03:06]} 39. Rxb5 {[%clk 0:13:10.8]} 39... Rf3 {[%clk 0:03:04.3]} 40. Kg4 {[%clk 0:13:16.2]} 40... Rf2 {[%clk 0:03:10.7]} 41. Rb4 {[%clk 0:13:14.3]} 41... f5 {[%clk 0:03:11.4]} 42. Kg3 {[%clk 0:13:20.3]} 42... Re2 {[%clk 0:02:51.6]} 43. Rxc4 {[%clk
=W1 | =X1 | Y1 | etc
=AS1 | = AT1 = | =AU1 | etc
I have tried inputting the formulas above manually for three instances and trying to fill the rows below by dragging the corner of the selected three rows but the formulas don't seem to "take." I haven't been able to figure out how to force an iterative 22-cell pull from the single row into each row below. Thank you for any ideas.
CodePudding user response：
Input(A1:1):
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | AT | AU | AV | AW | AX | AY | AZ | BA | BB | BC | BD | BE | BF | BG | BH | BI | BJ | BK | BL | BM | BN |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | 34 | 35 | 36 | 37 | 38 | 39 | 40 | 41 | 42 | 43 | 44 | 45 | 46 | 47 | 48 | 49 | 50 | 51 | 52 | 53 | 54 | 55 | 56 | 57 | 58 | 59 | 60 | 61 | 62 | 63 | 64 | 65 | 66 |
Output(A10:V14):
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 |
| 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | 34 | 35 | 36 | 37 | 38 | 39 | 40 | 41 | 42 | 43 | 44 |
| 45 | 46 | 47 | 48 | 49 | 50 | 51 | 52 | 53 | 54 | 55 | 56 | 57 | 58 | 59 | 60 | 61 | 62 | 63 | 64 | 65 | 66 |
Formula in A10:
=QUERY(TRANSPOSE(A1:1),"skipping 22",)
Auto fill to the right.
CodePudding user response：
You can use the "unpivot" custom function I wrote to answer this common question.