Forum Discussion
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 worksheet
- Replace the word Submitter with Presenter in F1:J1
- Start in row 2 and tell me if there is text in the original N2:R2
- If yes, insert a duplicate row below the current row and replace the values in new F2:J2 with the values in new N2:R2
- Next, tell me if there is text in the original S2:W2.
- If yes, insert a duplicate row below the current row and replace the values in newRow1 F2:J2 with the values in newRow1 S2:W2.
- Next, tell me if there is text in the original X2:AB2.
- If yes, insert a duplicate row below the current row and replace the values in newRow2 F2:J2 with the values in newRow2 X2:AB2.
- Next, tell me if there is text in the original AC2:AG2.
- If yes, insert a duplicate row below the current row and replace the values in newRow3 F2:J2 with the values in newRow3 AC2:AG2.
- Finally, go to the next original line and repeat to the end.
How do I do that without doing it manually?
Ashley Adams
University of North Carolina Wilmington
Please excuse any typos.
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)))
14 Replies
I can't square what you describe with what you show as 'New'.
That said, such restructuring depends upon both the tools you have available and the cleanliness of the data (no partially completed presenter records). Using 365 beta
= LET( requiredColumns, HSTACK(SEQUENCE(1,5,6,1),SEQUENCE(1,18,14,1)), singleColumn, TOCOL(CHOOSECOLS(data, requiredColumns),1), WRAPROWS(singleColumn, 5) )will stack the non-blank Presenter fields.
With legacy versions of Excel, turning to VBA or possibly Power Query might be better options.
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?
- You are correct. ID #5 should have appeared 3x in my original sample set. Nice catch.
