Creating a Data List in Wkbk B from Many Data Sets in Wkbk A

[KC E]

Hello,

I am using Excel 2013.

I'm not sure that this needs to involve VBA, but I am receiving Excel wksheets from clients who have many data sets randomly scattered across their wksheet, and I have to create a structured data list from those data sets in another workbook called Analysis.

I have attached a workbook giving a sample of the how the data sets are sent to me, and then how I have to structure it into a data list.

I would appreciate your help. I have to do this task repeatedly and there has to be an easier way than copy and paste. I can't see a formula that would help.

I thought about making each one of those many data sets into tables and then using a macro to stack them on top of each other into a list like below, but that would mean having to make a lot of tables. I could do this if I had to, though.

Thank you in advance.

The final structured data will have the category type in bold on top and the items that make up that category below it--all in a single column like this:
Fruit
Apple
Orange
Banana
Etc
Tree
Cherry
Oak
Elm
Etc
Etc

 

[AlanSidman]

Using Power Query/Get and Transform you can complete this task quite quickly. Here is the Mcode and I have attached the file.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type any}, {"Column4", type any}, {"Column5", type text}, {"Column6", Int64.Type}, {"Column7", type any}, {"Column8", type text}, {"Column9", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column2", "Column3", "Column4", "Column6", "Column7", "Column9"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Column1] <> null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Column1"}),
    #"Appended Query" = Table.Combine({#"Removed Other Columns", Column5, Column8})
in
    #"Appended Query"

 

[KC E]

Thank you very much. I tried to do this by going through each step manually in the Query Editor and I got to the last step

    #"Appended Query" = Table.Combine({#"Removed Other Columns", Column5, Column8})

And I couldn't do that on the Ribbon. I selected 'Append Query to New' but I could only append Table 1 to Table 1--I didn't have access to the Removed Other Columns 5 and 8.

Can you tell me how to append Table 1 to the Removed Other Columns 5 and 8 into a new Query using the buttons in the Query Editor Ribbon? Or is that just done through Mcode or appending columns 5 and 8 separately in Power Query?

Also, I'm sorry, but I forgot to include the Amounts in the 2nd column of each data set in my original post. Could you tell me how to append a 2nd column to the right of the column with the item names in Power Query (just the append part), either manually, if it can be done manually, or the line of Mcode to append that?

Thank you very much for your help.

 

[AlanSidman]

Need to convert the list (columns ) to tables. then you can append each to table 1 before you delete them.

 

[KC E]

Thank you very much. I realized too that I could go through similar steps with Table 1 a second time but remove the columns with the text, leaving the Amounts to use in the 2nd column.

I appreciate your help. God bless you.