Multiple column search and Copy

[Sklyn]

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.

 

[Max]

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]

Thanks a million max!!

The first sheet worked a charm!
However my Disposed sheets are not
the first copy I made returned only 1 result on row2 and not once I copied
it down when there should have been about 10 results and now I can't even get
that...

Column CC is now CD as I added a column, and Branch 1 is *Remote Health
(just so you know exactly what I'm using)

I think I am now having a very basic problem but no idea what it is.
I even tried re-entering the formula instead of copy paste..
I also copied Disposed from the master sheet to make sure there was no
difference.

All I had to do was change Current to Disposed in column A, correct?

 

[Sklyn]

oh, also there are quite a few empty cells in the master sheet, these are all
returning 0 or false dates (00/01/1900), is there a way to show an empty cell
in these cells??

 

[Sklyn]

Nevermind this,, got it sorted.. I just hadn't copied down the rows far
enough..
Told you it was something basic..

Still need help with the empty cells though..

Thanks so much Max!

 

[Max]

You could trap the index returns for zeros to instead return blanks: "",
albeit this trap will increase the recalc intensity. Performance may be
affected.

For this part

Put in B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(x!A:A,SMALL($A:$A,ROWS($1:1))))

Use instead in B2, copy across/fill down:
=IF(ROWS($1:1)>COUNT($A:$A),"",IF(INDEX(x!A:A,SMALL($A:$A,ROWS($1:1)))=0,"",INDEX(x!A:A,SMALL($A:$A,ROWS($1:1)))))

If you just want to mask it for neater appearances, try switching off zeros
display via the Tools > Options > View tab (uncheck the option: zero values).
The setting is sheet specific, so you need to repeat it on all the 4 extract
sheets.

Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

 

[Sklyn]

Thanks once again! Your help is very much appreciated!

 

[Max]

Welcome, and thanks for the feedback
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik