The fastest way to get a range with conditions into an array

capson

Board Regular
Joined
Jul 9, 2010
Messages
107
Hello,

If I have a Large range and I want to create an array from it but with conditions, I am trying to find the fast way to do this, possibly Advanced filter? (I have tried but no luck)

there are probably many ways but the fastest? or pretty darn fast at least

My ranges are dynamic but always resemble below, in this case, the criteria for exclusion is if C2=C3=C4 = Empty and this is representative of the criteria for exclusion I always have


Thank you for any thoughts on this

The range
Code:
[TABLE="width: 908"]
<tbody>[TR]
[TD]C1[/TD]
[TD]C2[/TD]
[TD]C3[/TD]
[TD]C4[/TD]
[TD]C5[/TD]
[/TR]
[TR]
[TD]elem-17718[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff1[/TD]
[/TR]
[TR]
[TD]elem-17719[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff2[/TD]
[/TR]
[TR]
[TD]elem-17720[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff3[/TD]
[/TR]
[TR]
[TD]elem-17721[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff4[/TD]
[/TR]
[TR]
[TD]elem-17722[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff5[/TD]
[/TR]
[TR]
[TD]elem-17723[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff6[/TD]
[/TR]
[TR]
[TD]elem-17724[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff7[/TD]
[/TR]
[TR]
[TD]elem-17725[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff8[/TD]
[/TR]
[TR]
[TD]elem-17726[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff9[/TD]
[/TR]
[TR]
[TD]elem-17727[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff10[/TD]
[/TR]
[TR]
[TD]elem-17728[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff11[/TD]
[/TR]
[TR]
[TD]elem-17729[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff12[/TD]
[/TR]
[TR]
[TD]elem-17730[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff13[/TD]
[/TR]
[TR]
[TD]elem-17731[/TD]
[TD]Economic Development[/TD]
[TD]Civic Engagement, Community Building[/TD]
[TD]Government and Public Administration[/TD]
[TD]stuff14[/TD]
[/TR]
[TR]
[TD]elem-17732[/TD]
[TD]Native Nations[/TD]
[TD][/TD]
[TD]Human Rights[/TD]
[TD]stuff15[/TD]
[/TR]
[TR]
[TD]elem-17733[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff16[/TD]
[/TR]
[TR]
[TD]elem-17734[/TD]
[TD]Human Rights[/TD]
[TD]Civic Engagement, Community Building[/TD]
[TD]Education[/TD]
[TD]stuff17[/TD]
[/TR]
[TR]
[TD]elem-17735[/TD]
[TD]Education[/TD]
[TD]Leadership Development[/TD]
[TD]Civic Engagement, Community Building[/TD]
[TD]stuff18[/TD]
[/TR]
[TR]
[TD]elem-17736[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff19[/TD]
[/TR]
[TR]
[TD]elem-17737[/TD]
[TD]Education[/TD]
[TD]Health[/TD]
[TD][/TD]
[TD]stuff20[/TD]
[/TR]
[TR]
[TD]elem-17738[/TD]
[TD]Leadership Development[/TD]
[TD]Social Entrepreneurship[/TD]
[TD]Civic Engagement, Community Building[/TD]
[TD]stuff21[/TD]
[/TR]
[TR]
[TD]elem-17739[/TD]
[TD]Human Services[/TD]
[TD]Social Entrepreneurship[/TD]
[TD]Civic Engagement, Community Building[/TD]
[TD]stuff22[/TD]
[/TR]
[TR]
[TD]elem-17740[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]stuff23[/TD]
[/TR]
[TR]
[TD]elem-17741[/TD]
[TD]Arts, Culture, and Humanities[/TD]
[TD]Native Nations[/TD]
[TD]Economic Development[/TD]
[TD]stuff24[/TD]
[/TR]
</tbody>[/TABLE]
The array wanted
Code:
[TABLE="width: 846"]
<tbody>[TR]
[TD]C1[/TD]
[TD]C2[/TD]
[TD]C3[/TD]
[TD]C4[/TD]
[TD]C5[/TD]
[/TR]
[TR]
[TD]elem-17731[/TD]
[TD]Economic Development[/TD]
[TD]Civic Engagement, Community Building[/TD]
[TD]Government and Public Administration[/TD]
[TD]stuff14[/TD]
[/TR]
[TR]
[TD]elem-17732[/TD]
[TD]Native Nations[/TD]
[TD][/TD]
[TD]Human Rights[/TD]
[TD]stuff15[/TD]
[/TR]
[TR]
[TD]elem-17734[/TD]
[TD]Human Rights[/TD]
[TD]Civic Engagement, Community Building[/TD]
[TD]Education[/TD]
[TD]stuff17[/TD]
[/TR]
[TR]
[TD]elem-17735[/TD]
[TD]Education[/TD]
[TD]Leadership Development[/TD]
[TD]Civic Engagement, Community Building[/TD]
[TD]stuff18[/TD]
[/TR]
[TR]
[TD]elem-17737[/TD]
[TD][/TD]
[TD]Health[/TD]
[TD][/TD]
[TD]stuff20[/TD]
[/TR]
[TR]
[TD]elem-17738[/TD]
[TD]Leadership Development[/TD]
[TD]Social Entrepreneurship[/TD]
[TD]Civic Engagement, Community Building[/TD]
[TD]stuff21[/TD]
[/TR]
[TR]
[TD]elem-17739[/TD]
[TD]Human Services[/TD]
[TD]Social Entrepreneurship[/TD]
[TD]Civic Engagement, Community Building[/TD]
[TD]stuff22[/TD]
[/TR]
[TR]
[TD]elem-17741[/TD]
[TD]Arts, Culture, and Humanities[/TD]
[TD]Native Nations[/TD]
[TD]Economic Development[/TD]
[TD]stuff24[/TD]
[/TR]
[TR]
[TD]elem-17748[/TD]
[TD]Arts, Culture, and Humanities[/TD]
[TD]Civic Engagement, Community Building[/TD]
[TD]Environment[/TD]
[TD]stuff30[/TD]
[/TR]
[TR]
[TD]elem-17754[/TD]
[TD][/TD]
[TD]Civic Engagement, Community Building[/TD]
[TD]Economic Development[/TD]
[TD]stuff35[/TD]
[/TR]
[TR]
[TD]elem-17756[/TD]
[TD]Education[/TD]
[TD]Arts, Culture, and Humanities[/TD]
[TD][/TD]
[TD]stuff37[/TD]
[/TR]
</tbody>[/TABLE]
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,844
Office Version
2010
Platform
Windows
One way would be to add a helper column with the formula (assuming "C1" is in A1): =COUNTA(B2:D2) copied down to cover all your data. Then filter using the helper column and the Number Filter: "Greater Than" and enter 0.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,462
Messages
5,468,791
Members
406,609
Latest member
cocobeans

This Week's Hot Topics

Top