Split one table to two

[imivan]

Company code
GL Account Group B GL Account
Local Currency Beginning Balance - DC Period Activity - DC
JAN-2023 TO MAR-2023 Ending Balance - DC Beginning Balance - GC Period Activity - GC
JAN-2023 TO MAR-2023 Ending Balance - GC Beginning Balance - LC Period Activity - LC
JAN-2023 TO MAR-2023 Ending Balance - LC Beggining Rate
(DC to GC) Ending Rate
(DC to GC) Beginning Rate
(DC to LC) Ending Rate
(DC to LC)

Company Code Account Assignment Account Name Document Number Document type Document Date Document Type Description Posting Date Document currency Amount in doc. curr. Local Currency Amount in local currency Local currency 2 Amount in loc.curr.2 Division Business Unit Profit Center Location code Subaccount Trading partner Functional Area WBS element Tax Code Clearing date Clearing Document Text Reference User Name

So above are the headers of an excel file in the same sheet I received which the data should be split into two different tables. I was using range before my superior rejects as the rows of data is ever-changing and this is not the best way to do it. Is there any other method to split them?

The first table should has the following headers:

Company code
GL Account Group B GL Account
Local Currency Beginning Balance - DC Period Activity - DC
JAN-2023 TO MAR-2023 Ending Balance - DC Beginning Balance - GC Period Activity - GC
JAN-2023 TO MAR-2023 Ending Balance - GC Beginning Balance - LC Period Activity - LC
JAN-2023 TO MAR-2023 Ending Balance - LC Beggining Rate
(DC to GC) Ending Rate
(DC to GC) Beginning Rate
(DC to LC) Ending Rate
(DC to LC)

And the second one should has the followings:

Company Code Account Assignment Account Name Document Number Document type Document Date Document Type Description Posting Date Document currency Amount in doc. curr. Local Currency Amount in local currency Local currency 2 Amount in loc.curr.2 Division Business Unit Profit Center Location code Subaccount Trading partner Functional Area WBS element Tax Code Clearing date Clearing Document Text Reference User Name

[binuacs]

@imivan Is it that the column names in the first table will be always the same or the number of columns will be the same? I mean to say the first 10 columns always go to the first table and rest of the columns should go to the second table?

[geraldo]

@imivan 

If you can send the excel with the columns to facilitate the elaboration of a workflow example for the division

[geraldo]

@imivan 

A simple example. as the columns have spaces and characters (the Alteryx tool does the replacement for "_". but it is an example of the construction

[imivan · Accepted solution]

Sorry for the late response, I actually figured out a way myself and I was pretty sick and didn't online, below is the way I do it:

I created two "Cross Tab" tools and select the columns that I need for both tables respectively then create a temporary unneeded column. The tables will be eventually created with the desired headers then we can use "Select" to deselect the unneeded column.