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

[cwhite1918]

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!

[#2]

@cwhite1918 

The keyword for your web search is "unpivot".

[#3]

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

[#4 · Accepted solution]

https://answers.microsoft.com/en-us/msoffice/forum/all/massive-formatting-data-issue/e7b87b0b-f8a0-4...

[#5]

Thank you!

[#6]

I'm not sure I'm at an advanced enough level to implement this, but I appreciate the thoroughness. Thank you for the response!

[#7]

Thank you, this worked! Much appreciated.

[#8]

Glad to hear you have solved the problem.
You are welcome.