Forum Discussion
Transforming data using new array shaping formula in excel
Hello Community,
I'm transforming a set of data into tabular form with the new array shaping formula such as vstack, tocol, chooserow and so forth
This is how my data set looks like
| Text 1 | Text 2 | Date 1 | Date 2 | Date 3 | Date 4 |
| 1 | A | 1-Nov-22 | 3-Nov-22 | 5-Nov-22 | 7-Nov-22 |
| 2 | B | 1-Oct-22 | 3-Oct-22 | 5-Oct-22 | 7-Oct-22 |
| 3 | C | 3-Aug-22 | 5-Aug-22 | 7-Aug-22 | 9-Aug-22 |
| 4 | D | 19-Jul-22 | 21-Jul-22 | 23-Jul-22 | 25-Jul-22 |
This is what i want to achieve
| 1 | A | Date 1 | 1-Nov-22 |
| 2 | B | Date 2 | 3-Nov-22 |
| 3 | C | Date 3 | 5-Nov-22 |
| 4 | D | Date 4 | 7-Nov-22 |
| 1 | A | Date 1 | 1-Oct-22 |
| 2 | B | Date 2 | 3-Oct-22 |
| 3 | C | Date 3 | 5-Oct-22 |
| 4 | D | Date 4 | 7-Oct-22 |
| 1 | A | Date 1 | 3-Aug-22 |
| 2 | B | Date 2 | 5-Aug-22 |
| 3 | C | Date 3 | 7-Aug-22 |
| 4 | D | Date 4 | 9-Aug-22 |
| 1 | A | Date 1 | 19-Jul-22 |
| 2 | B | Date 2 | 21-Jul-22 |
| 3 | C | Date 3 | 23-Jul-22 |
| 4 | D | Date 4 | 25-Jul-22 |
This is what i have done so far:
I have difficulties in transforming Column text 1 and text 2 into the right rows.
In addition, my set of data has over 180 rows and i can't replicate date 1,2,3,4 using chooserow for 180 times.
Is there any expert out there able to advise? Thanks for your help
sample illustrates the logic if number of rows in the table is the same as number of date columns. To take it literally the could be like
=DROP( REDUCE( "", SEQUENCE(ROWS(Tabelle1)), LAMBDA(a,v, VSTACK( a, HSTACK( Tabelle1[Text 1], Tabelle1[Text 2], TRANSPOSE(Tabelle1[[#Headers],[Date 1]:[Date 5]]), TRANSPOSE(DROP(CHOOSEROWS(Tabelle1, v), , 2)) ) ) ) ), 1 )But what shall be the logic of the final table if, for example, you have same 5 rows in table but only two date columns?
An alternative could be Power Query. In the attached file you can enter data in the large blue dynamic table. Then you can enter the number of rows in the small blue dynamic table. In my understanding the number of rows is always the same as the number of columns with dates in your data. For example 5 columns "Date1" to "Date 5" and 5 rows 1,2,3,4 and 5 in column "Text 1". You can then click in any cell of the green table and right-click with the mouse. Then you can select refresh to update the green result table. The green table and the small blue table can be dragged to the right according to the requirements.
