Extract from multiple worksheets

[trebor1956]

Hi, I have 5 worksheets (loop 1, loop 2, loop 3, loop 4 and loop 5) with similar info but in differing order. I would like to for instance extract every occurrence (whole line) where column D contains "MCP" from each of the 5 worksheets and put into a new worksheet called MCP listing all occurrences of "MCP". Other than "MCP" there are 4 other definitions (Smoke, Heat, Sounder, I/O) I would like to extract to their own new worksheets.
Below is a copy of the loop 1 w/sheet as an example. I had a go at Hlookup but struggled.

Loop	Address	Location	Type	Zone
1​	0001​	Main entrance	MCP	1​
1​	0002​	Main entrance	Smoke	1​
1​	0003​	Entrance doors	I/O	1​
1​	0004​	Family room	Sounder	2​
1​	0005​	Family room	Smoke	2​

 

[sandy666]

Use Power Query
Append all source tables
Create References and Filter for each type
Load each Reference to separate sheets

 

[sandy666]

here is an example

s666-PQ-Loop.xlsx

Microsoft Excel Workbook

1drv.ms

 

[trebor1956]

Thanks, I don't currently have Power Query available so I have asked the IT dept. if its possible to have it. If the answer is no can you think of an alternative method please?

 

[trebor1956]

I now have power query available but no idea how to use it

 

[sandy666]

first update your profile about Excel version and OS

second: it's hard to explain how to use Power Query if you know nothing
would be better if you will read

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

 

[sandy666]

anyway here is basic M code for all 5 source tables
M is a name of Power Query language

let
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Table")),
    #"Expanded Content" = Table.ExpandTableColumn(#"Filtered Rows", "Content", {"Loop", "Address", "Location", "Type", "Zone"}, {"Loop", "Address", "Location", "Type", "Zone"})
in
    #"Expanded Content"

and one for the result (the rest is similar)

let
    Source = Query1,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Type] = "I/O"))
in
    #"Filtered Rows"