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)))
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 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.- The actual data has 67 rows including a header row. How does that change the formula in step 3?
- Do you happen to have a sample of the desired results (w/ made up names instead of Name 1, Name 2, etc.?)
- You are correct. ID #5 should have appeared 3x in my original sample set. Nice catch.
