Split of columns and rows based on delimiter

[sakshim]

I want to split values in my columns to different rows, I have used TEXT to COLUMN Tool but that can split only 1 column at time. I have a below table for which I want 1:1 split of  Columns DEPT, Description , DEPT ID & Dept Value :

SNO	Business Date	Code	Dept description	Dept ID	DEPT Value	Status
1001	17-Mar-22	ED/N0000	GCRS Account, Facilities	238,012	40,6	Active
1002	17-Mar-22	ED/N0000	Facilities, Transport, HR, IT	012, 123, 435,321	6,23,24,54	Active
1003	17-Mar-22	ED/N0000	Corp finance, Operations	321,543	65,23	InACTIVE
1004	17-Mar-22	ED/N0000	GRC,IT	0	23,24	ACTIVE

the output should look like:

SNO	Business Date	Code	Dept description	Dept ID	DEPT Value	Status
1001	17-Mar-22	ED/N0000	GCRS Account	238	40	Active
1001	17-Mar-22	ED/N0000	Facilities	012	6	Active
1002	17-Mar-22	ED/N0000	Facilities	012	6	Active
1002	17-Mar-22	ED/N0000	Transport	123	23	Active
1002	17-Mar-22	ED/N0000	HR	435	24	Active
1002	17-Mar-22	ED/N0000	IR	321	54	Active
1003	17-Mar-22	ED/N0000	Corp finance	321	65	InACTIVE
1003	17-Mar-22	ED/N0000	Operations	543	23	InACTIVE
1004	17-Mar-22	ED/N0000	GRC	0	23	Active
1004	17-Mar-22	ED/N0000	IT	0	24	Active

[AngelosPachis]

Hi @sakshim ,

Here's one way you can do this

Cheers,

Angelos

[DavidP · Accepted solution]

Hi @sakshim 

I followed a very similar approach to @AngelosPachis, with the main difference that I used a Tile tool instead of Multi Row to create the individual row IDs needed for the CrossTab tool.

The Tile tool is often overlooked. The way I often use it is like a Record ID with Group By functionality. It's a handy tool to have in your back pocket.

[sakshim]

Thankyou both this had been really helpful