Forum Discussion

cwhite1918's avatar
cwhite1918
Copper Contributor
Jun 28, 2023
Solved

Splitting one row with multiple columns into multiple rows with single columns

Device: PC/Windows 10 Excel version 2305 Build 16501.20228   Hi all, I'm working with a bunch of consult data, and I need every consult in it's own row, while still keeping the rest of the client...
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jun 28, 2023

cwhite1918 

The preferred option would be to use PowerQuery to unpivot the data, but 365 would allow one to write a problem specific Lambda function to unpivot the table.

= Unpivotλ(
      Table1[[InitialDate]:[4thConsultation]], 
      Table1[[Campus]:[Caseworker]]
  )

where Unpivotλ contains

= LET(
      recordNumber,     SEQUENCE(ROWS(consultationDates)),
      datesNumbered,    IF(consultationDates <> "", recordNumber, NA()),
      datesStacked,     TOCOL(consultationDates, 1),
      numbersStacked,   TOCOL(datesNumbered, 2),
      otherDataStacked, CHOOSEROWS(otherColumns, numbersStacked),
      HSTACK(otherDataStacked, datesStacked)
  )
cwhite1918's avatar
cwhite1918
Copper Contributor
Jun 29, 2023
I'm not sure I'm at an advanced enough level to implement this, but I appreciate the thoroughness. Thank you for the response!

Resources