Stacking Columns

[MStark]

Our system exports employees that work double shifts on 1 line. I need help spliting the data and stacking it so each shift is a separate row. Can anyone help with this?

Export Data:

Name	Position	Work Date	Schedule Facility	Scheduled Department	Scheduled Position	Scheduled Shift	Scheduled Unit	Scheduled HoursX
John Doe	CNA	2/17/2023	Test,Test	Nursing,Nursing	Rep,Rep	7a-3p,3p-7p	Second Floor,Float	8.0,4.0
John Doe	CNA	2/18/2023	Test	Nursing	Rep	7a-3p	Second Floor	8

I need the data stacked like this:

Name	Position	Work Date	Schedule Facility	Scheduled Department	Scheduled Position	Scheduled Shift	Scheduled Unit	Scheduled HoursX
John Doe	CNA	2/17/2023	Test	Nursing	Rep	7a-3p	Second Floor	8
John Doe	CNA	2/18/2023	Test	Nursing	Rep	3p-7p	Float	4
John Doe	CNA	2/18/2023	Test	Nursing	Rep	7a-3p	Second Floor	8

Any advise?

Thanks in advance for your time!!

[HotChilli · Accepted solution]

Here's a way to do it via the interface.

Duplicate the table twice (so that we can keep the original table as a .

In each copy we have 3 fixed columns and other columns containing related values i.e. all the values before the comma in each column need to be combined to make a row. All the values after the comma should be combined too.

So let's make each 'table copy' produce the rows we want before recombining afterwards .  We can do this by: (this describes the steps in each table copy)

1. Select the first 3 columns.

2. Unpivot other columns.

3. Split the Value column (on comma)

4. Remove the column we don't want in each table i.e. 'Table Copy1' - remove the column holding text before the comma (Value.1)
    'Table Copy2' - remove the column holding text after the comma (Value.2)

5. Rename the column left behind - 'Value'

6. Pivot the Attribute column, using Value in the Values section and 'Do not aggregate' in the advanced section.

7. Append the 2 tables together (this is now possible because both tables have the same structure)

---

There is an intermediate step in one of the table copies where the null entries in Value should be filtered out.  We don't need those rows so get rid of them before the Pivot.

--

See if you can work your way through that and get back to me if you are stuck.

[MStark]

This Worked! Amazing!

Really appreciate your Help! Thanks!!

[HotChilli]

Can you explain why the middle row in the desired table has a date of 18th? Is this what you want or is there an error there?

[MStark]

That is an error. It should be 2/17