Forum Discussion
Ashley
Sep 18, 2022Iron Contributor
Change the Layout to Show Each Presenter Name on Different Line
I would like to change the layout in the Current worksheet of the attached file to look like the sample layout in the New worksheet. Technically, I want to tell Excel to: Go to the Current work...
- Sep 18, 2022
This might work if you're using Excel 365. I believe ID #5 needs to appear 3x (The same shows 2 rows) if I follow correctly.
Defined a named range for where the presenters might appear:
=Current!$N$2:$AG$6Defined a named range for the entire data range:
=Current!$A$1:$AG$6Go row-by-row and determine if a given row has multiple presenters. If the row has multiple presenters, the row repeats as many times as needed with a comma between each. Join all the numbers, discard blanks, and split into an array:
=DROP(TEXTSPLIT(TEXTJOIN(,1,BYROW(Presenters,LAMBDA(r,REPT(ROW(r)&",",COUNTA(TAKE(r,,25))/5)))),","),,-1)*1Feed the array to CHOOSEROWS to repeat the rows as needed:
=CHOOSEROWS(records,ObtainRows)Join the newly created rows with the existing rows (no header - don't want to sort it with the data) and sort them on ID. Then, add the header:
=VSTACK(TAKE(records,1),SORT(VSTACK(DROP(records,1),ExtraRows)))
Ashley
Sep 18, 2022Iron Contributor
I was able to replicate your instructions.
How do I take it a step further and get the names of the additional presenter(s) to appear in the new row(s)? For example, I was expecting N3:R3 to appear in the new F4:J4.
How do I take it a step further and get the names of the additional presenter(s) to appear in the new row(s)? For example, I was expecting N3:R3 to appear in the new F4:J4.
Ashley
Sep 18, 2022Iron Contributor
The actual data has 67 rows including a header row. How does that change the formula in step 3?