Copying sheet with the condition ...

[mlradak]

Getting a NEW Sheet with data from one sheet with the CONDITION....

OLD SHEET
--------------------------------
1 test 4 5 m
2 mladen 3 6 h
3 mladen 6 6 m
4 nikola 6 2 h
5 nikola 2 1 h
6 test 5 3 r
7 test 2 5 e
8 mladen 1 6 m

CONDITION =
------------------------------

RESULTING SHEET (copy of old sheet)
------------------------------------------------
1 test 4 5 m
3 mladen 6 6 m
8 mladen 1 6 m


How can I do this?
Please HELP!
Mlade

 

[Ron de Bruin]

Hi mlradak

Code example
http://www.rondebruin.nl/copy5.htm

This add-in have this option also
http://www.rondebruin.nl/easyfilter.htm

 

[Aladin Akyurek]

If you'd like to run a formula system to extract a conditional list...

Let A29 on Sheet1 (OLD SHEET) house the sample data you provided.

On Sheet2 (NEW SHEET)...

In A3 enter a 0, which is required.

In A4 enter & copy down:

=IF((Sheet1!D2=$B$1),LOOKUP(9.99999999999999E+307,$A$3:A3)+1,"")

In B1 enter: M, the condition.

In B2 enter:

=LOOKUP(9.99999999999999E+307,$A$4:$A$11)

which also calculates the number of records with M.

In B4 enter & copy down:

=IF(ROW()-ROW(B$4)+1<=$B$2,MATCH(ROW()-ROW(B$4)+1,$A$4:$A$11),"")

In C4 enter, copy across to F4 then down:

=IF(N($B4),INDEX(Sheet1!A$2:A$9,$B4),"")