Search code examples
google-sheetsgoogle-sheets-formulaspreadsheettranspose

How do I transform a single row of repeating data instances into multiple rows, of one instance per row? (Google Sheets)

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.

Solution

  • 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.