Lookup Row and move to another sheet

[7839matth]

Good morning,

I am hoping somebody could assist me with an formula i would like to write in order to do the following:

If any row in column AL equals 1

Move that entire row to another sheet (Sheet 1 for this example.)

As a side note; there may be large gaps between rows where column AL contains 1, can these gaps be removed so when the rows move to Sheet 1 they are displayed in a compact manner?

Thank you for any help in advance!

 

[RasGhul]

Hi 7839,

Copy this formula across the entire row & drag down as far as needed to return all possible rows.


This array formula needs to be entered with CTRL+SHIFT+Enter;

=IF(ROWS($A$1:A1)>COUNTIF(Sheet2!$AL$1:$AL$10,1),"",INDEX(Sheet2!A$1:A$10,SMALL(IF(Sheet2!$AL$1:$AL$10=1,ROW(Sheet2!$A$1:$A$10)-ROW(Sheet2!$A$1)+1),ROWS($A$1:A1))))

If done successfully the formula will show curly brackets at each end eg;

{=IF(ROWS($A$1:A1)>COUNTIF(Sheet2!$AL$1:$AL$10,1),"",INDEX(Sheet2!A$1:A$10,SMALL(IF(Sheet2!$AL$1:$AL$10=1,ROW(Sheet2!$A$1:$A$10)-ROW(Sheet2!$A$1)+1),ROWS($A$1:A1))))}