How to show a list view of columns based off another sheet / alternative to Pivot table?

[creative999]

Hi
I have a sheet with 15 columns and ~5000 rows.
On a separate sheet i need to show specific columns based on a criteria. For example, Where A:A=Machinery and B:B=Metal. The columns to be shown will always be the same, A:A, B:B, F:F, H:H, Z:Z.
I tried using pivot tables but the data changes often enough which requires the pivot table to be refreshed for everyone who has it open.

Any suggestions?

 

[wjhladik]

=filter(choosecols(sheet1!a1:Z5000,1,2,6,8,26),(sheet1!a1:a5000="Machinery")*(sheet1!b1:b5000="Metal),"")

 

[creative999]

=filter(choosecols(sheet1!a1:Z5000,1,2,6,8,26),(sheet1!a1:a5000="Machinery")*(sheet1!b1:b5000="Metal),"")

Thank you so much.
Is it possible to use a wildcard. Ive tried the following but get no results:

=filter(choosecols(sheet1!a5:Z5000,1,2,6,8,26),(sheet1!a5:a5000="Machinery")*(sheet1!b1:b5000="*"&$A$1&"*"),""

 

[Domenic]

Try the following...

=FILTER(CHOOSECOLS(Sheet1!A1:Z5000,1,2,6,8,26),(Sheet1!A1:A5000="Machinery")*(ISNUMBER(SEARCH($A$1,Sheet1!B1:B5000))),"")

Hope this helps!

 

[creative999]

Thank you so much. Works perfectly.
Last question. I’ve sorted this based on the first column. Is it possible to sort based on column 1 then column 2?

 

[Domenic]

Try the following...

=LET(
    d, CHOOSECOLS(Sheet1!A1:Z5000, 1, 2, 6, 8, 26),
    f, FILTER(
        d,
        (CHOOSECOLS(d, 1) = "Machinery") * (ISNUMBER(SEARCH($A$1, CHOOSECOLS(d, 2))))
    ),
    s, SORT(f, 2, 1),
    s
)

Hope this helps!

 

[creative999]

Thanks, but unsure what the letters represent.

 

[Domenic]

Those letters are simply variable names, where each of those variables get assigned a value so that a final calculation can be made using one or more or all of the vairables. Have a look at Microsoft Help for a more detailed explanation of the LET function.