I wish to filter results from one sheet onto a second sheet.
I have a data table containing 13 columns, but only columns 1,2 & 4 are
needed in the filtered results.
Column 1 contains either "In" or "Out". Col.2 a name, Col.4 a location.

I wish to place on sheet 2 all records shown as "Out".

I've tried the VLOOKUP but guess I'm not getting the right parameters.

Help appreciated.
Thanks.

PS - I'm working with Excel 2007, but the workbook has to be compatible with
2003.
--
Wercs

Hi,

Suppose the heading of columns 1,2 and 4 on sheet 1 are A,B and C. Please
perform the following steps

1. Assign a column heading to all the columns in sheet 1, if not already
done so. Say the heading are A-M
2. Now type A in a cell below the data, say A25
3. In cell A26, type Out
4. In sheet 2, type A,B and D in B3,C3 and D3.
5. Now click on cell B4 on sheet 2 and go to Data Filter Advance Filter;
6. Select the radio button for "Copy to another location";
7. In the list box, please select the range on sheet 1 (Please select the
header row as well);
8. In the Criteria box, select the range A25:A26 of sheet 1;
9. In the copy to box, select B3:D100 of sheet 2;
10. Click on OK

Hope this helps.



--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Wercs" wrote in message
...
I wish to filter results from one sheet onto a second sheet.
I have a data table containing 13 columns, but only columns 1,2 & 4 are
needed in the filtered results.
Column 1 contains either "In" or "Out". Col.2 a name, Col.4 a location.

I wish to place on sheet 2 all records shown as "Out".

I've tried the VLOOKUP but guess I'm not getting the right parameters.

Help appreciated.
Thanks.

PS - I'm working with Excel 2007, but the workbook has to be compatible
with 2003.
--
Wercs

Hi there,

Good work. This is exactly what's needed.

Thanks a lot!

Regards,
--
Wercs.


"Ashish Mathur" wrote in message
...
Hi,

Suppose the heading of columns 1,2 and 4 on sheet 1 are A,B and C. Please
perform the following steps

1. Assign a column heading to all the columns in sheet 1, if not already
done so. Say the heading are A-M
2. Now type A in a cell below the data, say A25
3. In cell A26, type Out
4. In sheet 2, type A,B and D in B3,C3 and D3.
5. Now click on cell B4 on sheet 2 and go to Data Filter Advance
Filter;
6. Select the radio button for "Copy to another location";
7. In the list box, please select the range on sheet 1 (Please select the
header row as well);
8. In the Criteria box, select the range A25:A26 of sheet 1;
9. In the copy to box, select B3:D100 of sheet 2;
10. Click on OK

Hope this helps.



--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Wercs" wrote in message
...
I wish to filter results from one sheet onto a second sheet.
I have a data table containing 13 columns, but only columns 1,2 & 4 are
needed in the filtered results.
Column 1 contains either "In" or "Out". Col.2 a name, Col.4 a location.

I wish to place on sheet 2 all records shown as "Out".

I've tried the VLOOKUP but guess I'm not getting the right parameters.

Help appreciated.
Thanks.

PS - I'm working with Excel 2007, but the workbook has to be compatible
with 2003.
--
Wercs

Just a point:

When data in sheet 1 changes from In to Out (or vice versa), how can the
filtered results on sheet 2 be similarly updated automatically?

Rgs.



"Ashish Mathur" wrote in message
...
Hi,

Suppose the heading of columns 1,2 and 4 on sheet 1 are A,B and C. Please
perform the following steps

1. Assign a column heading to all the columns in sheet 1, if not already
done so. Say the heading are A-M
2. Now type A in a cell below the data, say A25
3. In cell A26, type Out
4. In sheet 2, type A,B and D in B3,C3 and D3.
5. Now click on cell B4 on sheet 2 and go to Data Filter Advance
Filter;
6. Select the radio button for "Copy to another location";
7. In the list box, please select the range on sheet 1 (Please select the
header row as well);
8. In the Criteria box, select the range A25:A26 of sheet 1;
9. In the copy to box, select B3:D100 of sheet 2;
10. Click on OK

Hope this helps.



--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Wercs" wrote in message
...
I wish to filter results from one sheet onto a second sheet.
I have a data table containing 13 columns, but only columns 1,2 & 4 are
needed in the filtered results.
Column 1 contains either "In" or "Out". Col.2 a name, Col.4 a location.

I wish to place on sheet 2 all records shown as "Out".

I've tried the VLOOKUP but guess I'm not getting the right parameters.

Help appreciated.
Thanks.

PS - I'm working with Excel 2007, but the workbook has to be compatible
with 2003.
--
Wercs

Hi,

the Advanced Filter command is not a formula, so the second sheet will not
update automatically. You can rerun the filter command when the data changes
or you can record a macro that reruns the command for you.

If you need it to have a formula approach, its going to be fairly
complicated, so hopefully you can live with the above solution.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Wercs" wrote:

Just a point:

When data in sheet 1 changes from In to Out (or vice versa), how can the
filtered results on sheet 2 be similarly updated automatically?

Rgs.



"Ashish Mathur" wrote in message
...
Hi,

Suppose the heading of columns 1,2 and 4 on sheet 1 are A,B and C. Please
perform the following steps

1. Assign a column heading to all the columns in sheet 1, if not already
done so. Say the heading are A-M
2. Now type A in a cell below the data, say A25
3. In cell A26, type Out
4. In sheet 2, type A,B and D in B3,C3 and D3.
5. Now click on cell B4 on sheet 2 and go to Data Filter Advance
Filter;
6. Select the radio button for "Copy to another location";
7. In the list box, please select the range on sheet 1 (Please select the
header row as well);
8. In the Criteria box, select the range A25:A26 of sheet 1;
9. In the copy to box, select B3:D100 of sheet 2;
10. Click on OK

Hope this helps.



--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Wercs" wrote in message
...
I wish to filter results from one sheet onto a second sheet.
I have a data table containing 13 columns, but only columns 1,2 & 4 are
needed in the filtered results.
Column 1 contains either "In" or "Out". Col.2 a name, Col.4 a location.

I wish to place on sheet 2 all records shown as "Out".

I've tried the VLOOKUP but guess I'm not getting the right parameters.

Help appreciated.
Thanks.

PS - I'm working with Excel 2007, but the workbook has to be compatible
with 2003.
--
Wercs

Hi,

I understand about the filter not being a formula.

