Forum Discussion
Change the Layout to Show Each Presenter Name on Different Line
- 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)))
The second try went a little bit better. I'm in my workbook with real data. Worksheet names are "Current" and "New" just like before.
Here is my New Name Manager:
How do I get the magic grey box to populate? Wizardry?
- Patrick2788Sep 21, 2022Silver ContributorGlad it worked. Have a great day!
- AshleySep 21, 2022Iron Contributor
I switched to my personal laptop and was able to replicate your instructions. Success! Correct answer!
Dear Excel Tech Community Members,
Bookmark this answer to your favs!
Capturing the galaxy in a clear glass jar. Photo by Rakicevic Nenad.
- Patrick2788Sep 20, 2022Silver ContributorIt looks like the error is with 'Bigstack'. Excel is not recognizing VSTACK. You may want to verify you still have access to VSTACK, HSTACK, etc. Sometimes those functions can go missing even when you're on a version of Excel that includes them.