Formula to extract data from range

[ByteMarks]

Does anyone have a dynamic array formula to take data, filter it and remove some columns?

On a separate sheet show only the columns with the yellow title and rows where column M is "YES"
The number of rows will vary although the column count is fixed.

I've attached a file with a mock up showing the desired result.

[JohnTopley]

Try

=CHOOSECOLS(VSTACK(Data!A1:N1,FILTER(Data!A2:N17,Data!M2:M17="YES")),3,7,9,10,11,13)

[6StringJazzer]

Voila:

=CHOOSECOLS(FILTER(Data!$A:$N,(Data!$M:$M="YES")+(Data!$M:$M="EIGHT")),3,7,9,10,11,13)

[windknife]

Try this,

=CHOOSECOLS(VSTACK(Data!A1:N1,FILTER(Data!A2:N1000,Data!M2:M1000="YES")),3,7,9,10,11,13)

[ByteMarks]

Thanks guys, really appreciate the responses, all of which of course work

[windknife]

You are welcome.

[6StringJazzer]

I will just mention that I used references to the entire columns because for this particular formula, Excel knows which cells are actually used. In some cases with array formulas, using the entire column would force Excel to evaluate all million+ rows, causing a performance catastrophe. But not in this case.

[ByteMarks]

I wonder, is it possible to sort by Column A and then by Column B in the result as well?

[6StringJazzer]

I was able to do that with a restructuring of the formula

=CHOOSECOLS(VSTACK(Data!$A$1:$N$1,SORT(FILTER(Data!$A:$N,Data!M:M="YES"),{3,7})),3,7,9,10,11,13)

I did the sort on the inside to avoid sorting the headings. This means that the sort index number refer to the original data, not the result

[ByteMarks]

Thank you that's perfect. I was trying to do the sort on the inside which wasn't successful.

[josephteh]

With selected column headers in row 1:
A2=CHOOSECOLS(FILTER(Data!A:N,Data!M:M="YES"),MATCH(A1:F1,Data!A1:N1,0))

[josephteh]

This one better, no limit to number of selected columns: =CHOOSECOLS(FILTER(Data!A:N,Data!M:M="YES"),MATCH(TOROW(1:1,1),Data!A1:N1,0))

[josephteh]

To sort by 1st 2 columns: =SORT(CHOOSECOLS(FILTER(Data!A:N,Data!M:M="YES"),MATCH(TOROW(1:1,1),Data!A1:N1,0)),{1,2},{1,1})

[TMS]

Late to the party, as usual

I tend to do these things step by step, assigning variables to each stage. That way, I can display the variables as I go along to check that I’m getting what I expect. You can always consolidate later but I find the logical build up helps me to check the process.

So, I start with rng, then sorted range, srng, then filtered sorted range, fsrng, etc., each building on the previous one. And, eventually, I bite the bullet and put it all together.

Anyway, for what it's worth:

 =LET(lr,COUNTA(Data!A:A),rng,Data!A2:INDEX(Data!N:N,lr),hdr,Data!A1:N1,srng,SORT(rng,{3,7},{1,1}),fsrng,FILTER(srng,INDEX(rng,,13)="YES"),     CHOOSECOLS(VSTACK(hdr,fsrng),3,7,9,10,11,13))

Note that I have also calculated a last range variable, lr, based on column A to avoid potential full column references.


Based on feedback, this is the corrected version:

=LET(lr,COUNTA(Data!A:A),rng,Data!A2:INDEX(Data!N:N,lr),hdr,Data!A1:N1,srng,SORT(rng,{3,7},{1,1}),fsrng,FILTER(srng,INDEX(srng,,13)="YES"),cc,CHOOSECOLS(VSTACK(hdr,fsrng),3,7,9,10,11,13),cc)

The error was here: FILTER(srng,INDEX(rng,,13)="YES")

[ByteMarks]

@josephteh
Struggling with this one. It seems to be referencing the first row of the Result sheet?

@TMS
I like the idea of breaking it down using LET and getting the row count, but in the results the column EIGHT is a mix of YES and NO when it should be filtered for YES.

[josephteh]

@josephteh
Struggling with this one. It seems to be referencing the first row of the Result sheet?

That's correct. And, you can add in any column headers.

[TMS]

@josephteh
Struggling with this one. It seems to be referencing the first row of the Result sheet?

@TMS
I like the idea of breaking it down using LET and getting the row count, but in the results the column EIGHT is a mix of YES and NO when it should be filtered for YES.

I'll have a look at it. I'm sure it was ok at one point as I had it all matched up against the original expected results

[ByteMarks]

Oh I get it. So you manually add the column headings you want. Thanks for the input.

[ByteMarks]

I put the sort and filter the other way round which seems to have fixed it

=LET(lr,COUNTA(Data!A:A),

rng,Data!A2:INDEX(Data!N:N,lr),

hdr,Data!A1:N1,

frng,FILTER(rng,INDEX(rng,,13)="YES"),

srng,SORT(frng,{3,7},{1,1}),



CHOOSECOLS(VSTACK(hdr,srng),3,7,9,10,11,13))

[TMS]

This is the fixed version:

=LET(lr,COUNTA(Data!A:A),rng,Data!A2:INDEX(Data!N:N,lr),hdr,Data!A1:N1,srng,SORT(rng,{3,7},{1,1}),fsrng,FILTER(srng,INDEX(srng,,13)="YES"),cc,CHOOSECOLS(VSTACK(hdr,fsrng),3,7,9,10,11,13),cc)

Just for fun:

=LET(lr,COUNTA(Data!A:A),rng,Data!A2:INDEX(Data!N:N,lr),hdr,Data!A1:N1,srng,SORT(rng,{3,7},{1,1}),fsrng,FILTER(srng,INDEX(srng,,13)="YES"),cc,CHOOSECOLS(VSTACK(hdr,fsrng),XMATCH(TEXTSPLIT("HAPPY,CHRISTMAS,AND,NEW,YEAR,EIGHT",","),hdr)),cc)

=LET(lr,COUNTA(Data!A:A),rng,Data!A2:INDEX(Data!N:N,lr),hdr,Data!A1:N1,srng,SORT(rng,{3,7},{1,1}),fsrng,FILTER(srng,INDEX(srng,,13)="YES"),cc,CHOOSECOLS(VSTACK(hdr,fsrng),XMATCH(TEXTSPLIT("HAPPY,CHRISTMAS,AND,NEW,YEAR",","),hdr)),cc)

[ByteMarks]

Fantastic.

[TMS]

You're welcome. Thanks for the rep.