Forum Discussion
Change the Layout to Show Each Presenter Name on Different Line
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)))
I think this may do it. No need for a long explanation because you catch on very quickly. All I'll say is, take a look at the named items.
Here it is!
1. Copy the real data to "Current"
2. Remove the "Submitter City" column
3. Update the named ranges
- Glad it worked. Have a great day!
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.
- It 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.
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?