Spreadsheet: 800556d1665738456-formulate-unorganised-data-into-organised-one-portal-data1-tms
Sheet: Formula

 |  |  |  |  |  |  |  |  |  | =LET( oda, 'Original Data'!$A$2:$A$124, odb, 'Original Data'!$B$2:$B$124, odd, 'Original Data'!$D$2:$D$124, ode, 'Original Data'!$E$2:$E$124, odf, 'Original Data'!$F$2:$F$124, base, FILTER(oda,(ode=1)*(odf=2)), fca, FILTER(oda, (ode=1)*(odf=2)), fcb, INDEX(oda, MATCH(base, oda,0)+2), fcc, IF(RIGHT(base, 1)="s", "Y", "N"), fcd, MAKEARRAY(ROWS(base),1,   LAMBDA(r,c, LET(tms, INDEX(odb,MATCH(INDEX(base,ROW(INDEX($A:$A,r))),oda,0)+2), TRIM(LEFT(tms, FIND("-",tms)-1))))), fce, MAKEARRAY(ROWS(base),1,   LAMBDA(r,c, LET(tms, INDEX(odb,MATCH(INDEX(base,ROW(INDEX($A:$A,r))),oda,0)+2), TRIM(RIGHT(tms, LEN(tms)-(FIND("-",tms)+1)))))), fcf, MAKEARRAY(ROWS(base),1,   LAMBDA(r,c, LET(tms, INDEX(odd,MATCH(INDEX(base,ROW(INDEX($A:$A,r))),oda,0)+2), IFERROR(TRIM(LEFT(tms, FIND(":",tms)-1))+0,tms)))), fcg, MAKEARRAY(ROWS(base),1,   LAMBDA(r,c, LET(tms, INDEX(odd,MATCH(INDEX(base,ROW(INDEX($A:$A,r))),oda,0)+2), IFERROR(TRIM(RIGHT(tms, LEN(tms)-(FIND(":",tms))))+0,tms)))), fch, INDEX(ode,MATCH(base,oda,0)+2), fci, INDEX(odf,MATCH(base,oda,0)+2), HSTACK(fca, fcb, fcc, fcd, fce,fcf, fcg, fch, fci))
 | 16:40:00 | Y | [NAME] | [NAME] | 171 | 92 | 1.45 | 2.82
26 Jan 2022 - Play Offs | 16:25:00 | Y | [NAME] | Adelaide Strikers | 170 | 167 | 2.2 | 1.69
23 Jan 2022 - Play Offs | 16:15:00 | Y | [NAME] | Adelaide Strikers | 178 | 184 | 1.72 | 2.13
22 Jan 2022 - Play Offs | 16:15:00 | Y | [NAME] | [NAME] | 189 | 141 | 1.64 | 2.27
21 Jan 2022 - Play Offs | 16:15:00 | Y | Adelaide Strikers | [NAME] | 188 | 166 | 1.76 | 2.05
2022-01-19 00:00:00 | 16:50:00 | N | [NAME] | [NAME] | 273 | 167 | 1.75 | 2.06
2022-01-18 00:00:00 | 16:15:00 | N | [NAME] | Melbourne Renegades | 182 | 176 | 1.44 | 2.78
2022-01-17 00:00:00 | 17:15:00 | N | [NAME] | [NAME] | 155 | 156 | 2.65 | 1.48
2022-01-16 00:00:00 | 16:15:00 | N | [NAME] | [NAME] | 150 | 149 | 1.56 | 2.43
2022-01-15 00:00:00 | 15:40:00 | N | [NAME] | [NAME] | 197 | 137 | 2.06 | 1.77
2022-01-14 00:00:00 | 07:40:00 | N | Adelaide Strikers | [NAME] | 130 | 127 | 2.43 | 1.56
2022-01-13 00:00:00 | 16:35:00 | N | Melbourne Renegades | [NAME] | 122 | 123 | 2.35 | 1.57
2022-01-12 00:00:00 | 16:15:00 | N | [NAME] | Adelaide Strikers | 90 | 161 | 1.83 | 1.98
2022-01-11 00:00:00 | 16:15:00 | N | Melbourne Renegades | [NAME] | 105 | 150 | 2.11 | 1.73
2022-01-10 00:00:00 | 16:15:00 | N | [NAME] | Adelaide Strikers | 140 | 139 | 1.71 | 2.15
2022-01-09 00:00:00 | 16:15:00 | N | [NAME] | [NAME] | 151 | 152 | 1.89 | 1.93
2022-01-08 00:00:00 | 17:30:00 | N | [NAME] | [NAME] | 144 | 145 | 4.28 | 1.23
2022-01-06 00:00:00 | 18:15:00 | N | [NAME] | [NAME] | 133 | 137 | 2.06 | 1.77
2022-01-05 00:00:00 | 16:15:00 | N | Adelaide Strikers | [NAME] | 129 | 126 | 2.15 | 1.71
2022-01-04 00:00:00 | 16:15:00 | N | [NAME] | [NAME] | 143 | 133 | 2.17 | 1.68
2022-01-03 00:00:00 | 16:15:00 | N | [NAME] | Melbourne Renegades | 126 | 129 | 2.76 | 1.44
2022-01-02 00:00:00 | 16:15:00 | N | [NAME] | Adelaide Strikers | 172 | 144 | 1.85 | 1.97
2022-01-01 00:00:00 | 16:30:00 | N | [NAME] | Melbourne Renegades | abn. | abn. | 1.4 | 2.99
2021-12-31 00:00:00 | 16:15:00 | N | Adelaide Strikers | [NAME] | 165 | 187 | 1.72 | 2.1
2021-12-29 00:00:00 | 16:15:00 | N | Melbourne Renegades | [NAME] | 121 | 206 | 2.22 | 1.67
2021-12-28 00:00:00 | 16:15:00 | N | [NAME] | [NAME] | 200 | 166 | 2.35 | 1.59
2021-12-27 00:00:00 | 18:05:00 | N | [NAME] | [NAME] | 187 | 207 | 2.21 | 1.66
2021-12-26 00:00:00 | 18:15:00 | N | [NAME] | Melbourne Renegades | 152 | 151 | 1.43 | 2.86
2021-12-24 00:00:00 | 12:30:00 | N | [NAME] | [NAME] | 180 | 156 | 2.15 | 1.7
2021-12-23 00:00:00 | 16:15:00 | N | Adelaide Strikers | [NAME] | 169 | 208 | 1.66 | 2.22
2021-12-22 00:00:00 | 16:15:00 | N | Melbourne Renegades | [NAME] | 185 | 206 | 2.44 | 1.56
2021-12-21 00:00:00 | 16:15:00 | N | [NAME] | Adelaide Strikers | 150 | 147 | 1.57 | 2.39
2021-12-20 00:00:00 | 16:15:00 | N | [NAME] | [NAME] | 167 | 125 | 1.65 | 2.22
2021-12-19 00:00:00 | 16:40:00 | N | [NAME] | [NAME] | 143 | 196 | 1.88 | 1.88
2021-12-15 00:00:00 | 16:15:00 | N | [NAME] | [NAME] | 177 | 182 | 1.81 | 1.99
2021-12-14 00:00:00 | 16:15:00 | N | [NAME] | [NAME] | 129 | 182 | 2.12 | 1.73