Data of multiple category from single coulmn to multiple column

[roshaero]

Thanks for assisting with solution, i need an option to convert multiple category of data in single column to see in tabular form view without pivot option.

 

[AliGW]

Provide a SMALL dataset in the current format (max. 50 rows) and a manual mock-up of what you'd like it to look like.

 

[p45cal]

Your data looks like a copy/pasted pivot|Values pivot table. If you have access to this pivot table proper, you may be able to get what you need by adding a grand total at the bottom of the table, then double-clicking that grand total. This, if you're lucky, should create a new sheet with the relevant rows of the source data as a plain data table.

 

[roshaero]

Provide a SMALL dataset in the current format (max. 50 rows) and a manual mock-up of what you'd like it to look like.

Please find the excel sheet attached.Original sheet is downloaded from the ERP and required sheet is the format which i required.

 

[roshaero]

Your data looks like a copy/pasted pivot|Values pivot table. If you have access to this pivot table proper, you may be able to get what you need by adding a grand total at the bottom of the table, then double-clicking that grand total. This, if you're lucky, should create a new sheet with the relevant rows of the source data as a plain data table.

Unfortunately, it is not an pivot sheet, it is getting downloaded from an ERP in this format. (Spreadsheet)

 

[p45cal]

In the attached, a table at cell G1 which is more or less what you wanted. It is a Power Query query. Can be updated (refreshed) like a pivot able.
For cross-checking purposes, I've added a pivot table at cell N5 based on this new table. You can directly compare this with your source data and it all seems to line up perfectly.

 

[roshaero]

In the attached, a table at cell G1 which is more or less what you wanted. It is a Power Query query. Can be updated (refreshed) like a pivot able.
For cross-checking purposes, I've added a pivot table at cell N5 based on this new table. You can directly compare this with your source data and it all seems to line up perfectly.

Thank you so much for the same, Can you please share the step by step method to create this as i am having continuous data similar to this case.

 

[p45cal]

Can you please share the step by step method to create this

That's a big one.
You should be able to update the source table then refresh the result table (query). There's a fair amount of hard-coding in the query. It assumes the source data is 5 columns wide, has 6 levels of indentation in column1, the same rows at the top before it gets into the nitty-gritty of the data.
The way to upate the source data is by first deleting the data body of the table, leaving the headers as they are:
Hover over the C of Column1 until it looks like below then click, click again if necessary, to select only the data body of the table:



Then right-click somewhere in what's selected and choose Delete… then Table rows. You should end up with this, which is still a proper table with one blank row. You should be able to see the grab-handle at bottom-right as shown below:



