How to filter quickly 2 mutidimensional array?

[jaryszek]

Hi Guys,

i have table:

Topo Value
H1 1
H1 2
H1 3
H2 4
H2 5
H2 6
H3 7
H3 8

and what i want to do to get quickly filter from arrayTable without looping if it possible.

The 1 dim array can be filtered like here:

Please Login or Register  to view this content.

But i am getting error when trying to filter 2 dimensional array. It is possible to do this without loop?

Best,
Jacek

[jindon]

I think I posted similar code before...

Please Login or Register  to view this content.

[jaryszek]

thank you!

Jacek

[jaryszek]

jindon,

one more thing,
if i want to take more than 1 or more columns into array how should i modify the code?

Topo Value Value2
H1 1 a
H1 2 b
H1 3 c
H2 4 d
H2 5 e
H2 6 f
H3 7 g
H3 8 h

Thank you,
Jacek

[jindon]

It should automatically do it according to the number of columns in the list object.

[jaryszek]

Oh right,

thank you!

Jacek

[jaryszek]

Ok problem solved

[jindon]

Please Login or Register  to view this content.

Extract parts from an array using Index method, 2nd arg needs to be 2d array and 3rd should be 1d array.
Evaluate("transpose(row(1:" & .Columns.Count & "))" creates {1,2,3} when columns.count = 3
You can also change the order when you need to
e.g

Please Login or Register  to view this content.

reverse order

[jaryszek]

thank you very much for your explanation, great code!

Jacek

[jaryszek]

Hi Guys,

i have one more question.

I would like to add second column to my table as condition.

So if range = H2 and range = 4, how can i filter?
So i would like totake only these 2 rows:

H2 4 a
H2 4 b

Please help,

Best,
Jacek

[Bo_Ry]

Please try

Please Login or Register  to view this content.

[jindon]

That's a question of how to create an array for the condition of 2nd argument in Index method.

Like ordinary If function in worksheet + means Or, * means And

If((A1="X")*(B1="Y"),True,False)
A1="X" 1 when true, 0 when false
B1 ="Y" 1 when true, 0 when false

So it evaluates 0 as False, other than 0 as True.
1 + 0, 0 + 1 both true in Or condition
1*1 is the only True in And condition

[jaryszek]

O wow thank you Guys!!

It is solved and understood!
Best,
Jacek

[jaryszek]

Ok i found one more issue.

When you do not have active tab --> function is not working for me. I mean x is empty.
Hmm i do not know if i can use this method because i am refering to hidden sheet.

Best,
Jacek

[jindon]

You need qualify the Evaluate method like range

Please Login or Register  to view this content.

[jaryszek]

oo wow.
How this is working and why this is working ?

Best,
Jacek

[jindon]

Evalueate method has 2 types
Applucation and worjsheet.
Worksheet.evaluate is the fastest.
Application.evaluate assumes the cell referene in active sheet.

From phone.

[jaryszek]

Thank you.

jindon so this parent enforces to get application.evaluate because worksheet fails?

Best,
Jacek

[jindon]

Parent in this case refers to Sheet1.
So, equivalent to
Sheets("Sheet1").evaluate
So that all the address in formula refers to
Sheet1!A1:A10 etc.

[jaryszek]

ok great!

So we are using fastest evaluate

Thank you,
Best,
Jacek