Forum Discussion
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 information. Essentially I'm trying to split one row with multiple columns into multiple rows with single columns, while maintaining the data in the rest of the columns. So, turning Table 1
into Table 2.
I'm trying to do this without VBA, or with very simple beginner VBA, since I don't know any VBA.
Does anyone have any insight?
Thanks so much!
- Thank you, this worked! Much appreciated.
- Glad to hear you have solved the problem.
You are welcome.
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) )- I'm not sure I'm at an advanced enough level to implement this, but I appreciate the thoroughness. Thank you for the response!
- Thank you!