When you paste new data in, you will select cell A2 and Paste.
Now you can go to the result table, right-click and choose Refresh (same with the pivot table if you've still got it).

To visualise what's going on in the query, right-click the results table and choose Table then Edit query…
You should see something like this where on the right are the Applied Steps of the query:



You can click on these from the top down to see what the query does.
I'll go through them:
Source
This is just grabbing the data from the workbook; Table1 in this case.
Added Index
This just adds a column on the right numbering the rows from 0 upwards. I use this later to sort the data back into its original order and to remove specific hard coded rows later on.
Removed Columns1
This removes Column5 which is just the sum of the 3 columns directly to its left. Not needed because it can be calculated for ourselves later.
Added Custom
This is an important one: It adds a new column IndentLevel which counts the number of spaces before text begins in column 1. As you can see, they're all multiples of 5.
Added Custom1
This step looks at the previous column and puts in Date/Team/Country etc. according to the size of the indent. into a new column called Custom.
Pivoted Column
To get this step, I selected the Custom column and chose Pivot Column in the Transform tab of the ribbon and chose as follows in the dialogue box:



This creates a bunch of new columns. One of these doesn't have a header and is removed by the next step: Removed Columns.
Trimmed Text
Many of these new columns have lots of spaces in them so I trimmed them with this step.
Sorted Rows
If you look at the Index column you'll see that they're in a funny order so I sorted by this Index column.
Filtered Rows1
This is where there's a bit of hard coding; I removed rows with index 0, 1 and 2. These rows don't contain useful data.
Filled Down
I selected the "Root", "Date", "Team", "Company", "Country", "Item" columns and filled down. It's vital here that the data is in its original order.
Filtered Rows
Here I filtered on the Indent level for 25 (another hard-coded value). These rows have the deepest level of indentation and contain all the information we need.
Removed Columns2
I removed the column Root.
Changed Type
This converts the strings in the Date column to real dates (the first of the month). This is really good for pivot tables based on this data to work well.
Inserted Sum
This adds the values in columns Column2, Column3 & Column4 in a new column Amt. There is usually only one of these three columns which has any data in it on a given row, so this just serves to condense these three columns to one column.
Removed Columns3
I then remove the 3 columns Column2, Column3 & Column4.
Sorted Rows1
For good measure I sorted on Index again.
Removed Other Columns
This step removes a column or two and puts them in a readable order.

That's the last step and it's this table that gets loaded into the sheet.

 

[roshaero]

That's a big one.
You should be able to update the source table then refresh the result table (query). There's a fair amount of hard-coding in the query. It assumes the source data is 5 columns wide, has 6 levels of indentation in column1, the same rows at the top before it gets into the nitty-gritty of the data.
The way to upate the source data is by first deleting the data body of the table, leaving the headers as they are:
Hover over the C of Column1 until it looks like below then click, click again if necessary, to select only the data body of the table:

View attachment 11631

Then right-click somewhere in what's selected and choose Delete… then Table rows. You should end up with this, which is still a proper table with one blank row. You should be able to see the grab-handle at bottom-right as shown below:

View attachment 11632

When you paste new data in, you will select cell A2 and Paste.
Now you can go to the result table, right-click and choose Refresh (same with the pivot table if you've still got it).

To visualise what's going on in the query, right-click the results table and choose Table then Edit query…
You should see something like this where on the right are the Applied Steps of the query:

View attachment 11633

You can click on these from the top down to see what the query does.
I'll go through them:
Source
This is just grabbing the data from the workbook; Table1 in this case.
Added Index
This just adds a column on the right numbering the rows from 0 upwards. I use this later to sort the data back into its original order and to remove specific hard coded rows later on.
Removed Columns1
This removes Column5 which is just the sum of the 3 columns directly to its left. Not needed because it can be calculated for ourselves later.
Added Custom
This is an important one: It adds a new column IndentLevel which counts the number of spaces before text begins in column 1. As you can see, they're all multiples of 5.
Added Custom1
This step looks at the previous column and puts in Date/Team/Country etc. according to the size of the indent. into a new column called Custom.
Pivoted Column
To get this step, I selected the Custom column and chose Pivot Column in the Transform tab of the ribbon and chose as follows in the dialogue box:

View attachment 11634

This creates a bunch of new columns. One of these doesn't have a header and is removed by the next step: Removed Columns.
Trimmed Text
Many of these new columns have lots of spaces in them so I trimmed them with this step.
Sorted Rows
If you look at the Index column you'll see that they're in a funny order so I sorted by this Index column.
Filtered Rows1
This is where there's a bit of hard coding; I removed rows with index 0, 1 and 2. These rows don't contain useful data.
Filled Down
I selected the "Root", "Date", "Team", "Company", "Country", "Item" columns and filled down. It's vital here that the data is in its original order.
Filtered Rows
Here I filtered on the Indent level for 25 (another hard-coded value). These rows have the deepest level of indentation and contain all the information we need.
Removed Columns2
I removed the column Root.
Changed Type
This converts the strings in the Date column to real dates (the first of the month). This is really good for pivot tables based on this data to work well.
Inserted Sum
This adds the values in columns Column2, Column3 & Column4 in a new column Amt. There is usually only one of these three columns which has any data in it on a given row, so this just serves to condense these three columns to one column.
Removed Columns3
I then remove the 3 columns Column2, Column3 & Column4.
Sorted Rows1
For good measure I sorted on Index again.
Removed Other Columns
This step removes a column or two and puts them in a readable order.

That's the last step and it's this table that gets loaded into the sheet.

Thank you so much.
Now I learned how to do power query.
Much appreciated if you have time to train me in advanced excel. I can pay your fees.