First, name your master sheet as simply: x
Then in a new sheet,
this set-up will auto-extract source lines satisfying col BN = Branch 1, col
CC = Current, with all lines neatly packed at the top
Put in A2:
=IF(AND(x!BN2="Branch 1",x!CC2="Current"),ROW(),"")
This is the criteria to flag lines as mentioned
Put in B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(x!A:A,SMALL($ A:$A,ROWS($1:1))))
Copy B2 across to CD2 (to extract the entire source line). Select A2:CD2,
fill down to cover the max expected extent of source data. Cols B to CD will
return the required results, neatly packed at the top.
Dress the sheet up, then make another 3 copies of it, where in each copy,
you'd just need to tweak the criteria formula in A2 to suit, then copy A2
down to have the corresponding results returned, viz:
Extract lines satisfying col BN = Branch 1, col CC = Disposed
In A2, copied down:
=IF(AND(x!BN2="Branch 1",x!CC2="Disposed"),ROW(),"")
Extract lines satisfying col BN < Branch 1, col CC = Current
In A2, copied down:
=IF(AND(x!BN2<"Branch 1",x!CC2="Current"),ROW(),"")
Extract lines satisfying col BN < Branch 1, col CC = Disposed
In A2, copied down:
=IF(AND(x!BN2<"Branch 1",x!CC2="Disposed"),ROW(),"")
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Sklyn" wrote:
I'm using Excel 2003.How do i search a sheet for records matching criteria in
2 columns and copy all data from the resulting rows into a new sheet?
Of the remaining entries I need to sort them by 1 column and also transfer
the information into new sheets..
the master sheet is filled to CC160 I need to sort by Column CC (current or
Disposed) then search for 1 particular branch in column BN, which needs to be
catalogued seperately.
Basically I need to end up with 4 sheets retrieving data from the master,
Branch 1 Current, Branch 1 Disposed, All Other Branches Current, All Other
Branches Disposed.
So something like:
IF MasterSheet!CC2=Current & MasterSheet!BN2=Branch 1 then copy A2:CC2
and
IF MasterSheet!CC2=Current & MasterSheet!BN2"not equal to"Branch 1 then copy
A2:CC2
Obviously I can substitue Current for Disposed as they are the only 2
entries in this column.
I also need to allow for new entries into the master sheet to automatically
fill in the other sheets, without filling them with 0 values.
On the new sheets I also have many columns hidden.
I know I can simply copy & paste the selected data easily but ultimately I
want to transfer the info to a new workbook to allow other people to view the
restricted information.
Thanks in advance for any help you can offer.