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]
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top