https://docs.google.com/spreadsheets/d/1ZlLGFdv6o3zK-K3v5ZFk3FKeuHFpgeWgltXtQf1bjdg/edit?usp=sharing
Refer to this sheet, I have a complex set of data with 333 rows, each row has year, make , model and each row also has product slugs which may vary in numbers, I want the year, make, model to be as it is and instead of products in columns, need in rows, that means instead of this:
| Year | Make | Model | Slug1 | Slug2 | Slug3 |
|---|---|---|---|---|---|
| 2002 | Series 1 | f82 | a | b | c |
| 2003 | Series 2 | f83 | a | b | c |
We will have
| Year | Make | Model | Slug |
|---|---|---|---|
| 2002 | Series 1 | f82 | a |
| 2002 | Series 1 | f82 | b |
| 2002 | Series 1 | f82 | c |
| 2003 | Series 2 | f83 | a |
| 2003 | Series 2 | f83 | b |
| 2003 | Series 2 | f83 | c |
Please help
Tried transposing, tries with arrays but is actually a bit too complex for me
>Solution :
You may try this in a separate new tab:
=reduce(tocol(,1),sequence(index(match(,0/('New prods - Sheet1.csv'!A2:A<>"")))),lambda(a,c,vstack(a,let(Λ,index(indirect("'New prods - Sheet1.csv'!D2:"&rows('New prods - Sheet1.csv'!A:A)),c),Σ,filter(Λ,Λ<>""),
hstack(chooserows(index('New prods - Sheet1.csv'!A2:C,c),sequence(counta(Σ),1,1,0)),tocol(Σ,1))))))
