Denormalization of data

[PB_DEV10]

Hello Gurus

I have an excel file as an input with project data. The data shows how many departments have worked on the project. The raw data looks like this:

Project name	Project description	Division	Department	Start date	End date
Cash accounting	Companies that are not obliged to deliver annual accounts (Accounts Act) must be able to generate annual accounts themselves based on bank transactions.	Division IT and digital; Division Management and consultancy; Division bank housing purchase and business development	Digital customer services; Real Estate Management; ABC Bank	1/11/2021	1/2/2022

Now as you can see the 3 divisions worked on the project: Division IT and digital, Division Management and consultancy and Division bank housing purchase and business development (seperated by semicolon) and their corresponding departments also seperated by semicolon.



I have division table already in my worksheet:

Division_PK	Division
1	Division IT and digital
2	Division Management and consultancy
3	Division bank housing purchase and business development

And we have table also for department which connects to division.

Department_PK	Department	Division_FK
1	Digital customer services	1
2	Real Estate Management	2
3	ABC Bank	3

The desired output I need for this data is:

Project name	Project description	Division	Department
Cash accounting	Companies that are not obliged to deliver annual accounts (Accounts Act) must be able to generate annual accounts themselves based on bank transactions.	Division IT and digital	Digital customer services
Cash accounting	Companies that are not obliged to deliver annual accounts (Accounts Act) must be able to generate annual accounts themselves based on bank transactions.	Division Management and consultancy	Real Estate Management
Cash accounting	Companies that are not obliged to deliver annual accounts (Accounts Act) must be able to generate annual accounts themselves based on bank transactions.	Division bank housing purchase and business development	ABC Bank

I dont know how to do this. Can someone help?

 

[SanjayGMusafir]

Project name	Project description	Division	Department	Start date	End date
Cash accounting	Companies that are not obliged to deliver annual accounts (Accounts Act) must be able to generate annual accounts themselves based on bank transactions.	Division IT and digital; Division Management and consultancy; Division bank housing purchase and business development	Digital customer services; Real Estate Management; ABC Bank	1/11/2021	1/2/2022

Now as you can see the 3 divisions worked on the project: Division IT and digital, Division Management and consultancy and Division bank housing purchase and business development (seperated by semicolon) and their corresponding departments also seperated by semicolon.

Simply in the above table Split columns with headers Division and Department - Using Text to Column and semicolon as delimiter
Then play with data the way you want to play