Multiple column search and Copy
 

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.

Multiple column search and Copy
 

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.

Multiple column search and Copy
 

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?

"Max" wrote:

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.

Multiple column search and Copy
 

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??

"Max" wrote:

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.

Multiple column search and Copy
 

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!

"Sklyn" wrote:

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?

"Max" wrote:

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.

Multiple column search and Copy
 

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,SMAL L($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" wrote:
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??

Multiple column search and Copy
 

Thanks once again! Your help is very much appreciated! :)

"Max" wrote:

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,SMAL L($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" wrote:
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??

Multiple column search and Copy
 

Welcome, and thanks for the feedback
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Sklyn" wrote:
Thanks once again! Your help is very much appreciated! :)