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

imivan

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.