Forum Discussion

Ashley's avatar
Ashley
Iron Contributor
Sep 18, 2022
Solved

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...
BI & Data Analysis
excel
Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Sep 18, 2022

    Ashley 

     

    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$6

    Defined a named range for the entire data range:

    =Current!$A$1:$AG$6

    Go 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)*1

    Feed 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)))

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Sep 18, 2022

Ashley 

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.

 

Resources