Just how complicated would the formula approach be?
As the data changes quite regularly, this would be the preferred method, if
at all possible.

Regards,
--
Wercs.


"Shane Devenshire" wrote in
message ...
Hi,

the Advanced Filter command is not a formula, so the second sheet will not
update automatically. You can rerun the filter command when the data
changes
or you can record a macro that reruns the command for you.

If you need it to have a formula approach, its going to be fairly
complicated, so hopefully you can live with the above solution.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Wercs" wrote:

Just a point:

When data in sheet 1 changes from In to Out (or vice versa), how can the
filtered results on sheet 2 be similarly updated automatically?

Rgs.



"Ashish Mathur" wrote in message
...
Hi,

Suppose the heading of columns 1,2 and 4 on sheet 1 are A,B and C.
Please
perform the following steps

1. Assign a column heading to all the columns in sheet 1, if not
already
done so. Say the heading are A-M
2. Now type A in a cell below the data, say A25
3. In cell A26, type Out
4. In sheet 2, type A,B and D in B3,C3 and D3.
5. Now click on cell B4 on sheet 2 and go to Data Filter Advance
Filter;
6. Select the radio button for "Copy to another location";
7. In the list box, please select the range on sheet 1 (Please select
the
header row as well);
8. In the Criteria box, select the range A25:A26 of sheet 1;
9. In the copy to box, select B3:D100 of sheet 2;
10. Click on OK

Hope this helps.



--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Wercs" wrote in message
...
I wish to filter results from one sheet onto a second sheet.
I have a data table containing 13 columns, but only columns 1,2 & 4
are
needed in the filtered results.
Column 1 contains either "In" or "Out". Col.2 a name, Col.4 a
location.

I wish to place on sheet 2 all records shown as "Out".

I've tried the VLOOKUP but guess I'm not getting the right parameters.

Help appreciated.
Thanks.

PS - I'm working with Excel 2007, but the workbook has to be
compatible
with 2003.
--
Wercs

You have 3 columns you want to extract data from. How many rows of data need
to be searched and approximately how many rows of results do you expect to
be extracted?

The formula to do this isn't that complicated but it is calculation
intensive on large amounts of data. If you have 1000's of rows of data using
a formula may not be the best way to go.

--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Hi,

I understand about the filter not being a formula.

Just how complicated would the formula approach be?
As the data changes quite regularly, this would be the preferred method,
if at all possible.

Regards,
--
Wercs.


"Shane Devenshire" wrote in
message ...
Hi,

the Advanced Filter command is not a formula, so the second sheet will
not
update automatically. You can rerun the filter command when the data
changes
or you can record a macro that reruns the command for you.

If you need it to have a formula approach, its going to be fairly
complicated, so hopefully you can live with the above solution.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Wercs" wrote:

Just a point:

When data in sheet 1 changes from In to Out (or vice versa), how can the
filtered results on sheet 2 be similarly updated automatically?

Rgs.



"Ashish Mathur" wrote in message
...
Hi,

Suppose the heading of columns 1,2 and 4 on sheet 1 are A,B and C.
Please
perform the following steps

1. Assign a column heading to all the columns in sheet 1, if not
already
done so. Say the heading are A-M
2. Now type A in a cell below the data, say A25
3. In cell A26, type Out
4. In sheet 2, type A,B and D in B3,C3 and D3.
5. Now click on cell B4 on sheet 2 and go to Data Filter Advance
Filter;
6. Select the radio button for "Copy to another location";
7. In the list box, please select the range on sheet 1 (Please select
the
header row as well);
8. In the Criteria box, select the range A25:A26 of sheet 1;
9. In the copy to box, select B3:D100 of sheet 2;
10. Click on OK

Hope this helps.



--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Wercs" wrote in message
...
I wish to filter results from one sheet onto a second sheet.
I have a data table containing 13 columns, but only columns 1,2 & 4
are
needed in the filtered results.
Column 1 contains either "In" or "Out". Col.2 a name, Col.4 a
location.

I wish to place on sheet 2 all records shown as "Out".

I've tried the VLOOKUP but guess I'm not getting the right
parameters.

Help appreciated.
Thanks.

PS - I'm working with Excel 2007, but the workbook has to be
compatible
with 2003.
--
Wercs

Hi,
I have 300-350 rows in the table. I would expect to have a maximum of 100
rows of filtered results.

Is a formula possible for these?

Cheers.
--
Wercs.


"T. Valko" wrote in message
...
You have 3 columns you want to extract data from. How many rows of data
need to be searched and approximately how many rows of results do you
expect to be extracted?

The formula to do this isn't that complicated but it is calculation
intensive on large amounts of data. If you have 1000's of rows of data
using a formula may not be the best way to go.

--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Hi,

I understand about the filter not being a formula.

Just how complicated would the formula approach be?
As the data changes quite regularly, this would be the preferred method,
if at all possible.

Regards,
--
Wercs.


"Shane Devenshire" wrote in
message ...
Hi,

the Advanced Filter command is not a formula, so the second sheet will
not
update automatically. You can rerun the filter command when the data
changes
or you can record a macro that reruns the command for you.

If you need it to have a formula approach, its going to be fairly
complicated, so hopefully you can live with the above solution.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Wercs" wrote:

Just a point:

When data in sheet 1 changes from In to Out (or vice versa), how can
the
filtered results on sheet 2 be similarly updated automatically?

Rgs.



"Ashish Mathur" wrote in message
...
Hi,

Suppose the heading of columns 1,2 and 4 on sheet 1 are A,B and C.
Please
perform the following steps

1. Assign a column heading to all the columns in sheet 1, if not
already
done so. Say the heading are A-M
2. Now type A in a cell below the data, say A25
3. In cell A26, type Out
4. In sheet 2, type A,B and D in B3,C3 and D3.
5. Now click on cell B4 on sheet 2 and go to Data Filter Advance
Filter;
6. Select the radio button for "Copy to another location";
7. In the list box, please select the range on sheet 1 (Please select
the
header row as well);
8. In the Criteria box, select the range A25:A26 of sheet 1;
9. In the copy to box, select B3:D100 of sheet 2;
10. Click on OK

Hope this helps.



--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Wercs" wrote in message
...
I wish to filter results from one sheet onto a second sheet.
I have a data table containing 13 columns, but only columns 1,2 & 4
are
needed in the filtered results.
Column 1 contains either "In" or "Out". Col.2 a name, Col.4 a
location.

