Copy a range if it meet certain condition.

[Avinash916]

I am having problem with copying a range if it meet a certain condition. Please see the file once to understand my problem better.

What I want to achieve from this macro is that I want to copy the name of the movies for which the length of movie is longer than 125 minutes. But I am having problem with it. It also includes my attempt to do it.

Please help me with it. I just started VBA .

Thank You

 

[Khalid NGO]

Hi Avinash
Good day and welcome to the forum

I am sure someone will give you VBA based solution, but if you are okay with formula based solution, you can use this {array formula} in Sheet2:

=IFERROR(INDEX(Sheet1!$B$2:$B$31,SMALL(IF(Sheet1!$D$2:$D$31>125,ROW($A$2:$A$31)-1),ROW(A1))),"")

Enter in A2 and copy down...

{Array formula needs to be entered with
a key combination of Ctrl+Shift+Enter}


You can also use Pivot table, create a helper column with this simple formula:
=D2>125

• Make Pivot table movies column and helper column.
• Report filter = helper column
• Row label = Movies
• Go to report filter and select TRUE

Regards,

 

[Avinash916]

Hi Avinash
Good day and welcome to the forum

I am sure someone will give you VBA based solution, but if you are okay with formula based solution, you can use this {array formula} in Sheet2:

=IFERROR(INDEX(Sheet1!$B$2:$B$31,SMALL(IF(Sheet1!$D$2:$D$31>125,ROW($A$2:$A$31)-1),ROW(A1))),"")

Enter in A2 and copy down...

{Array formula needs to be entered with
a key combination of Ctrl+Shift+Enter}


You can also use Pivot table, create a helper column with this simple formula:
=D2>125

• Make Pivot table movies column and helper column.
• Report filter = helper column
• Row label = Movies
• Go to report filter and select TRUE

Regards,

Thank you for the reply. Actually I know that I can do it from Pivot Table but I was trying to learn VBA macros so I asked for the solution.