I wish to place on sheet 2 all records shown as "Out".

I've tried the VLOOKUP but guess I'm not getting the right
parameters.

Help appreciated.
Thanks.

PS - I'm working with Excel 2007, but the workbook has to be
compatible
with 2003.
--
Wercs

Is a formula possible for these?

Yes, no problem.

Ok, assume your raw data is on Sheet1. You want to extract data from columns
A, B and D where column A = out.

We'll extract this data to Sheet2 columns A, B and C. On Sheet2, A1:C1 are
column headers.

In the formula:

Status refers to Sheet1 A2:A350
Name refers to Sheet1 B2:B350
Location refers to Sheet1 D2:D350

I'm assuming the names are all unique. If this is the case we can use a
simpler formula to get the locations by looking up the names.

On Sheet2:

Enter this formula in A2:

=IF(B2="","","out")

Enter this array formula** in B2:

=IF(ROWS(B$2:B2)<=COUNTIF(Status,"out"),INDEX(Name ,SMALL(IF(Status="out",ROW(Name)),ROWS(B$2:B2))-MIN(ROW(Name))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Enter this formula in C2:

=IF(B2="","",INDEX(Location,MATCH(B2,Name,0)))

Now, select all 3 cells, A2:C2, copy down a number of rows that you expect
will cover the amount of rows of data extracted from Sheet1. 100 rows? 150?


--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Hi,
I have 300-350 rows in the table. I would expect to have a maximum of 100
rows of filtered results.

Is a formula possible for these?

Cheers.
--
Wercs.


"T. Valko" wrote in message
...
You have 3 columns you want to extract data from. How many rows of data
need to be searched and approximately how many rows of results do you
expect to be extracted?

The formula to do this isn't that complicated but it is calculation
intensive on large amounts of data. If you have 1000's of rows of data
using a formula may not be the best way to go.

--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Hi,

I understand about the filter not being a formula.

Just how complicated would the formula approach be?
As the data changes quite regularly, this would be the preferred method,
if at all possible.

Regards,
--
Wercs.


"Shane Devenshire" wrote in
message ...
Hi,

the Advanced Filter command is not a formula, so the second sheet will
not
update automatically. You can rerun the filter command when the data
changes
or you can record a macro that reruns the command for you.

If you need it to have a formula approach, its going to be fairly
complicated, so hopefully you can live with the above solution.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Wercs" wrote:

Just a point:

When data in sheet 1 changes from In to Out (or vice versa), how can
the
filtered results on sheet 2 be similarly updated automatically?

Rgs.



"Ashish Mathur" wrote in message
...
Hi,

Suppose the heading of columns 1,2 and 4 on sheet 1 are A,B and C.
Please
perform the following steps

1. Assign a column heading to all the columns in sheet 1, if not
already
done so. Say the heading are A-M
2. Now type A in a cell below the data, say A25
3. In cell A26, type Out
4. In sheet 2, type A,B and D in B3,C3 and D3.
5. Now click on cell B4 on sheet 2 and go to Data Filter Advance
Filter;
6. Select the radio button for "Copy to another location";
7. In the list box, please select the range on sheet 1 (Please
select the
header row as well);
8. In the Criteria box, select the range A25:A26 of sheet 1;
9. In the copy to box, select B3:D100 of sheet 2;
10. Click on OK

Hope this helps.



--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Wercs" wrote in message
...
I wish to filter results from one sheet onto a second sheet.
I have a data table containing 13 columns, but only columns 1,2 & 4
are
needed in the filtered results.
Column 1 contains either "In" or "Out". Col.2 a name, Col.4 a
location.

I wish to place on sheet 2 all records shown as "Out".

I've tried the VLOOKUP but guess I'm not getting the right
parameters.

Help appreciated.
Thanks.

PS - I'm working with Excel 2007, but the workbook has to be
compatible
with 2003.
--
Wercs

Hi,

I think I may be missing something or....?

On sheet one A1,B1 & D1 headers are IN/OUT, NAME & LOCATION.

It's the status of column A that's important in the extracted records.

Some of the names in column B are repeated, but with different locations.

Is that why I can't seem to get the formulas to work?

Sorry if I've missed something obvious :(
--
Wercs.



"T. Valko" wrote in message
...
Is a formula possible for these?

Yes, no problem.

Ok, assume your raw data is on Sheet1. You want to extract data from
columns A, B and D where column A = out.

We'll extract this data to Sheet2 columns A, B and C. On Sheet2, A1:C1 are
column headers.

In the formula:

Status refers to Sheet1 A2:A350
Name refers to Sheet1 B2:B350
Location refers to Sheet1 D2:D350

I'm assuming the names are all unique. If this is the case we can use a
simpler formula to get the locations by looking up the names.

On Sheet2:

Enter this formula in A2:

=IF(B2="","","out")

Enter this array formula** in B2:

=IF(ROWS(B$2:B2)<=COUNTIF(Status,"out"),INDEX(Name ,SMALL(IF(Status="out",ROW(Name)),ROWS(B$2:B2))-MIN(ROW(Name))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

Enter this formula in C2:

=IF(B2="","",INDEX(Location,MATCH(B2,Name,0)))

Now, select all 3 cells, A2:C2, copy down a number of rows that you expect
will cover the amount of rows of data extracted from Sheet1. 100 rows?
150?


--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Hi,
I have 300-350 rows in the table. I would expect to have a maximum of 100
rows of filtered results.

Is a formula possible for these?

Cheers.
--
Wercs.


"T. Valko" wrote in message
...
You have 3 columns you want to extract data from. How many rows of data
need to be searched and approximately how many rows of results do you
expect to be extracted?

The formula to do this isn't that complicated but it is calculation
intensive on large amounts of data. If you have 1000's of rows of data
using a formula may not be the best way to go.

--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Hi,

I understand about the filter not being a formula.

Just how complicated would the formula approach be?
As the data changes quite regularly, this would be the preferred
method, if at all possible.

Regards,
--
Wercs.


"Shane Devenshire" wrote in
message ...
Hi,

the Advanced Filter command is not a formula, so the second sheet will
not
update automatically. You can rerun the filter command when the data
changes
or you can record a macro that reruns the command for you.

If you need it to have a formula approach, its going to be fairly
complicated, so hopefully you can live with the above solution.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Wercs" wrote:

Just a point:

When data in sheet 1 changes from In to Out (or vice versa), how can
the
filtered results on sheet 2 be similarly updated automatically?

Rgs.



"Ashish Mathur" wrote in message
...
Hi,

Suppose the heading of columns 1,2 and 4 on sheet 1 are A,B and C.
Please
perform the following steps

1. Assign a column heading to all the columns in sheet 1, if not
already
done so. Say the heading are A-M
2. Now type A in a cell below the data, say A25
3. In cell A26, type Out
4. In sheet 2, type A,B and D in B3,C3 and D3.
5. Now click on cell B4 on sheet 2 and go to Data Filter
Advance
Filter;
6. Select the radio button for "Copy to another location";
7. In the list box, please select the range on sheet 1 (Please
select the
header row as well);
8. In the Criteria box, select the range A25:A26 of sheet 1;
9. In the copy to box, select B3:D100 of sheet 2;
10. Click on OK

Hope this helps.



--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Wercs" wrote in message
...
I wish to filter results from one sheet onto a second sheet.
I have a data table containing 13 columns, but only columns 1,2 &
4 are
needed in the filtered results.
Column 1 contains either "In" or "Out". Col.2 a name, Col.4 a
location.

I wish to place on sheet 2 all records shown as "Out".

I've tried the VLOOKUP but guess I'm not getting the right
parameters.

Help appreciated.
Thanks.

PS - I'm working with Excel 2007, but the workbook has to be
compatible
with 2003.
--
Wercs

Some of the names in column B are repeated, but with different locations.

We'll have to change the formula in Sheet2 C2 to this array formula** :

=IF(B2="","",INDEX(Location,SMALL(IF(Status="out", ROW(Location)),ROWS(C$2:C2))-MIN(ROW(Location))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Array formulas are entered differently than a regular formula. After you
type in a regular formula you hit the ENTER key. With an array formula you
*must* use a combination of keys. Those keys are the CTRL key, the SHIFT key
and the ENTER key. That is, hold down both the CTRL key and the SHIFT key
then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }.
You can't just type these brackets in, you *must* use the key combo. Also,
anytime you edit an array formula it *must* be re-entered as an array using
the key combo.

Here's a small sample file that demonstrates this.

xExtract.xls 24kb

http://cjoint.com/?crtXjqDLs4

--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Hi,

I think I may be missing something or....?

On sheet one A1,B1 & D1 headers are IN/OUT, NAME & LOCATION.

It's the status of column A that's important in the extracted records.

Some of the names in column B are repeated, but with different locations.

Is that why I can't seem to get the formulas to work?

Sorry if I've missed something obvious :(
--
Wercs.



"T. Valko" wrote in message
...
Is a formula possible for these?

Yes, no problem.

Ok, assume your raw data is on Sheet1. You want to extract data from
columns A, B and D where column A = out.

We'll extract this data to Sheet2 columns A, B and C. On Sheet2, A1:C1
are column headers.

In the formula:

Status refers to Sheet1 A2:A350
Name refers to Sheet1 B2:B350
Location refers to Sheet1 D2:D350

I'm assuming the names are all unique. If this is the case we can use a
simpler formula to get the locations by looking up the names.

On Sheet2:

Enter this formula in A2:

=IF(B2="","","out")

Enter this array formula** in B2:

=IF(ROWS(B$2:B2)<=COUNTIF(Status,"out"),INDEX(Name ,SMALL(IF(Status="out",ROW(Name)),ROWS(B$2:B2))-MIN(ROW(Name))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

Enter this formula in C2:

=IF(B2="","",INDEX(Location,MATCH(B2,Name,0)))

Now, select all 3 cells, A2:C2, copy down a number of rows that you
expect will cover the amount of rows of data extracted from Sheet1. 100
rows? 150?


--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Hi,
I have 300-350 rows in the table. I would expect to have a maximum of
100 rows of filtered results.

Is a formula possible for these?

Cheers.
--
Wercs.


"T. Valko" wrote in message
...
You have 3 columns you want to extract data from. How many rows of data
need to be searched and approximately how many rows of results do you
expect to be extracted?

The formula to do this isn't that complicated but it is calculation
intensive on large amounts of data. If you have 1000's of rows of data
using a formula may not be the best way to go.

--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Hi,

I understand about the filter not being a formula.

Just how complicated would the formula approach be?
As the data changes quite regularly, this would be the preferred
method, if at all possible.

Regards,
--
Wercs.


"Shane Devenshire" wrote
in message ...
Hi,

the Advanced Filter command is not a formula, so the second sheet
will not
update automatically. You can rerun the filter command when the data
changes
or you can record a macro that reruns the command for you.

If you need it to have a formula approach, its going to be fairly
complicated, so hopefully you can live with the above solution.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Wercs" wrote:

Just a point:

When data in sheet 1 changes from In to Out (or vice versa), how can
the
filtered results on sheet 2 be similarly updated automatically?

Rgs.



"Ashish Mathur" wrote in message
...
Hi,

Suppose the heading of columns 1,2 and 4 on sheet 1 are A,B and C.
Please
perform the following steps

1. Assign a column heading to all the columns in sheet 1, if not
already
done so. Say the heading are A-M
2. Now type A in a cell below the data, say A25
3. In cell A26, type Out
4. In sheet 2, type A,B and D in B3,C3 and D3.
5. Now click on cell B4 on sheet 2 and go to Data Filter
Advance
Filter;
6. Select the radio button for "Copy to another location";
7. In the list box, please select the range on sheet 1 (Please
select the
header row as well);
8. In the Criteria box, select the range A25:A26 of sheet 1;
9. In the copy to box, select B3:D100 of sheet 2;
10. Click on OK

Hope this helps.



--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Wercs" wrote in message
...
I wish to filter results from one sheet onto a second sheet.
I have a data table containing 13 columns, but only columns 1,2 &
4 are
needed in the filtered results.
Column 1 contains either "In" or "Out". Col.2 a name, Col.4 a
location.

I wish to place on sheet 2 all records shown as "Out".

I've tried the VLOOKUP but guess I'm not getting the right
parameters.

Help appreciated.
Thanks.

PS - I'm working with Excel 2007, but the workbook has to be
compatible
with 2003.
--
Wercs

Well....

With A2 as

=IF(B2="","","out")

and C2 as the array

=IF(B2="","",INDEX(Location,SMALL(IF(Status="out", ROW(Location)),ROWS(C$2:C2))-MIN(ROW(Location))+1))

when the array

=IF(ROWS(B$2:B2)<=COUNTIF(Status,"out"),INDEX(Name ,SMALL(IF(Status="out",ROW(Name)),ROWS(B$2:B2))-MIN(ROW(Name))+1),"")

is entered into B2

all three (A2,B2,C2) display

#NAME?

I'm taking care to enter the arrays with Ctrl+Shift+Enter.
Where am I going wrong?
--
Wercs.


"T. Valko" wrote in message
...
Some of the names in column B are repeated, but with different locations.

We'll have to change the formula in Sheet2 C2 to this array formula** :

=IF(B2="","",INDEX(Location,SMALL(IF(Status="out", ROW(Location)),ROWS(C$2:C2))-MIN(ROW(Location))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

Array formulas are entered differently than a regular formula. After you
type in a regular formula you hit the ENTER key. With an array formula you
*must* use a combination of keys. Those keys are the CTRL key, the SHIFT
key and the ENTER key. That is, hold down both the CTRL key and the SHIFT
key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets
{ }. You can't just type these brackets in, you *must* use the key combo.
Also, anytime you edit an array formula it *must* be re-entered as an
array using the key combo.

Here's a small sample file that demonstrates this.

xExtract.xls 24kb

http://cjoint.com/?crtXjqDLs4

--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Hi,

I think I may be missing something or....?

On sheet one A1,B1 & D1 headers are IN/OUT, NAME & LOCATION.

It's the status of column A that's important in the extracted records.

Some of the names in column B are repeated, but with different locations.

Is that why I can't seem to get the formulas to work?

Sorry if I've missed something obvious :(
--
Wercs.



"T. Valko" wrote in message
...
Is a formula possible for these?

Yes, no problem.

Ok, assume your raw data is on Sheet1. You want to extract data from
columns A, B and D where column A = out.

We'll extract this data to Sheet2 columns A, B and C. On Sheet2, A1:C1
are column headers.

In the formula:

Status refers to Sheet1 A2:A350
Name refers to Sheet1 B2:B350
Location refers to Sheet1 D2:D350

I'm assuming the names are all unique. If this is the case we can use a
simpler formula to get the locations by looking up the names.

On Sheet2:

Enter this formula in A2:

=IF(B2="","","out")

Enter this array formula** in B2:

=IF(ROWS(B$2:B2)<=COUNTIF(Status,"out"),INDEX(Name ,SMALL(IF(Status="out",ROW(Name)),ROWS(B$2:B2))-MIN(ROW(Name))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

Enter this formula in C2:

=IF(B2="","",INDEX(Location,MATCH(B2,Name,0)))

Now, select all 3 cells, A2:C2, copy down a number of rows that you
expect will cover the amount of rows of data extracted from Sheet1. 100
rows? 150?


--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Hi,
I have 300-350 rows in the table. I would expect to have a maximum of
100 rows of filtered results.

Is a formula possible for these?

Cheers.
--
Wercs.


"T. Valko" wrote in message
...
You have 3 columns you want to extract data from. How many rows of
data need to be searched and approximately how many rows of results do
you expect to be extracted?

The formula to do this isn't that complicated but it is calculation
intensive on large amounts of data. If you have 1000's of rows of data
using a formula may not be the best way to go.

--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Hi,

I understand about the filter not being a formula.

Just how complicated would the formula approach be?
As the data changes quite regularly, this would be the preferred
method, if at all possible.

Regards,
--
Wercs.


"Shane Devenshire" wrote
in message ...
Hi,

the Advanced Filter command is not a formula, so the second sheet
will not
update automatically. You can rerun the filter command when the
data changes
or you can record a macro that reruns the command for you.

If you need it to have a formula approach, its going to be fairly
complicated, so hopefully you can live with the above solution.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Wercs" wrote:

Just a point:

When data in sheet 1 changes from In to Out (or vice versa), how
can the
filtered results on sheet 2 be similarly updated automatically?

Rgs.



"Ashish Mathur" wrote in message
...
Hi,

Suppose the heading of columns 1,2 and 4 on sheet 1 are A,B and
C. Please
perform the following steps

1. Assign a column heading to all the columns in sheet 1, if not
already
done so. Say the heading are A-M
2. Now type A in a cell below the data, say A25
3. In cell A26, type Out
4. In sheet 2, type A,B and D in B3,C3 and D3.
5. Now click on cell B4 on sheet 2 and go to Data Filter
Advance
Filter;
6. Select the radio button for "Copy to another location";
7. In the list box, please select the range on sheet 1 (Please
select the
header row as well);
8. In the Criteria box, select the range A25:A26 of sheet 1;
9. In the copy to box, select B3:D100 of sheet 2;
10. Click on OK

Hope this helps.



--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Wercs" wrote in message
...
I wish to filter results from one sheet onto a second sheet.
I have a data table containing 13 columns, but only columns 1,2
& 4 are
needed in the filtered results.
Column 1 contains either "In" or "Out". Col.2 a name, Col.4 a
location.

I wish to place on sheet 2 all records shown as "Out".

I've tried the VLOOKUP but guess I'm not getting the right
parameters.

Help appreciated.
Thanks.

PS - I'm working with Excel 2007, but the workbook has to be
compatible
with 2003.
--
Wercs

Forgot to add...

The Status in sheet 1 column A is derived by the formula

=IF(TODAY()-I2365,"OUT","IN")

Is this causing problems?
--
Wercs.


"T. Valko" wrote in message
...
Some of the names in column B are repeated, but with different locations.

We'll have to change the formula in Sheet2 C2 to this array formula** :

=IF(B2="","",INDEX(Location,SMALL(IF(Status="out", ROW(Location)),ROWS(C$2:C2))-MIN(ROW(Location))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

Array formulas are entered differently than a regular formula. After you
type in a regular formula you hit the ENTER key. With an array formula you
*must* use a combination of keys. Those keys are the CTRL key, the SHIFT
key and the ENTER key. That is, hold down both the CTRL key and the SHIFT
key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets
{ }. You can't just type these brackets in, you *must* use the key combo.
Also, anytime you edit an array formula it *must* be re-entered as an
array using the key combo.

Here's a small sample file that demonstrates this.

xExtract.xls 24kb

http://cjoint.com/?crtXjqDLs4

--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Hi,

I think I may be missing something or....?

On sheet one A1,B1 & D1 headers are IN/OUT, NAME & LOCATION.

It's the status of column A that's important in the extracted records.

Some of the names in column B are repeated, but with different locations.

Is that why I can't seem to get the formulas to work?

Sorry if I've missed something obvious :(
--
Wercs.



"T. Valko" wrote in message
...
Is a formula possible for these?

Yes, no problem.

Ok, assume your raw data is on Sheet1. You want to extract data from
columns A, B and D where column A = out.

We'll extract this data to Sheet2 columns A, B and C. On Sheet2, A1:C1
are column headers.

In the formula:

Status refers to Sheet1 A2:A350
Name refers to Sheet1 B2:B350
Location refers to Sheet1 D2:D350

I'm assuming the names are all unique. If this is the case we can use a
simpler formula to get the locations by looking up the names.

On Sheet2:

Enter this formula in A2:

=IF(B2="","","out")

Enter this array formula** in B2:

=IF(ROWS(B$2:B2)<=COUNTIF(Status,"out"),INDEX(Name ,SMALL(IF(Status="out",ROW(Name)),ROWS(B$2:B2))-MIN(ROW(Name))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

Enter this formula in C2:

=IF(B2="","",INDEX(Location,MATCH(B2,Name,0)))

Now, select all 3 cells, A2:C2, copy down a number of rows that you
expect will cover the amount of rows of data extracted from Sheet1. 100
rows? 150?


--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Hi,
I have 300-350 rows in the table. I would expect to have a maximum of
100 rows of filtered results.

Is a formula possible for these?

Cheers.
--
Wercs.


"T. Valko" wrote in message
...
You have 3 columns you want to extract data from. How many rows of
data need to be searched and approximately how many rows of results do
you expect to be extracted?

The formula to do this isn't that complicated but it is calculation
intensive on large amounts of data. If you have 1000's of rows of data
using a formula may not be the best way to go.

--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Hi,

I understand about the filter not being a formula.

Just how complicated would the formula approach be?
As the data changes quite regularly, this would be the preferred
method, if at all possible.

Regards,
--
Wercs.


"Shane Devenshire" wrote
in message ...
Hi,

the Advanced Filter command is not a formula, so the second sheet
will not
update automatically. You can rerun the filter command when the
data changes
or you can record a macro that reruns the command for you.

If you need it to have a formula approach, its going to be fairly
complicated, so hopefully you can live with the above solution.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Wercs" wrote:

Just a point:

When data in sheet 1 changes from In to Out (or vice versa), how
can the
filtered results on sheet 2 be similarly updated automatically?

Rgs.



"Ashish Mathur" wrote in message
...
Hi,

Suppose the heading of columns 1,2 and 4 on sheet 1 are A,B and
C. Please
perform the following steps

1. Assign a column heading to all the columns in sheet 1, if not
already
done so. Say the heading are A-M
2. Now type A in a cell below the data, say A25
3. In cell A26, type Out
4. In sheet 2, type A,B and D in B3,C3 and D3.
5. Now click on cell B4 on sheet 2 and go to Data Filter
Advance
Filter;
6. Select the radio button for "Copy to another location";
7. In the list box, please select the range on sheet 1 (Please
select the
header row as well);
8. In the Criteria box, select the range A25:A26 of sheet 1;
9. In the copy to box, select B3:D100 of sheet 2;
10. Click on OK

Hope this helps.



--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Wercs" wrote in message
...
I wish to filter results from one sheet onto a second sheet.
I have a data table containing 13 columns, but only columns 1,2
& 4 are
needed in the filtered results.
Column 1 contains either "In" or "Out". Col.2 a name, Col.4 a
location.

I wish to place on sheet 2 all records shown as "Out".

I've tried the VLOOKUP but guess I'm not getting the right
parameters.

Help appreciated.
Thanks.

PS - I'm working with Excel 2007, but the workbook has to be
compatible
with 2003.
--
Wercs

No, that's not a problem. See the sample file I posted. I use *defined named
ranges*.

--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Forgot to add...

The Status in sheet 1 column A is derived by the formula

=IF(TODAY()-I2365,"OUT","IN")

Is this causing problems?
--
Wercs.


"T. Valko" wrote in message
...
Some of the names in column B are repeated, but with different
locations.

We'll have to change the formula in Sheet2 C2 to this array formula** :

=IF(B2="","",INDEX(Location,SMALL(IF(Status="out", ROW(Location)),ROWS(C$2:C2))-MIN(ROW(Location))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

Array formulas are entered differently than a regular formula. After you
type in a regular formula you hit the ENTER key. With an array formula
you *must* use a combination of keys. Those keys are the CTRL key, the
SHIFT key and the ENTER key. That is, hold down both the CTRL key and the
SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets
{ }. You can't just type these brackets in, you *must* use the key combo.
Also, anytime you edit an array formula it *must* be re-entered as an
array using the key combo.

Here's a small sample file that demonstrates this.

xExtract.xls 24kb

http://cjoint.com/?crtXjqDLs4

--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Hi,

I think I may be missing something or....?

On sheet one A1,B1 & D1 headers are IN/OUT, NAME & LOCATION.

It's the status of column A that's important in the extracted records.

Some of the names in column B are repeated, but with different
locations.

Is that why I can't seem to get the formulas to work?

Sorry if I've missed something obvious :(
--
Wercs.



"T. Valko" wrote in message
...
Is a formula possible for these?

Yes, no problem.

Ok, assume your raw data is on Sheet1. You want to extract data from
columns A, B and D where column A = out.

We'll extract this data to Sheet2 columns A, B and C. On Sheet2, A1:C1
are column headers.

In the formula:

Status refers to Sheet1 A2:A350
Name refers to Sheet1 B2:B350
Location refers to Sheet1 D2:D350

I'm assuming the names are all unique. If this is the case we can use a
simpler formula to get the locations by looking up the names.

On Sheet2:

Enter this formula in A2:

=IF(B2="","","out")

Enter this array formula** in B2:

=IF(ROWS(B$2:B2)<=COUNTIF(Status,"out"),INDEX(Name ,SMALL(IF(Status="out",ROW(Name)),ROWS(B$2:B2))-MIN(ROW(Name))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

Enter this formula in C2:

=IF(B2="","",INDEX(Location,MATCH(B2,Name,0)))

Now, select all 3 cells, A2:C2, copy down a number of rows that you
expect will cover the amount of rows of data extracted from Sheet1. 100
rows? 150?


--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Hi,
I have 300-350 rows in the table. I would expect to have a maximum of
100 rows of filtered results.

Is a formula possible for these?

Cheers.
--
Wercs.


"T. Valko" wrote in message
...
You have 3 columns you want to extract data from. How many rows of
data need to be searched and approximately how many rows of results
do you expect to be extracted?

The formula to do this isn't that complicated but it is calculation
intensive on large amounts of data. If you have 1000's of rows of
data using a formula may not be the best way to go.

--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Hi,

I understand about the filter not being a formula.

Just how complicated would the formula approach be?
As the data changes quite regularly, this would be the preferred
method, if at all possible.

Regards,
--
Wercs.


"Shane Devenshire" wrote
in message
...
Hi,

the Advanced Filter command is not a formula, so the second sheet
will not
update automatically. You can rerun the filter command when the
data changes
or you can record a macro that reruns the command for you.

If you need it to have a formula approach, its going to be fairly
complicated, so hopefully you can live with the above solution.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Wercs" wrote:

Just a point:

When data in sheet 1 changes from In to Out (or vice versa), how
can the
filtered results on sheet 2 be similarly updated automatically?

Rgs.



"Ashish Mathur" wrote in message
...
Hi,

Suppose the heading of columns 1,2 and 4 on sheet 1 are A,B and
C. Please
perform the following steps

1. Assign a column heading to all the columns in sheet 1, if not
already
done so. Say the heading are A-M
2. Now type A in a cell below the data, say A25
3. In cell A26, type Out
4. In sheet 2, type A,B and D in B3,C3 and D3.
5. Now click on cell B4 on sheet 2 and go to Data Filter
Advance
Filter;
6. Select the radio button for "Copy to another location";
7. In the list box, please select the range on sheet 1 (Please
select the
header row as well);
8. In the Criteria box, select the range A25:A26 of sheet 1;
9. In the copy to box, select B3:D100 of sheet 2;
10. Click on OK

Hope this helps.



--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Wercs" wrote in message
...
I wish to filter results from one sheet onto a second sheet.
I have a data table containing 13 columns, but only columns 1,2
& 4 are
needed in the filtered results.
Column 1 contains either "In" or "Out". Col.2 a name, Col.4 a
location.

I wish to place on sheet 2 all records shown as "Out".

I've tried the VLOOKUP but guess I'm not getting the right
parameters.

Help appreciated.
Thanks.

PS - I'm working with Excel 2007, but the workbook has to be
compatible
with 2003.
--
Wercs

Ahhhhh.....

Now I see!

Thanks for your help -- and patience :))
--
Wercs.


"T. Valko" wrote in message
...
No, that's not a problem. See the sample file I posted. I use *defined
named ranges*.

--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Forgot to add...

The Status in sheet 1 column A is derived by the formula

=IF(TODAY()-I2365,"OUT","IN")

Is this causing problems?
--
Wercs.


"T. Valko" wrote in message
...
Some of the names in column B are repeated, but with different
locations.

We'll have to change the formula in Sheet2 C2 to this array formula** :

=IF(B2="","",INDEX(Location,SMALL(IF(Status="out", ROW(Location)),ROWS(C$2:C2))-MIN(ROW(Location))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

Array formulas are entered differently than a regular formula. After you
type in a regular formula you hit the ENTER key. With an array formula
you *must* use a combination of keys. Those keys are the CTRL key, the
SHIFT key and the ENTER key. That is, hold down both the CTRL key and
the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets
{ }. You can't just type these brackets in, you *must* use the key
combo. Also, anytime you edit an array formula it *must* be re-entered
as an array using the key combo.

Here's a small sample file that demonstrates this.

xExtract.xls 24kb

http://cjoint.com/?crtXjqDLs4

--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Hi,

I think I may be missing something or....?

On sheet one A1,B1 & D1 headers are IN/OUT, NAME & LOCATION.

It's the status of column A that's important in the extracted records.

Some of the names in column B are repeated, but with different
locations.

Is that why I can't seem to get the formulas to work?

Sorry if I've missed something obvious :(
--
Wercs.



"T. Valko" wrote in message
...
Is a formula possible for these?

Yes, no problem.

Ok, assume your raw data is on Sheet1. You want to extract data from
columns A, B and D where column A = out.

We'll extract this data to Sheet2 columns A, B and C. On Sheet2, A1:C1
are column headers.

In the formula:

Status refers to Sheet1 A2:A350
Name refers to Sheet1 B2:B350
Location refers to Sheet1 D2:D350

I'm assuming the names are all unique. If this is the case we can use
a simpler formula to get the locations by looking up the names.

On Sheet2:

Enter this formula in A2:

=IF(B2="","","out")

Enter this array formula** in B2:

=IF(ROWS(B$2:B2)<=COUNTIF(Status,"out"),INDEX(Name ,SMALL(IF(Status="out",ROW(Name)),ROWS(B$2:B2))-MIN(ROW(Name))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

Enter this formula in C2:

=IF(B2="","",INDEX(Location,MATCH(B2,Name,0)))

Now, select all 3 cells, A2:C2, copy down a number of rows that you
expect will cover the amount of rows of data extracted from Sheet1.
100 rows? 150?


--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Hi,
I have 300-350 rows in the table. I would expect to have a maximum of
100 rows of filtered results.

Is a formula possible for these?

Cheers.
--
Wercs.


"T. Valko" wrote in message
...
You have 3 columns you want to extract data from. How many rows of
data need to be searched and approximately how many rows of results
do you expect to be extracted?

The formula to do this isn't that complicated but it is calculation
intensive on large amounts of data. If you have 1000's of rows of
data using a formula may not be the best way to go.

--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Hi,

I understand about the filter not being a formula.

Just how complicated would the formula approach be?
As the data changes quite regularly, this would be the preferred
method, if at all possible.

Regards,
--
Wercs.


"Shane Devenshire"
wrote in message
...
Hi,

the Advanced Filter command is not a formula, so the second sheet
will not
update automatically. You can rerun the filter command when the
data changes
or you can record a macro that reruns the command for you.

If you need it to have a formula approach, its going to be fairly
complicated, so hopefully you can live with the above solution.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Wercs" wrote:

Just a point:

When data in sheet 1 changes from In to Out (or vice versa), how
can the
filtered results on sheet 2 be similarly updated automatically?

Rgs.



"Ashish Mathur" wrote in message
...
Hi,

Suppose the heading of columns 1,2 and 4 on sheet 1 are A,B and
C. Please
perform the following steps

1. Assign a column heading to all the columns in sheet 1, if
not already
done so. Say the heading are A-M
2. Now type A in a cell below the data, say A25
3. In cell A26, type Out
4. In sheet 2, type A,B and D in B3,C3 and D3.
5. Now click on cell B4 on sheet 2 and go to Data Filter
Advance
Filter;
6. Select the radio button for "Copy to another location";
7. In the list box, please select the range on sheet 1 (Please
select the
header row as well);
8. In the Criteria box, select the range A25:A26 of sheet 1;
9. In the copy to box, select B3:D100 of sheet 2;
10. Click on OK

Hope this helps.



--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Wercs" wrote in message
...
I wish to filter results from one sheet onto a second sheet.
I have a data table containing 13 columns, but only columns
1,2 & 4 are
needed in the filtered results.
Column 1 contains either "In" or "Out". Col.2 a name, Col.4 a
location.

I wish to place on sheet 2 all records shown as "Out".

I've tried the VLOOKUP but guess I'm not getting the right
parameters.

Help appreciated.
Thanks.

PS - I'm working with Excel 2007, but the workbook has to be
compatible
with 2003.
--
Wercs

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Ahhhhh.....

Now I see!

Thanks for your help -- and patience :))
--
Wercs.


"T. Valko" wrote in message
...
No, that's not a problem. See the sample file I posted. I use *defined
named ranges*.

--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Forgot to add...

The Status in sheet 1 column A is derived by the formula

=IF(TODAY()-I2365,"OUT","IN")

Is this causing problems?
--
Wercs.


"T. Valko" wrote in message
...
Some of the names in column B are repeated, but with different
locations.

We'll have to change the formula in Sheet2 C2 to this array formula** :

=IF(B2="","",INDEX(Location,SMALL(IF(Status="out", ROW(Location)),ROWS(C$2:C2))-MIN(ROW(Location))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

Array formulas are entered differently than a regular formula. After
you type in a regular formula you hit the ENTER key. With an array
formula you *must* use a combination of keys. Those keys are the CTRL
key, the SHIFT key and the ENTER key. That is, hold down both the CTRL
key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets
{ }. You can't just type these brackets in, you *must* use the key
combo. Also, anytime you edit an array formula it *must* be re-entered
as an array using the key combo.

Here's a small sample file that demonstrates this.

xExtract.xls 24kb

http://cjoint.com/?crtXjqDLs4

--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Hi,

I think I may be missing something or....?

On sheet one A1,B1 & D1 headers are IN/OUT, NAME & LOCATION.

It's the status of column A that's important in the extracted records.

Some of the names in column B are repeated, but with different
locations.

Is that why I can't seem to get the formulas to work?

Sorry if I've missed something obvious :(
--
Wercs.



"T. Valko" wrote in message
...
Is a formula possible for these?

Yes, no problem.

Ok, assume your raw data is on Sheet1. You want to extract data from
columns A, B and D where column A = out.

We'll extract this data to Sheet2 columns A, B and C. On Sheet2,
A1:C1 are column headers.

In the formula:

Status refers to Sheet1 A2:A350
Name refers to Sheet1 B2:B350
Location refers to Sheet1 D2:D350

I'm assuming the names are all unique. If this is the case we can use
a simpler formula to get the locations by looking up the names.

On Sheet2:

Enter this formula in A2:

=IF(B2="","","out")

Enter this array formula** in B2:

=IF(ROWS(B$2:B2)<=COUNTIF(Status,"out"),INDEX(Name ,SMALL(IF(Status="out",ROW(Name)),ROWS(B$2:B2))-MIN(ROW(Name))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and
the SHIFT key then hit ENTER.

Enter this formula in C2:

=IF(B2="","",INDEX(Location,MATCH(B2,Name,0)))

Now, select all 3 cells, A2:C2, copy down a number of rows that you
expect will cover the amount of rows of data extracted from Sheet1.
100 rows? 150?


--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Hi,
I have 300-350 rows in the table. I would expect to have a maximum
of 100 rows of filtered results.

Is a formula possible for these?

Cheers.
--
Wercs.


"T. Valko" wrote in message
...
You have 3 columns you want to extract data from. How many rows of
data need to be searched and approximately how many rows of results
do you expect to be extracted?

The formula to do this isn't that complicated but it is calculation
intensive on large amounts of data. If you have 1000's of rows of
data using a formula may not be the best way to go.

--
Biff
Microsoft Excel MVP


"Wercs" wrote in message
...
Hi,

I understand about the filter not being a formula.

Just how complicated would the formula approach be?
As the data changes quite regularly, this would be the preferred
method, if at all possible.

Regards,
--
Wercs.


"Shane Devenshire"
wrote in message
...
Hi,

the Advanced Filter command is not a formula, so the second sheet
will not
update automatically. You can rerun the filter command when the
data changes
or you can record a macro that reruns the command for you.

If you need it to have a formula approach, its going to be fairly
complicated, so hopefully you can live with the above solution.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Wercs" wrote:

Just a point:

When data in sheet 1 changes from In to Out (or vice versa), how
can the
filtered results on sheet 2 be similarly updated automatically?

Rgs.



"Ashish Mathur" wrote in message
...
Hi,

Suppose the heading of columns 1,2 and 4 on sheet 1 are A,B
and C. Please
perform the following steps

1. Assign a column heading to all the columns in sheet 1, if
not already
done so. Say the heading are A-M
2. Now type A in a cell below the data, say A25
3. In cell A26, type Out
4. In sheet 2, type A,B and D in B3,C3 and D3.
5. Now click on cell B4 on sheet 2 and go to Data Filter
Advance
Filter;
6. Select the radio button for "Copy to another location";
7. In the list box, please select the range on sheet 1 (Please
select the
header row as well);
8. In the Criteria box, select the range A25:A26 of sheet 1;
9. In the copy to box, select B3:D100 of sheet 2;
10. Click on OK

Hope this helps.



--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Wercs" wrote in message
...
I wish to filter results from one sheet onto a second sheet.
I have a data table containing 13 columns, but only columns
1,2 & 4 are
needed in the filtered results.
Column 1 contains either "In" or "Out". Col.2 a name, Col.4 a
location.

I wish to place on sheet 2 all records shown as "Out".

I've tried the VLOOKUP but guess I'm not getting the right
parameters.

Help appreciated.
Thanks.

PS - I'm working with Excel 2007, but the workbook has to be
compatible
with 2003.
--
Wercs