Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Rikki-Handgards
 
Posts: n/a
Default Getting all rows of data that have a value in a particular column

Hi. Is there a way to list, on a separate sheet, only rows of data with a
value in a specific column? I want to list inventory items (and all data in
that row) as long as there is a value in column F. If there is no value in
column F for that inventory item, I don't want it to be the separate summary
sheet. Is there a way to do this with a formula and not a pivot table?
Thanks!
  #2   Report Post  
Posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default Getting all rows of data that have a value in a particular column

Is there a way to do this with a formula and not a pivot table?

Yes, but whether or not it's practical depends on how many cells from the
row you need returned and in general, how big is the table of data this data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you need all
150 columns of data extracted for each instance of "value", then a formula
approach is not practical.

Biff

"Rikki-Handgards" wrote in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of data with a
value in a specific column? I want to list inventory items (and all data
in
that row) as long as there is a value in column F. If there is no value
in
column F for that inventory item, I don't want it to be the separate
summary
sheet. Is there a way to do this with a formula and not a pivot table?
Thanks!



  #3   Report Post  
Posted to microsoft.public.excel.misc
Rikki-Handgards
 
Posts: n/a
Default Getting all rows of data that have a value in a particular col

Thanks Biff. Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?


Yes, but whether or not it's practical depends on how many cells from the
row you need returned and in general, how big is the table of data this data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you need all
150 columns of data extracted for each instance of "value", then a formula
approach is not practical.

Biff

"Rikki-Handgards" wrote in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of data with a
value in a specific column? I want to list inventory items (and all data
in
that row) as long as there is a value in column F. If there is no value
in
column F for that inventory item, I don't want it to be the separate
summary
sheet. Is there a way to do this with a formula and not a pivot table?
Thanks!




  #4   Report Post  
Posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default Getting all rows of data that have a value in a particular col

Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?


That should be Ok but you'll have to see how it affects performance and then
make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file that
demonstrates this (unless Max beats me to it!<g) and post a link to the
file.

Biff

"Rikki-Handgards" wrote in
message ...
Thanks Biff. Would it still be practical if I had 250 rows of data total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?


Yes, but whether or not it's practical depends on how many cells from the
row you need returned and in general, how big is the table of data this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you need all
150 columns of data extracted for each instance of "value", then a
formula
approach is not practical.

Biff

"Rikki-Handgards" wrote in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of data
with a
value in a specific column? I want to list inventory items (and all
data
in
that row) as long as there is a value in column F. If there is no
value
in
column F for that inventory item, I don't want it to be the separate
summary
sheet. Is there a way to do this with a formula and not a pivot table?
Thanks!






  #5   Report Post  
Posted to microsoft.public.excel.misc
pinmaster
 
Posts: n/a
Default Getting all rows of data that have a value in a particular column

Here's what I would do.

I would copy my 10 columns then paste it into the other worksheet (paste as
link) then use a filter.....filter by values greater than 0 in column F.

HTH
Jean-Guy

"Rikki-Handgards" wrote:

Hi. Is there a way to list, on a separate sheet, only rows of data with a
value in a specific column? I want to list inventory items (and all data in
that row) as long as there is a value in column F. If there is no value in
column F for that inventory item, I don't want it to be the separate summary
sheet. Is there a way to do this with a formula and not a pivot table?
Thanks!



  #6   Report Post  
Posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default Getting all rows of data that have a value in a particular col

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used. This
demonstrates the method I prefer. Just one formula copied to the cells. It's
an array formula, though, and array formulas take longer to calculate.
Depending on the size of your file and other factors, you might not notice
any difference.

Try deleting some data in column F then look at sheet 2 to see how it
updates.

If in your situation you expect that there may be 20-40 rows that meet the
criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?


That should be Ok but you'll have to see how it affects performance and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file that
demonstrates this (unless Max beats me to it!<g) and post a link to the
file.

Biff

"Rikki-Handgards" wrote in
message ...
Thanks Biff. Would it still be practical if I had 250 rows of data total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?

Yes, but whether or not it's practical depends on how many cells from
the
row you need returned and in general, how big is the table of data this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you need
all
150 columns of data extracted for each instance of "value", then a
formula
approach is not practical.

Biff

"Rikki-Handgards" wrote in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of data
with a
value in a specific column? I want to list inventory items (and all
data
in
that row) as long as there is a value in column F. If there is no
value
in
column F for that inventory item, I don't want it to be the separate
summary
sheet. Is there a way to do this with a formula and not a pivot
table?
Thanks!







  #7   Report Post  
Posted to microsoft.public.excel.misc
Rikki-Handgards
 
Posts: n/a
Default Getting all rows of data that have a value in a particular col

Thank you very much Biff, the formula works exactly like I needed it to.
However, I am having problems entering the array formula. I have the normal
formula in a cell and then I select the rows beneath it and hit F2 and press
Ctrl+Shift+Enter but the formulas don't copy correctly. I've tried it
several different ways, but I always get
=IF(ROWS('Sheet1'!$1:1)... in every row instead of
....('Sheet1'!$1:2),...('Sheet1'!$1:3), etc. If I copy the formula down the
column the formulas change like they are supposed to. Then, when I select
them all and enter it as an array formula, they all change back to
('Sheet1'!$1:1) again. Can you tell me what I am doing wrong? Thank you.
-Rikki

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used. This
demonstrates the method I prefer. Just one formula copied to the cells. It's
an array formula, though, and array formulas take longer to calculate.
Depending on the size of your file and other factors, you might not notice
any difference.

Try deleting some data in column F then look at sheet 2 to see how it
updates.

If in your situation you expect that there may be 20-40 rows that meet the
criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?


That should be Ok but you'll have to see how it affects performance and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file that
demonstrates this (unless Max beats me to it!<g) and post a link to the
file.

Biff

"Rikki-Handgards" wrote in
message ...
Thanks Biff. Would it still be practical if I had 250 rows of data total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?

Yes, but whether or not it's practical depends on how many cells from
the
row you need returned and in general, how big is the table of data this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you need
all
150 columns of data extracted for each instance of "value", then a
formula
approach is not practical.

Biff

"Rikki-Handgards" wrote in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of data
with a
value in a specific column? I want to list inventory items (and all
data
in
that row) as long as there is a value in column F. If there is no
value
in
column F for that inventory item, I don't want it to be the separate
summary
sheet. Is there a way to do this with a formula and not a pivot
table?
Thanks!








  #8   Report Post  
Posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default Getting all rows of data that have a value in a particular col

Hi!

Just enter the formula in one cell, the top left cell of the "grid", and
enter it as an array using the key combo of CTRL,SHIFT,ENTER. With the
formula typed into that one top left cell move the cursor (mouse) to the end
of the formula in the formula bar. Then enter it as an array.

Then, just drag copy across the row to the right. Then drag copy down the
columns as needed.

Each cell formula is an array. You don't want to make the entire grid a
single block array.

Biff

"Rikki-Handgards" wrote in
message ...
Thank you very much Biff, the formula works exactly like I needed it to.
However, I am having problems entering the array formula. I have the
normal
formula in a cell and then I select the rows beneath it and hit F2 and
press
Ctrl+Shift+Enter but the formulas don't copy correctly. I've tried it
several different ways, but I always get
=IF(ROWS('Sheet1'!$1:1)... in every row instead of
...('Sheet1'!$1:2),...('Sheet1'!$1:3), etc. If I copy the formula down
the
column the formulas change like they are supposed to. Then, when I select
them all and enter it as an array formula, they all change back to
('Sheet1'!$1:1) again. Can you tell me what I am doing wrong? Thank you.
-Rikki

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used. This
demonstrates the method I prefer. Just one formula copied to the cells.
It's
an array formula, though, and array formulas take longer to calculate.
Depending on the size of your file and other factors, you might not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to see how it
updates.

If in your situation you expect that there may be 20-40 rows that meet
the
criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects performance and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file that
demonstrates this (unless Max beats me to it!<g) and post a link to
the
file.

Biff

"Rikki-Handgards" wrote in
message ...
Thanks Biff. Would it still be practical if I had 250 rows of data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?

Yes, but whether or not it's practical depends on how many cells from
the
row you need returned and in general, how big is the table of data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you need
all
150 columns of data extracted for each instance of "value", then a
formula
approach is not practical.

Biff

"Rikki-Handgards" wrote
in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of data
with a
value in a specific column? I want to list inventory items (and
all
data
in
that row) as long as there is a value in column F. If there is no
value
in
column F for that inventory item, I don't want it to be the
separate
summary
sheet. Is there a way to do this with a formula and not a pivot
table?
Thanks!










  #9   Report Post  
Posted to microsoft.public.excel.misc
Rikki-Handgards
 
Posts: n/a
Default Getting all rows of data that have a value in a particular col

Hello. Thanks so much, it worked! of course :) I appreciate your time Biff.

"Biff" wrote:

Hi!

Just enter the formula in one cell, the top left cell of the "grid", and
enter it as an array using the key combo of CTRL,SHIFT,ENTER. With the
formula typed into that one top left cell move the cursor (mouse) to the end
of the formula in the formula bar. Then enter it as an array.

Then, just drag copy across the row to the right. Then drag copy down the
columns as needed.

Each cell formula is an array. You don't want to make the entire grid a
single block array.

Biff

"Rikki-Handgards" wrote in
message ...
Thank you very much Biff, the formula works exactly like I needed it to.
However, I am having problems entering the array formula. I have the
normal
formula in a cell and then I select the rows beneath it and hit F2 and
press
Ctrl+Shift+Enter but the formulas don't copy correctly. I've tried it
several different ways, but I always get
=IF(ROWS('Sheet1'!$1:1)... in every row instead of
...('Sheet1'!$1:2),...('Sheet1'!$1:3), etc. If I copy the formula down
the
column the formulas change like they are supposed to. Then, when I select
them all and enter it as an array formula, they all change back to
('Sheet1'!$1:1) again. Can you tell me what I am doing wrong? Thank you.
-Rikki

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used. This
demonstrates the method I prefer. Just one formula copied to the cells.
It's
an array formula, though, and array formulas take longer to calculate.
Depending on the size of your file and other factors, you might not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to see how it
updates.

If in your situation you expect that there may be 20-40 rows that meet
the
criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects performance and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file that
demonstrates this (unless Max beats me to it!<g) and post a link to
the
file.

Biff

"Rikki-Handgards" wrote in
message ...
Thanks Biff. Would it still be practical if I had 250 rows of data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?

Yes, but whether or not it's practical depends on how many cells from
the
row you need returned and in general, how big is the table of data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you need
all
150 columns of data extracted for each instance of "value", then a
formula
approach is not practical.

Biff

"Rikki-Handgards" wrote
in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of data
with a
value in a specific column? I want to list inventory items (and
all
data
in
that row) as long as there is a value in column F. If there is no
value
in
column F for that inventory item, I don't want it to be the
separate
summary
sheet. Is there a way to do this with a formula and not a pivot
table?
Thanks!











  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 42
Default Getting all rows of data that have a value in a particular col

I have a list 4 x 316:
county code sales tax
smith 36-02 25.00 5.00
allen 14-17 14.00 1.40
caddo 72-36 .00 .00
park 55 -92 97.00 9.70

In a different part of the spread sheet I would like:

code sales tax county
36-02 25.00 5.00 smith
14-17 14.00 1.40 allen
55-92 97.00 9.70 park

I would like this list updated 'automatically' when something is changed in
the top part.
The numbers in the top are looked up on another spreadsheet and populated
upon opening of the workbook.

Do you need anything else?

Thanks for your help

"Biff" wrote:

Hi!

How about explaining exactly what you want to do (include the DETAILS!) and
if I can figure it out I'll post a fresh link with a sample.

Biff

"Sunny" wrote in message
...
This is exactly what I would liek to do! However, the link no longer
works.
Would you please send it again?

Thanks-
sunny

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used. This
demonstrates the method I prefer. Just one formula copied to the cells.
It's
an array formula, though, and array formulas take longer to calculate.
Depending on the size of your file and other factors, you might not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to see how it
updates.

If in your situation you expect that there may be 20-40 rows that meet
the
criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects performance and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file that
demonstrates this (unless Max beats me to it!<g) and post a link to
the
file.

Biff

"Rikki-Handgards" wrote in
message ...
Thanks Biff. Would it still be practical if I had 250 rows of data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?

Yes, but whether or not it's practical depends on how many cells from
the
row you need returned and in general, how big is the table of data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you need
all
150 columns of data extracted for each instance of "value", then a
formula
approach is not practical.

Biff

"Rikki-Handgards" wrote
in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of data
with a
value in a specific column? I want to list inventory items (and
all
data
in
that row) as long as there is a value in column F. If there is no
value
in
column F for that inventory item, I don't want it to be the
separate
summary
sheet. Is there a way to do this with a formula and not a pivot
table?
Thanks!













  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,688
Default Getting all rows of data that have a value in a particular col

Ok.....

So you want to extract data where there is a value greater than 0 in *BOTH*
Sales and Tax?

Is the size of the table always the same? Always 4c x 316r ?

What is the cell address where the table starts? For example, based on you
sample Smith appears in cell A2. The reason I "need" to know this is because
the formula will contain an expression based on this starting cell. A lot of
times a poster will copy the formula from a reply and change this expression
incorrectly and that ends up causing the formula to not work properly
leading to a bunch of follow-up questions.

This should be no problem.

Biff

"Sunny" wrote in message
...
I have a list 4 x 316:
county code sales tax
smith 36-02 25.00 5.00
allen 14-17 14.00 1.40
caddo 72-36 .00 .00
park 55 -92 97.00 9.70

In a different part of the spread sheet I would like:

code sales tax county
36-02 25.00 5.00 smith
14-17 14.00 1.40 allen
55-92 97.00 9.70 park

I would like this list updated 'automatically' when something is changed
in
the top part.
The numbers in the top are looked up on another spreadsheet and populated
upon opening of the workbook.

Do you need anything else?

Thanks for your help

"Biff" wrote:

Hi!

How about explaining exactly what you want to do (include the DETAILS!)
and
if I can figure it out I'll post a fresh link with a sample.

Biff

"Sunny" wrote in message
...
This is exactly what I would liek to do! However, the link no longer
works.
Would you please send it again?

Thanks-
sunny

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used. This
demonstrates the method I prefer. Just one formula copied to the
cells.
It's
an array formula, though, and array formulas take longer to calculate.
Depending on the size of your file and other factors, you might not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to see how it
updates.

If in your situation you expect that there may be 20-40 rows that meet
the
criteria, then you'd need to copy the formula to AT LEAST 40 rows. In
the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects performance
and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file
that
demonstrates this (unless Max beats me to it!<g) and post a link to
the
file.

Biff

"Rikki-Handgards" wrote
in
message ...
Thanks Biff. Would it still be practical if I had 250 rows of data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?

Yes, but whether or not it's practical depends on how many cells
from
the
row you need returned and in general, how big is the table of data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you
need
all
150 columns of data extracted for each instance of "value", then
a
formula
approach is not practical.

Biff

"Rikki-Handgards"
wrote
in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of
data
with a
value in a specific column? I want to list inventory items (and
all
data
in
that row) as long as there is a value in column F. If there is
no
value
in
column F for that inventory item, I don't want it to be the
separate
summary
sheet. Is there a way to do this with a formula and not a pivot
table?
Thanks!













  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 42
Default Getting all rows of data that have a value in a particular col

316 is a constant number.
It starts in A4 and can land anyplace right of W.

"Biff" wrote:

Ok.....

So you want to extract data where there is a value greater than 0 in *BOTH*
Sales and Tax?

Is the size of the table always the same? Always 4c x 316r ?

What is the cell address where the table starts? For example, based on you
sample Smith appears in cell A2. The reason I "need" to know this is because
the formula will contain an expression based on this starting cell. A lot of
times a poster will copy the formula from a reply and change this expression
incorrectly and that ends up causing the formula to not work properly
leading to a bunch of follow-up questions.

This should be no problem.

Biff

"Sunny" wrote in message
...
I have a list 4 x 316:
county code sales tax
smith 36-02 25.00 5.00
allen 14-17 14.00 1.40
caddo 72-36 .00 .00
park 55 -92 97.00 9.70

In a different part of the spread sheet I would like:

code sales tax county
36-02 25.00 5.00 smith
14-17 14.00 1.40 allen
55-92 97.00 9.70 park

I would like this list updated 'automatically' when something is changed
in
the top part.
The numbers in the top are looked up on another spreadsheet and populated
upon opening of the workbook.

Do you need anything else?

Thanks for your help

"Biff" wrote:

Hi!

How about explaining exactly what you want to do (include the DETAILS!)
and
if I can figure it out I'll post a fresh link with a sample.

Biff

"Sunny" wrote in message
...
This is exactly what I would liek to do! However, the link no longer
works.
Would you please send it again?

Thanks-
sunny

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used. This
demonstrates the method I prefer. Just one formula copied to the
cells.
It's
an array formula, though, and array formulas take longer to calculate.
Depending on the size of your file and other factors, you might not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to see how it
updates.

If in your situation you expect that there may be 20-40 rows that meet
the
criteria, then you'd need to copy the formula to AT LEAST 40 rows. In
the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects performance
and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file
that
demonstrates this (unless Max beats me to it!<g) and post a link to
the
file.

Biff

"Rikki-Handgards" wrote
in
message ...
Thanks Biff. Would it still be practical if I had 250 rows of data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?

Yes, but whether or not it's practical depends on how many cells
from
the
row you need returned and in general, how big is the table of data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you
need
all
150 columns of data extracted for each instance of "value", then
a
formula
approach is not practical.

Biff

"Rikki-Handgards"
wrote
in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of
data
with a
value in a specific column? I want to list inventory items (and
all
data
in
that row) as long as there is a value in column F. If there is
no
value
in
column F for that inventory item, I don't want it to be the
separate
summary
sheet. Is there a way to do this with a formula and not a pivot
table?
Thanks!














  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,688
Default Getting all rows of data that have a value in a particular col

can land anyplace right of W

What does that mean? You want the extracted data to be placed anywhere to
the right of column W?

Biff

"Sunny" wrote in message
...
316 is a constant number.
It starts in A4 and can land anyplace right of W.

"Biff" wrote:

Ok.....

So you want to extract data where there is a value greater than 0 in
*BOTH*
Sales and Tax?

Is the size of the table always the same? Always 4c x 316r ?

What is the cell address where the table starts? For example, based on
you
sample Smith appears in cell A2. The reason I "need" to know this is
because
the formula will contain an expression based on this starting cell. A lot
of
times a poster will copy the formula from a reply and change this
expression
incorrectly and that ends up causing the formula to not work properly
leading to a bunch of follow-up questions.

This should be no problem.

Biff

"Sunny" wrote in message
...
I have a list 4 x 316:
county code sales tax
smith 36-02 25.00 5.00
allen 14-17 14.00 1.40
caddo 72-36 .00 .00
park 55 -92 97.00 9.70

In a different part of the spread sheet I would like:

code sales tax county
36-02 25.00 5.00 smith
14-17 14.00 1.40 allen
55-92 97.00 9.70 park

I would like this list updated 'automatically' when something is
changed
in
the top part.
The numbers in the top are looked up on another spreadsheet and
populated
upon opening of the workbook.

Do you need anything else?

Thanks for your help

"Biff" wrote:

Hi!

How about explaining exactly what you want to do (include the
DETAILS!)
and
if I can figure it out I'll post a fresh link with a sample.

Biff

"Sunny" wrote in message
...
This is exactly what I would liek to do! However, the link no
longer
works.
Would you please send it again?

Thanks-
sunny

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used. This
demonstrates the method I prefer. Just one formula copied to the
cells.
It's
an array formula, though, and array formulas take longer to
calculate.
Depending on the size of your file and other factors, you might not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to see how
it
updates.

If in your situation you expect that there may be 20-40 rows that
meet
the
criteria, then you'd need to copy the formula to AT LEAST 40 rows.
In
the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects
performance
and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file
that
demonstrates this (unless Max beats me to it!<g) and post a link
to
the
file.

Biff

"Rikki-Handgards"
wrote
in
message
...
Thanks Biff. Would it still be practical if I had 250 rows of
data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot
table?

Yes, but whether or not it's practical depends on how many
cells
from
the
row you need returned and in general, how big is the table of
data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and
you
need
all
150 columns of data extracted for each instance of "value",
then
a
formula
approach is not practical.

Biff

"Rikki-Handgards"
wrote
in
message
...
Hi. Is there a way to list, on a separate sheet, only rows
of
data
with a
value in a specific column? I want to list inventory items
(and
all
data
in
that row) as long as there is a value in column F. If there
is
no
value
in
column F for that inventory item, I don't want it to be the
separate
summary
sheet. Is there a way to do this with a formula and not a
pivot
table?
Thanks!
















  #14   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 42
Default Getting all rows of data that have a value in a particular col

yes and only the sales column needs to be checked for 0

"Biff" wrote:

can land anyplace right of W


What does that mean? You want the extracted data to be placed anywhere to
the right of column W?

Biff

"Sunny" wrote in message
...
316 is a constant number.
It starts in A4 and can land anyplace right of W.

"Biff" wrote:

Ok.....

So you want to extract data where there is a value greater than 0 in
*BOTH*
Sales and Tax?

Is the size of the table always the same? Always 4c x 316r ?

What is the cell address where the table starts? For example, based on
you
sample Smith appears in cell A2. The reason I "need" to know this is
because
the formula will contain an expression based on this starting cell. A lot
of
times a poster will copy the formula from a reply and change this
expression
incorrectly and that ends up causing the formula to not work properly
leading to a bunch of follow-up questions.

This should be no problem.

Biff

"Sunny" wrote in message
...
I have a list 4 x 316:
county code sales tax
smith 36-02 25.00 5.00
allen 14-17 14.00 1.40
caddo 72-36 .00 .00
park 55 -92 97.00 9.70

In a different part of the spread sheet I would like:

code sales tax county
36-02 25.00 5.00 smith
14-17 14.00 1.40 allen
55-92 97.00 9.70 park

I would like this list updated 'automatically' when something is
changed
in
the top part.
The numbers in the top are looked up on another spreadsheet and
populated
upon opening of the workbook.

Do you need anything else?

Thanks for your help

"Biff" wrote:

Hi!

How about explaining exactly what you want to do (include the
DETAILS!)
and
if I can figure it out I'll post a fresh link with a sample.

Biff

"Sunny" wrote in message
...
This is exactly what I would liek to do! However, the link no
longer
works.
Would you please send it again?

Thanks-
sunny

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used. This
demonstrates the method I prefer. Just one formula copied to the
cells.
It's
an array formula, though, and array formulas take longer to
calculate.
Depending on the size of your file and other factors, you might not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to see how
it
updates.

If in your situation you expect that there may be 20-40 rows that
meet
the
criteria, then you'd need to copy the formula to AT LEAST 40 rows.
In
the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects
performance
and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file
that
demonstrates this (unless Max beats me to it!<g) and post a link
to
the
file.

Biff

"Rikki-Handgards"
wrote
in
message
...
Thanks Biff. Would it still be practical if I had 250 rows of
data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot
table?

Yes, but whether or not it's practical depends on how many
cells
from
the
row you need returned and in general, how big is the table of
data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and
you
need
all
150 columns of data extracted for each instance of "value",
then
a
formula
approach is not practical.

Biff

"Rikki-Handgards"
wrote
in
message
...
Hi. Is there a way to list, on a separate sheet, only rows
of
data
with a
value in a specific column? I want to list inventory items
(and
all
data
in
that row) as long as there is a value in column F. If there
is
no
value
in
column F for that inventory item, I don't want it to be the
separate
summary
sheet. Is there a way to do this with a formula and not a
pivot
table?
Thanks!

















  #15   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,688
Default Getting all rows of data that have a value in a particular col

Ok, no problem!

I'll be away from the computer for a few hours so check back later.

Biff

"Sunny" wrote in message
...
yes and only the sales column needs to be checked for 0

"Biff" wrote:

can land anyplace right of W


What does that mean? You want the extracted data to be placed anywhere to
the right of column W?

Biff

"Sunny" wrote in message
...
316 is a constant number.
It starts in A4 and can land anyplace right of W.

"Biff" wrote:

Ok.....

So you want to extract data where there is a value greater than 0 in
*BOTH*
Sales and Tax?

Is the size of the table always the same? Always 4c x 316r ?

What is the cell address where the table starts? For example, based on
you
sample Smith appears in cell A2. The reason I "need" to know this is
because
the formula will contain an expression based on this starting cell. A
lot
of
times a poster will copy the formula from a reply and change this
expression
incorrectly and that ends up causing the formula to not work properly
leading to a bunch of follow-up questions.

This should be no problem.

Biff

"Sunny" wrote in message
...
I have a list 4 x 316:
county code sales tax
smith 36-02 25.00 5.00
allen 14-17 14.00 1.40
caddo 72-36 .00 .00
park 55 -92 97.00 9.70

In a different part of the spread sheet I would like:

code sales tax county
36-02 25.00 5.00 smith
14-17 14.00 1.40 allen
55-92 97.00 9.70 park

I would like this list updated 'automatically' when something is
changed
in
the top part.
The numbers in the top are looked up on another spreadsheet and
populated
upon opening of the workbook.

Do you need anything else?

Thanks for your help

"Biff" wrote:

Hi!

How about explaining exactly what you want to do (include the
DETAILS!)
and
if I can figure it out I'll post a fresh link with a sample.

Biff

"Sunny" wrote in message
...
This is exactly what I would liek to do! However, the link no
longer
works.
Would you please send it again?

Thanks-
sunny

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used.
This
demonstrates the method I prefer. Just one formula copied to the
cells.
It's
an array formula, though, and array formulas take longer to
calculate.
Depending on the size of your file and other factors, you might
not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to see
how
it
updates.

If in your situation you expect that there may be 20-40 rows
that
meet
the
criteria, then you'd need to copy the formula to AT LEAST 40
rows.
In
the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total
and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects
performance
and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample
file
that
demonstrates this (unless Max beats me to it!<g) and post a
link
to
the
file.

Biff

"Rikki-Handgards"
wrote
in
message
...
Thanks Biff. Would it still be practical if I had 250 rows
of
data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot
table?

Yes, but whether or not it's practical depends on how many
cells
from
the
row you need returned and in general, how big is the table
of
data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and
you
need
all
150 columns of data extracted for each instance of "value",
then
a
formula
approach is not practical.

Biff

"Rikki-Handgards"

wrote
in
message
...
Hi. Is there a way to list, on a separate sheet, only
rows
of
data
with a
value in a specific column? I want to list inventory
items
(and
all
data
in
that row) as long as there is a value in column F. If
there
is
no
value
in
column F for that inventory item, I don't want it to be
the
separate
summary
sheet. Is there a way to do this with a formula and not a
pivot
table?
Thanks!





















  #16   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 42
Default Getting all rows of data that have a value in a particular col

thanks!

"Biff" wrote:

Ok, no problem!

I'll be away from the computer for a few hours so check back later.

Biff

"Sunny" wrote in message
...
yes and only the sales column needs to be checked for 0

"Biff" wrote:

can land anyplace right of W

What does that mean? You want the extracted data to be placed anywhere to
the right of column W?

Biff

"Sunny" wrote in message
...
316 is a constant number.
It starts in A4 and can land anyplace right of W.

"Biff" wrote:

Ok.....

So you want to extract data where there is a value greater than 0 in
*BOTH*
Sales and Tax?

Is the size of the table always the same? Always 4c x 316r ?

What is the cell address where the table starts? For example, based on
you
sample Smith appears in cell A2. The reason I "need" to know this is
because
the formula will contain an expression based on this starting cell. A
lot
of
times a poster will copy the formula from a reply and change this
expression
incorrectly and that ends up causing the formula to not work properly
leading to a bunch of follow-up questions.

This should be no problem.

Biff

"Sunny" wrote in message
...
I have a list 4 x 316:
county code sales tax
smith 36-02 25.00 5.00
allen 14-17 14.00 1.40
caddo 72-36 .00 .00
park 55 -92 97.00 9.70

In a different part of the spread sheet I would like:

code sales tax county
36-02 25.00 5.00 smith
14-17 14.00 1.40 allen
55-92 97.00 9.70 park

I would like this list updated 'automatically' when something is
changed
in
the top part.
The numbers in the top are looked up on another spreadsheet and
populated
upon opening of the workbook.

Do you need anything else?

Thanks for your help

"Biff" wrote:

Hi!

How about explaining exactly what you want to do (include the
DETAILS!)
and
if I can figure it out I'll post a fresh link with a sample.

Biff

"Sunny" wrote in message
...
This is exactly what I would liek to do! However, the link no
longer
works.
Would you please send it again?

Thanks-
sunny

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used.
This
demonstrates the method I prefer. Just one formula copied to the
cells.
It's
an array formula, though, and array formulas take longer to
calculate.
Depending on the size of your file and other factors, you might
not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to see
how
it
updates.

If in your situation you expect that there may be 20-40 rows
that
meet
the
criteria, then you'd need to copy the formula to AT LEAST 40
rows.
In
the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total
and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects
performance
and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample
file
that
demonstrates this (unless Max beats me to it!<g) and post a
link
to
the
file.

Biff

"Rikki-Handgards"
wrote
in
message
...
Thanks Biff. Would it still be practical if I had 250 rows
of
data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot
table?

Yes, but whether or not it's practical depends on how many
cells
from
the
row you need returned and in general, how big is the table
of
data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and
you
need
all
150 columns of data extracted for each instance of "value",
then
a
formula
approach is not practical.

Biff

"Rikki-Handgards"

wrote
in
message
...
Hi. Is there a way to list, on a separate sheet, only
rows
of
data
with a
value in a specific column? I want to list inventory
items
(and
all
data
in
that row) as long as there is a value in column F. If
there
is
no
value
in
column F for that inventory item, I don't want it to be
the
separate
summary
sheet. Is there a way to do this with a formula and not a
pivot
table?
Thanks!




















  #17   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,688
Default Getting all rows of data that have a value in a particular col

Ok, here you go:

Sample file:

Sample extract.xls 18.5kb

http://cjoint.com/?ipe25QHXQb

I took a little bit of a different approach on this one. I saw your other
post!!!!!

If you have 50 sheets and they average 300 rows one array formula copied to
that many cells would really slow things down!

So, I used just one array formula in column X and then simple Vlookups in
columns Y and Z and then a simple Index/Match in column AA. Also, another
reason for the different formula is that you want the extracted data in a
slightly different sequence. I'm assuming that the "code" is unique to the
county? That's what I based the lookup formulas on.

So, all you should have to do is change the references to the actual end of
range of your data. You'll have to copy the formulas down to enough rows so
that they pick up all the related data. Typically, how many entries will
have 0 sales? 10? 20? 200? That's how many rows you would need to copy the
formulas to.

Are you familar with array formulas?

How to enter an array formula:

http://cpearson.com/excel/array.htm

Biff

"Sunny" wrote in message
...
thanks!

"Biff" wrote:

Ok, no problem!

I'll be away from the computer for a few hours so check back later.

Biff

"Sunny" wrote in message
...
yes and only the sales column needs to be checked for 0

"Biff" wrote:

can land anyplace right of W

What does that mean? You want the extracted data to be placed anywhere
to
the right of column W?

Biff

"Sunny" wrote in message
...
316 is a constant number.
It starts in A4 and can land anyplace right of W.

"Biff" wrote:

Ok.....

So you want to extract data where there is a value greater than 0
in
*BOTH*
Sales and Tax?

Is the size of the table always the same? Always 4c x 316r ?

What is the cell address where the table starts? For example, based
on
you
sample Smith appears in cell A2. The reason I "need" to know this
is
because
the formula will contain an expression based on this starting cell.
A
lot
of
times a poster will copy the formula from a reply and change this
expression
incorrectly and that ends up causing the formula to not work
properly
leading to a bunch of follow-up questions.

This should be no problem.

Biff

"Sunny" wrote in message
...
I have a list 4 x 316:
county code sales tax
smith 36-02 25.00 5.00
allen 14-17 14.00 1.40
caddo 72-36 .00 .00
park 55 -92 97.00 9.70

In a different part of the spread sheet I would like:

code sales tax county
36-02 25.00 5.00 smith
14-17 14.00 1.40 allen
55-92 97.00 9.70 park

I would like this list updated 'automatically' when something is
changed
in
the top part.
The numbers in the top are looked up on another spreadsheet and
populated
upon opening of the workbook.

Do you need anything else?

Thanks for your help

"Biff" wrote:

Hi!

How about explaining exactly what you want to do (include the
DETAILS!)
and
if I can figure it out I'll post a fresh link with a sample.

Biff

"Sunny" wrote in message
...
This is exactly what I would liek to do! However, the link no
longer
works.
Would you please send it again?

Thanks-
sunny

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used.
This
demonstrates the method I prefer. Just one formula copied to
the
cells.
It's
an array formula, though, and array formulas take longer to
calculate.
Depending on the size of your file and other factors, you
might
not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to
see
how
it
updates.

If in your situation you expect that there may be 20-40 rows
that
meet
the
criteria, then you'd need to copy the formula to AT LEAST 40
rows.
In
the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data
total
and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects
performance
and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a
sample
file
that
demonstrates this (unless Max beats me to it!<g) and post
a
link
to
the
file.

Biff

"Rikki-Handgards"

wrote
in
message
...
Thanks Biff. Would it still be practical if I had 250
rows
of
data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot
table?

Yes, but whether or not it's practical depends on how
many
cells
from
the
row you need returned and in general, how big is the
table
of
data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns
and
you
need
all
150 columns of data extracted for each instance of
"value",
then
a
formula
approach is not practical.

Biff

"Rikki-Handgards"

wrote
in
message
...
Hi. Is there a way to list, on a separate sheet, only
rows
of
data
with a
value in a specific column? I want to list inventory
items
(and
all
data
in
that row) as long as there is a value in column F. If
there
is
no
value
in
column F for that inventory item, I don't want it to be
the
separate
summary
sheet. Is there a way to do this with a formula and
not a
pivot
table?
Thanks!






















  #18   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 42
Default Getting all rows of data that have a value in a particular col

Sorry it took so long . . . insane week.
This works exactly like I want it to on the lines I got from your example.
I can't seem to make the copy to other lines work. Is there a trick?

"Biff" wrote:

Ok, here you go:

Sample file:

Sample extract.xls 18.5kb

http://cjoint.com/?ipe25QHXQb

I took a little bit of a different approach on this one. I saw your other
post!!!!!

If you have 50 sheets and they average 300 rows one array formula copied to
that many cells would really slow things down!

So, I used just one array formula in column X and then simple Vlookups in
columns Y and Z and then a simple Index/Match in column AA. Also, another
reason for the different formula is that you want the extracted data in a
slightly different sequence. I'm assuming that the "code" is unique to the
county? That's what I based the lookup formulas on.

So, all you should have to do is change the references to the actual end of
range of your data. You'll have to copy the formulas down to enough rows so
that they pick up all the related data. Typically, how many entries will
have 0 sales? 10? 20? 200? That's how many rows you would need to copy the
formulas to.

Are you familar with array formulas?

How to enter an array formula:

http://cpearson.com/excel/array.htm

Biff

"Sunny" wrote in message
...
thanks!

"Biff" wrote:

Ok, no problem!

I'll be away from the computer for a few hours so check back later.

Biff

"Sunny" wrote in message
...
yes and only the sales column needs to be checked for 0

"Biff" wrote:

can land anyplace right of W

What does that mean? You want the extracted data to be placed anywhere
to
the right of column W?

Biff

"Sunny" wrote in message
...
316 is a constant number.
It starts in A4 and can land anyplace right of W.

"Biff" wrote:

Ok.....

So you want to extract data where there is a value greater than 0
in
*BOTH*
Sales and Tax?

Is the size of the table always the same? Always 4c x 316r ?

What is the cell address where the table starts? For example, based
on
you
sample Smith appears in cell A2. The reason I "need" to know this
is
because
the formula will contain an expression based on this starting cell.
A
lot
of
times a poster will copy the formula from a reply and change this
expression
incorrectly and that ends up causing the formula to not work
properly
leading to a bunch of follow-up questions.

This should be no problem.

Biff

"Sunny" wrote in message
...
I have a list 4 x 316:
county code sales tax
smith 36-02 25.00 5.00
allen 14-17 14.00 1.40
caddo 72-36 .00 .00
park 55 -92 97.00 9.70

In a different part of the spread sheet I would like:

code sales tax county
36-02 25.00 5.00 smith
14-17 14.00 1.40 allen
55-92 97.00 9.70 park

I would like this list updated 'automatically' when something is
changed
in
the top part.
The numbers in the top are looked up on another spreadsheet and
populated
upon opening of the workbook.

Do you need anything else?

Thanks for your help

"Biff" wrote:

Hi!

How about explaining exactly what you want to do (include the
DETAILS!)
and
if I can figure it out I'll post a fresh link with a sample.

Biff

"Sunny" wrote in message
...
This is exactly what I would liek to do! However, the link no
longer
works.
Would you please send it again?

Thanks-
sunny

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used.
This
demonstrates the method I prefer. Just one formula copied to
the
cells.
It's
an array formula, though, and array formulas take longer to
calculate.
Depending on the size of your file and other factors, you
might
not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to
see
how
it
updates.

If in your situation you expect that there may be 20-40 rows
that
meet
the
criteria, then you'd need to copy the formula to AT LEAST 40
rows.
In
the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data
total
and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects
performance
and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a
sample
file
that
demonstrates this (unless Max beats me to it!<g) and post
a
link
to
the
file.

Biff

"Rikki-Handgards"

wrote
in
message
...
Thanks Biff. Would it still be practical if I had 250
rows
of
data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot
table?

Yes, but whether or not it's practical depends on how
many
cells
from
the
row you need returned and in general, how big is the
table
of
data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns
and
you
need
all
150 columns of data extracted for each instance of
"value",
then
a
formula
approach is not practical.

Biff

"Rikki-Handgards"

wrote
in
message
...
Hi. Is there a way to list, on a separate sheet, only
rows
of
data
with a
value in a specific column? I want to list inventory
items
(and
all
data
in
that row) as long as there is a value in column F. If
there
is
no
value
in
column F for that inventory item, I don't want it to be
the
separate
summary
sheet. Is there a way to do this with a formula and
not a
pivot
table?
Thanks!














  #19   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,688
Default Getting all rows of data that have a value in a particular col

Not sure I understand what you mean?

Biff

"Sunny" wrote in message
...
Sorry it took so long . . . insane week.
This works exactly like I want it to on the lines I got from your example.
I can't seem to make the copy to other lines work. Is there a trick?

"Biff" wrote:

Ok, here you go:

Sample file:

Sample extract.xls 18.5kb

http://cjoint.com/?ipe25QHXQb

I took a little bit of a different approach on this one. I saw your other
post!!!!!

If you have 50 sheets and they average 300 rows one array formula copied
to
that many cells would really slow things down!

So, I used just one array formula in column X and then simple Vlookups in
columns Y and Z and then a simple Index/Match in column AA. Also, another
reason for the different formula is that you want the extracted data in a
slightly different sequence. I'm assuming that the "code" is unique to
the
county? That's what I based the lookup formulas on.

So, all you should have to do is change the references to the actual end
of
range of your data. You'll have to copy the formulas down to enough rows
so
that they pick up all the related data. Typically, how many entries will
have 0 sales? 10? 20? 200? That's how many rows you would need to copy
the
formulas to.

Are you familar with array formulas?

How to enter an array formula:

http://cpearson.com/excel/array.htm

Biff

"Sunny" wrote in message
...
thanks!

"Biff" wrote:

Ok, no problem!

I'll be away from the computer for a few hours so check back later.

Biff

"Sunny" wrote in message
...
yes and only the sales column needs to be checked for 0

"Biff" wrote:

can land anyplace right of W

What does that mean? You want the extracted data to be placed
anywhere
to
the right of column W?

Biff

"Sunny" wrote in message
...
316 is a constant number.
It starts in A4 and can land anyplace right of W.

"Biff" wrote:

Ok.....

So you want to extract data where there is a value greater than
0
in
*BOTH*
Sales and Tax?

Is the size of the table always the same? Always 4c x 316r ?

What is the cell address where the table starts? For example,
based
on
you
sample Smith appears in cell A2. The reason I "need" to know
this
is
because
the formula will contain an expression based on this starting
cell.
A
lot
of
times a poster will copy the formula from a reply and change
this
expression
incorrectly and that ends up causing the formula to not work
properly
leading to a bunch of follow-up questions.

This should be no problem.

Biff

"Sunny" wrote in message
...
I have a list 4 x 316:
county code sales tax
smith 36-02 25.00 5.00
allen 14-17 14.00 1.40
caddo 72-36 .00 .00
park 55 -92 97.00 9.70

In a different part of the spread sheet I would like:

code sales tax county
36-02 25.00 5.00 smith
14-17 14.00 1.40 allen
55-92 97.00 9.70 park

I would like this list updated 'automatically' when something
is
changed
in
the top part.
The numbers in the top are looked up on another spreadsheet
and
populated
upon opening of the workbook.

Do you need anything else?

Thanks for your help

"Biff" wrote:

Hi!

How about explaining exactly what you want to do (include the
DETAILS!)
and
if I can figure it out I'll post a fresh link with a sample.

Biff

"Sunny" wrote in message
...
This is exactly what I would liek to do! However, the link
no
longer
works.
Would you please send it again?

Thanks-
sunny

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be
used.
This
demonstrates the method I prefer. Just one formula copied
to
the
cells.
It's
an array formula, though, and array formulas take longer
to
calculate.
Depending on the size of your file and other factors, you
might
not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to
see
how
it
updates.

If in your situation you expect that there may be 20-40
rows
that
meet
the
criteria, then you'd need to copy the formula to AT LEAST
40
rows.
In
the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data
total
and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects
performance
and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a
sample
file
that
demonstrates this (unless Max beats me to it!<g) and
post
a
link
to
the
file.

Biff

"Rikki-Handgards"

wrote
in
message
...
Thanks Biff. Would it still be practical if I had 250
rows
of
data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a
pivot
table?

Yes, but whether or not it's practical depends on how
many
cells
from
the
row you need returned and in general, how big is the
table
of
data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150
columns
and
you
need
all
150 columns of data extracted for each instance of
"value",
then
a
formula
approach is not practical.

Biff

"Rikki-Handgards"

wrote
in
message
...
Hi. Is there a way to list, on a separate sheet,
only
rows
of
data
with a
value in a specific column? I want to list
inventory
items
(and
all
data
in
that row) as long as there is a value in column F.
If
there
is
no
value
in
column F for that inventory item, I don't want it to
be
the
separate
summary
sheet. Is there a way to do this with a formula and
not a
pivot
table?
Thanks!
















  #20   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 42
Default Getting all rows of data that have a value in a particular col

Normally I just drag down to other cells. When I do that with this formula,
I get #REF in the destination cells. I even tried copy and paste. What
other way is there to get a perfect formula into subsequent rows?

sunny

"Biff" wrote:

Not sure I understand what you mean?

Biff

"Sunny" wrote in message
...
Sorry it took so long . . . insane week.
This works exactly like I want it to on the lines I got from your example.
I can't seem to make the copy to other lines work. Is there a trick?

"Biff" wrote:

Ok, here you go:

Sample file:

Sample extract.xls 18.5kb

http://cjoint.com/?ipe25QHXQb

I took a little bit of a different approach on this one. I saw your other
post!!!!!

If you have 50 sheets and they average 300 rows one array formula copied
to
that many cells would really slow things down!

So, I used just one array formula in column X and then simple Vlookups in
columns Y and Z and then a simple Index/Match in column AA. Also, another
reason for the different formula is that you want the extracted data in a
slightly different sequence. I'm assuming that the "code" is unique to
the
county? That's what I based the lookup formulas on.

So, all you should have to do is change the references to the actual end
of
range of your data. You'll have to copy the formulas down to enough rows
so
that they pick up all the related data. Typically, how many entries will
have 0 sales? 10? 20? 200? That's how many rows you would need to copy
the
formulas to.

Are you familar with array formulas?

How to enter an array formula:

http://cpearson.com/excel/array.htm

Biff

"Sunny" wrote in message
...
thanks!

"Biff" wrote:

Ok, no problem!

I'll be away from the computer for a few hours so check back later.

Biff

"Sunny" wrote in message
...
yes and only the sales column needs to be checked for 0

"Biff" wrote:

can land anyplace right of W

What does that mean? You want the extracted data to be placed
anywhere
to
the right of column W?

Biff

"Sunny" wrote in message
...
316 is a constant number.
It starts in A4 and can land anyplace right of W.

"Biff" wrote:

Ok.....

So you want to extract data where there is a value greater than
0
in
*BOTH*
Sales and Tax?

Is the size of the table always the same? Always 4c x 316r ?

What is the cell address where the table starts? For example,
based
on
you
sample Smith appears in cell A2. The reason I "need" to know
this
is
because
the formula will contain an expression based on this starting
cell.
A
lot
of
times a poster will copy the formula from a reply and change
this
expression
incorrectly and that ends up causing the formula to not work
properly
leading to a bunch of follow-up questions.

This should be no problem.

Biff

"Sunny" wrote in message
...
I have a list 4 x 316:
county code sales tax
smith 36-02 25.00 5.00
allen 14-17 14.00 1.40
caddo 72-36 .00 .00
park 55 -92 97.00 9.70

In a different part of the spread sheet I would like:

code sales tax county
36-02 25.00 5.00 smith
14-17 14.00 1.40 allen
55-92 97.00 9.70 park

I would like this list updated 'automatically' when something
is
changed
in
the top part.
The numbers in the top are looked up on another spreadsheet
and
populated
upon opening of the workbook.

Do you need anything else?

Thanks for your help

"Biff" wrote:

Hi!

How about explaining exactly what you want to do (include the
DETAILS!)
and
if I can figure it out I'll post a fresh link with a sample.

Biff

"Sunny" wrote in message
...
This is exactly what I would liek to do! However, the link
no
longer
works.
Would you please send it again?

Thanks-
sunny

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be
used.
This
demonstrates the method I prefer. Just one formula copied
to
the
cells.
It's
an array formula, though, and array formulas take longer
to
calculate.
Depending on the size of your file and other factors, you
might
not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to
see
how
it
updates.

If in your situation you expect that there may be 20-40
rows
that
meet
the
criteria, then you'd need to copy the formula to AT LEAST
40
rows.
In
the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data
total
and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects
performance
and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a
sample
file
that
demonstrates this (unless Max beats me to it!<g) and
post
a
link
to
the
file.

Biff

"Rikki-Handgards"

wrote
in
message
...
Thanks Biff. Would it still be practical if I had 250
rows
of
data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a
pivot
table?

Yes, but whether or not it's practical depends on how
many
cells
from
the
row you need returned and in general, how big is the
table
of
data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150
columns
and
you
need
all
150 columns of data extracted for each instance of
"value",
then
a
formula
approach is not practical.

Biff

"Rikki-Handgards"

wrote
in
message
...
Hi. Is there a way to list, on a separate sheet,
only
rows
of
data
with a



  #21   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,688
Default Getting all rows of data that have a value in a particular col

Can you send me a copy of your file? I don't need the whole thing if it's a
huge file, just the sheet where you're getting these errors. The only thing
that I can think of that would cause #REF! errors is if you were somehow
returning references that were outside of the range in the INDEX portion of
the formula. If you can send me the file I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

"Sunny" wrote in message
...
Normally I just drag down to other cells. When I do that with this
formula,
I get #REF in the destination cells. I even tried copy and paste. What
other way is there to get a perfect formula into subsequent rows?

sunny

"Biff" wrote:

Not sure I understand what you mean?

Biff

"Sunny" wrote in message
...
Sorry it took so long . . . insane week.
This works exactly like I want it to on the lines I got from your
example.
I can't seem to make the copy to other lines work. Is there a trick?

"Biff" wrote:

Ok, here you go:

Sample file:

Sample extract.xls 18.5kb

http://cjoint.com/?ipe25QHXQb

I took a little bit of a different approach on this one. I saw your
other
post!!!!!

If you have 50 sheets and they average 300 rows one array formula
copied
to
that many cells would really slow things down!

So, I used just one array formula in column X and then simple Vlookups
in
columns Y and Z and then a simple Index/Match in column AA. Also,
another
reason for the different formula is that you want the extracted data
in a
slightly different sequence. I'm assuming that the "code" is unique to
the
county? That's what I based the lookup formulas on.

So, all you should have to do is change the references to the actual
end
of
range of your data. You'll have to copy the formulas down to enough
rows
so
that they pick up all the related data. Typically, how many entries
will
have 0 sales? 10? 20? 200? That's how many rows you would need to copy
the
formulas to.

Are you familar with array formulas?

How to enter an array formula:

http://cpearson.com/excel/array.htm

Biff

"Sunny" wrote in message
...
thanks!

"Biff" wrote:

Ok, no problem!

I'll be away from the computer for a few hours so check back later.

Biff

"Sunny" wrote in message
...
yes and only the sales column needs to be checked for 0

"Biff" wrote:

can land anyplace right of W

What does that mean? You want the extracted data to be placed
anywhere
to
the right of column W?

Biff

"Sunny" wrote in message
...
316 is a constant number.
It starts in A4 and can land anyplace right of W.

"Biff" wrote:

Ok.....

So you want to extract data where there is a value greater
than
0
in
*BOTH*
Sales and Tax?

Is the size of the table always the same? Always 4c x 316r ?

What is the cell address where the table starts? For example,
based
on
you
sample Smith appears in cell A2. The reason I "need" to know
this
is
because
the formula will contain an expression based on this starting
cell.
A
lot
of
times a poster will copy the formula from a reply and change
this
expression
incorrectly and that ends up causing the formula to not work
properly
leading to a bunch of follow-up questions.

This should be no problem.

Biff

"Sunny" wrote in message
...
I have a list 4 x 316:
county code sales tax
smith 36-02 25.00 5.00
allen 14-17 14.00 1.40
caddo 72-36 .00 .00
park 55 -92 97.00 9.70

In a different part of the spread sheet I would like:

code sales tax county
36-02 25.00 5.00 smith
14-17 14.00 1.40 allen
55-92 97.00 9.70 park

I would like this list updated 'automatically' when
something
is
changed
in
the top part.
The numbers in the top are looked up on another spreadsheet
and
populated
upon opening of the workbook.

Do you need anything else?

Thanks for your help

"Biff" wrote:

Hi!

How about explaining exactly what you want to do (include
the
DETAILS!)
and
if I can figure it out I'll post a fresh link with a
sample.

Biff

"Sunny" wrote in message
...
This is exactly what I would liek to do! However, the
link
no
longer
works.
Would you please send it again?

Thanks-
sunny

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be
used.
This
demonstrates the method I prefer. Just one formula
copied
to
the
cells.
It's
an array formula, though, and array formulas take
longer
to
calculate.
Depending on the size of your file and other factors,
you
might
not
notice
any difference.

Try deleting some data in column F then look at sheet 2
to
see
how
it
updates.

If in your situation you expect that there may be 20-40
rows
that
meet
the
criteria, then you'd need to copy the formula to AT
LEAST
40
rows.
In
the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of
data
total
and
needed to pull in 10 columns for only about 20-40
rows?

That should be Ok but you'll have to see how it
affects
performance
and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a
sample
file
that
demonstrates this (unless Max beats me to it!<g) and
post
a
link
to
the
file.

Biff

"Rikki-Handgards"

wrote
in
message
...
Thanks Biff. Would it still be practical if I had
250
rows
of
data
total
and
needed to pull in 10 columns for only about 20-40
rows?

"Biff" wrote:

Is there a way to do this with a formula and not a
pivot
table?

Yes, but whether or not it's practical depends on
how
many
cells
from
the
row you need returned and in general, how big is
the
table
of
data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150
columns
and
you
need
all
150 columns of data extracted for each instance of
"value",
then
a
formula
approach is not practical.

Biff

"Rikki-Handgards"

wrote
in
message
...
Hi. Is there a way to list, on a separate sheet,
only
rows
of
data
with a



  #22   Report Post  
Posted to microsoft.public.excel.misc
UT UT is offline
external usenet poster
 
Posts: 39
Default Getting all rows of data that have a value in a particular col

Hi,

I am trying to do something similar. I want to call values from another
worksheet but ignore the values that are "n/a". The link you posted does not
have the formula anymore.

Thanks for your time, UT

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used. This
demonstrates the method I prefer. Just one formula copied to the cells. It's
an array formula, though, and array formulas take longer to calculate.
Depending on the size of your file and other factors, you might not notice
any difference.

Try deleting some data in column F then look at sheet 2 to see how it
updates.

If in your situation you expect that there may be 20-40 rows that meet the
criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?


That should be Ok but you'll have to see how it affects performance and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file that
demonstrates this (unless Max beats me to it!<g) and post a link to the
file.

Biff

"Rikki-Handgards" wrote in
message ...
Thanks Biff. Would it still be practical if I had 250 rows of data total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?

Yes, but whether or not it's practical depends on how many cells from
the
row you need returned and in general, how big is the table of data this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you need
all
150 columns of data extracted for each instance of "value", then a
formula
approach is not practical.

Biff

"Rikki-Handgards" wrote in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of data
with a
value in a specific column? I want to list inventory items (and all
data
in
that row) as long as there is a value in column F. If there is no
value
in
column F for that inventory item, I don't want it to be the separate
summary
sheet. Is there a way to do this with a formula and not a pivot
table?
Thanks!








  #23   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Getting all rows of data that have a value in a particular col

Can you provide some more specific details?

Is "n/a" a TEXT entry or is it the Excel "error" value #N/A ?

Biff

"UT" wrote in message
...
Hi,

I am trying to do something similar. I want to call values from another
worksheet but ignore the values that are "n/a". The link you posted does
not
have the formula anymore.

Thanks for your time, UT

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used. This
demonstrates the method I prefer. Just one formula copied to the cells.
It's
an array formula, though, and array formulas take longer to calculate.
Depending on the size of your file and other factors, you might not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to see how it
updates.

If in your situation you expect that there may be 20-40 rows that meet
the
criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects performance and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file that
demonstrates this (unless Max beats me to it!<g) and post a link to
the
file.

Biff

"Rikki-Handgards" wrote in
message ...
Thanks Biff. Would it still be practical if I had 250 rows of data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?

Yes, but whether or not it's practical depends on how many cells from
the
row you need returned and in general, how big is the table of data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you need
all
150 columns of data extracted for each instance of "value", then a
formula
approach is not practical.

Biff

"Rikki-Handgards" wrote
in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of data
with a
value in a specific column? I want to list inventory items (and
all
data
in
that row) as long as there is a value in column F. If there is no
value
in
column F for that inventory item, I don't want it to be the
separate
summary
sheet. Is there a way to do this with a formula and not a pivot
table?
Thanks!










  #24   Report Post  
Posted to microsoft.public.excel.misc
UT UT is offline
external usenet poster
 
Posts: 39
Default Getting all rows of data that have a value in a particular col

Hi,

The "n/a" values are valid text entry. However in another worksheet I want
to ignore the "n/a" text and only link to other cell values in that column. I
have read about the formatting the not required values in white font in the
conditional formatting. But that does not serve my purpose. I only want to
link to the text values other than "n/a". Is there a way to do this? Thanks.

"T. Valko" wrote:

Can you provide some more specific details?

Is "n/a" a TEXT entry or is it the Excel "error" value #N/A ?

Biff

"UT" wrote in message
...
Hi,

I am trying to do something similar. I want to call values from another
worksheet but ignore the values that are "n/a". The link you posted does
not
have the formula anymore.

Thanks for your time, UT

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used. This
demonstrates the method I prefer. Just one formula copied to the cells.
It's
an array formula, though, and array formulas take longer to calculate.
Depending on the size of your file and other factors, you might not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to see how it
updates.

If in your situation you expect that there may be 20-40 rows that meet
the
criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects performance and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file that
demonstrates this (unless Max beats me to it!<g) and post a link to
the
file.

Biff

"Rikki-Handgards" wrote in
message ...
Thanks Biff. Would it still be practical if I had 250 rows of data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?

Yes, but whether or not it's practical depends on how many cells from
the
row you need returned and in general, how big is the table of data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you need
all
150 columns of data extracted for each instance of "value", then a
formula
approach is not practical.

Biff

"Rikki-Handgards" wrote
in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of data
with a
value in a specific column? I want to list inventory items (and
all
data
in
that row) as long as there is a value in column F. If there is no
value
in
column F for that inventory item, I don't want it to be the
separate
summary
sheet. Is there a way to do this with a formula and not a pivot
table?
Thanks!











  #25   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Getting all rows of data that have a value in a particular col

Let's assume you have this data in A1:A5 -

n/a
blue
n/a
n/a
red

You want to extract all entries that are not n/a to another location.

If there are no empty/blank cells as above try this array** formula:

=IF(ROWS($1:1)<=COUNTIF(rng,"<n/a"),INDEX(rng,SMALL(IF(rng<"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"")

Copy down until you get blanks.

If there might be empty/blank cells try this array** formula:

=IF(ROWS($1:1)<=SUMPRODUCT(--(rng<""),--(rng<"n/a")),INDEX(rng,SMALL(IF((rng<"n/a")*(rng<""),ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"")

Copy down until you get blanks.

** array formulas *MUST* be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"UT" wrote in message
...
Hi,

The "n/a" values are valid text entry. However in another worksheet I want
to ignore the "n/a" text and only link to other cell values in that
column. I
have read about the formatting the not required values in white font in
the
conditional formatting. But that does not serve my purpose. I only want to
link to the text values other than "n/a". Is there a way to do this?
Thanks.

"T. Valko" wrote:

Can you provide some more specific details?

Is "n/a" a TEXT entry or is it the Excel "error" value #N/A ?

Biff

"UT" wrote in message
...
Hi,

I am trying to do something similar. I want to call values from another
worksheet but ignore the values that are "n/a". The link you posted
does
not
have the formula anymore.

Thanks for your time, UT

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used. This
demonstrates the method I prefer. Just one formula copied to the
cells.
It's
an array formula, though, and array formulas take longer to calculate.
Depending on the size of your file and other factors, you might not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to see how it
updates.

If in your situation you expect that there may be 20-40 rows that meet
the
criteria, then you'd need to copy the formula to AT LEAST 40 rows. In
the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects performance
and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file
that
demonstrates this (unless Max beats me to it!<g) and post a link to
the
file.

Biff

"Rikki-Handgards" wrote
in
message ...
Thanks Biff. Would it still be practical if I had 250 rows of data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?

Yes, but whether or not it's practical depends on how many cells
from
the
row you need returned and in general, how big is the table of data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you
need
all
150 columns of data extracted for each instance of "value", then
a
formula
approach is not practical.

Biff

"Rikki-Handgards"
wrote
in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of
data
with a
value in a specific column? I want to list inventory items (and
all
data
in
that row) as long as there is a value in column F. If there is
no
value
in
column F for that inventory item, I don't want it to be the
separate
summary
sheet. Is there a way to do this with a formula and not a pivot
table?
Thanks!















  #26   Report Post  
Posted to microsoft.public.excel.misc
UT UT is offline
external usenet poster
 
Posts: 39
Default Getting all rows of data that have a value in a particular col

Hi Biff:

I tried the following formula but it didn't work. It give "name" error. What
am I doing wrong? Thanks

=IF(ROWS(GISData!H$8:H$500)<=COUNTIF(rng,"<n/a"),INDEX(rng,SMALL(IF(rng<"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS(GISData!H$8:H$500))),"")

"T. Valko" wrote:

Let's assume you have this data in A1:A5 -

n/a
blue
n/a
n/a
red

You want to extract all entries that are not n/a to another location.

If there are no empty/blank cells as above try this array** formula:

=IF(ROWS($1:1)<=COUNTIF(rng,"<n/a"),INDEX(rng,SMALL(IF(rng<"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"")

Copy down until you get blanks.

If there might be empty/blank cells try this array** formula:

=IF(ROWS($1:1)<=SUMPRODUCT(--(rng<""),--(rng<"n/a")),INDEX(rng,SMALL(IF((rng<"n/a")*(rng<""),ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"")

Copy down until you get blanks.

** array formulas *MUST* be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"UT" wrote in message
...
Hi,

The "n/a" values are valid text entry. However in another worksheet I want
to ignore the "n/a" text and only link to other cell values in that
column. I
have read about the formatting the not required values in white font in
the
conditional formatting. But that does not serve my purpose. I only want to
link to the text values other than "n/a". Is there a way to do this?
Thanks.

"T. Valko" wrote:

Can you provide some more specific details?

Is "n/a" a TEXT entry or is it the Excel "error" value #N/A ?

Biff

"UT" wrote in message
...
Hi,

I am trying to do something similar. I want to call values from another
worksheet but ignore the values that are "n/a". The link you posted
does
not
have the formula anymore.

Thanks for your time, UT

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used. This
demonstrates the method I prefer. Just one formula copied to the
cells.
It's
an array formula, though, and array formulas take longer to calculate.
Depending on the size of your file and other factors, you might not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to see how it
updates.

If in your situation you expect that there may be 20-40 rows that meet
the
criteria, then you'd need to copy the formula to AT LEAST 40 rows. In
the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects performance
and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file
that
demonstrates this (unless Max beats me to it!<g) and post a link to
the
file.

Biff

"Rikki-Handgards" wrote
in
message ...
Thanks Biff. Would it still be practical if I had 250 rows of data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?

Yes, but whether or not it's practical depends on how many cells
from
the
row you need returned and in general, how big is the table of data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you
need
all
150 columns of data extracted for each instance of "value", then
a
formula
approach is not practical.

Biff

"Rikki-Handgards"
wrote
in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of
data
with a
value in a specific column? I want to list inventory items (and
all
data
in
that row) as long as there is a value in column F. If there is
no
value
in
column F for that inventory item, I don't want it to be the
separate
summary
sheet. Is there a way to do this with a formula and not a pivot
table?
Thanks!














  #27   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 422
Default Getting all rows of data that have a value in a particular col

In the formula where you see rng << This is referring to a named
Range, which in your case is Range(A1:A5). Select the Range A1:A5 (and
while selected (In the NamedRange Box type in "rng" (without the
quotes),
thereby creating a named range equal to Range A1:A5) then the formulas
will work,,,

HTH




"UT" wrote in message
:

Hi Biff:

I tried the following formula but it didn't work. It give "name" error. What
am I doing wrong? Thanks

=IF(ROWS(GISData!H$8:H$500)<=COUNTIF(rng,"<n/a"),INDEX(rng,SMALL(IF(rng<"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS(GISData!H$8:H$500))),"")

"T. Valko" wrote:

Let's assume you have this data in A1:A5 -

n/a
blue
n/a
n/a
red

You want to extract all entries that are not n/a to another location.

If there are no empty/blank cells as above try this array** formula:

=IF(ROWS($1:1)<=COUNTIF(rng,"<n/a"),INDEX(rng,SMALL(IF(rng<"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"")

Copy down until you get blanks.

If there might be empty/blank cells try this array** formula:

=IF(ROWS($1:1)<=SUMPRODUCT(--(rng<""),--(rng<"n/a")),INDEX(rng,SMALL(IF((rng<"n/a")*(rng<""),ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"")

Copy down until you get blanks.

** array formulas *MUST* be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"UT" wrote in message
...
Hi,

The "n/a" values are valid text entry. However in another worksheet I want
to ignore the "n/a" text and only link to other cell values in that
column. I
have read about the formatting the not required values in white font in
the
conditional formatting. But that does not serve my purpose. I only want to
link to the text values other than "n/a". Is there a way to do this?
Thanks.

"T. Valko" wrote:

Can you provide some more specific details?

Is "n/a" a TEXT entry or is it the Excel "error" value #N/A ?

Biff

"UT" wrote in message
...
Hi,

I am trying to do something similar. I want to call values from another
worksheet but ignore the values that are "n/a". The link you posted
does
not
have the formula anymore.

Thanks for your time, UT

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used. This
demonstrates the method I prefer. Just one formula copied to the
cells.
It's
an array formula, though, and array formulas take longer to calculate.
Depending on the size of your file and other factors, you might not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to see how it
updates.

If in your situation you expect that there may be 20-40 rows that meet
the
criteria, then you'd need to copy the formula to AT LEAST 40 rows. In
the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects performance
and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file
that
demonstrates this (unless Max beats me to it!<g) and post a link to
the
file.

Biff

"Rikki-Handgards" wrote
in
message ...
Thanks Biff. Would it still be practical if I had 250 rows of data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?

Yes, but whether or not it's practical depends on how many cells
from
the
row you need returned and in general, how big is the table of data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you
need
all
150 columns of data extracted for each instance of "value", then
a
formula
approach is not practical.

Biff

"Rikki-Handgards"
wrote
in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of
data
with a
value in a specific column? I want to list inventory items (and
all
data
in
that row) as long as there is a value in column F. If there is
no
value
in
column F for that inventory item, I don't want it to be the
separate
summary
sheet. Is there a way to do this with a formula and not a pivot
table?
Thanks!















  #28   Report Post  
Posted to microsoft.public.excel.misc
UT UT is offline
external usenet poster
 
Posts: 39
Default Getting all rows of data that have a value in a particular col

Thanks for the tip. I had never used range before.

I named my column range - "rng1" (without quotes), in the other worksheet.
In this worksheet I entered the range name in the following formula. There
are blank cells and cells with "n/a" value that I want to ignore.

The formula still doesn't work though. The cell remains blank after I
entered this formula.

=IF(ROWS(GISData!H$8:H$500)<=COUNTIF(rng1,"<n/a"),INDEX(rng1,SMALL(IF(rng1<"n/a",ROW(rng1)-MIN(ROW(rng1))+1),ROWS(GISData!H$8:H$500))),"")

Thanks for your help.
"JMay" wrote:

In the formula where you see rng << This is referring to a named
Range, which in your case is Range(A1:A5). Select the Range A1:A5 (and
while selected (In the NamedRange Box type in "rng" (without the
quotes),
thereby creating a named range equal to Range A1:A5) then the formulas
will work,,,

HTH




"UT" wrote in message
:

Hi Biff:

I tried the following formula but it didn't work. It give "name" error. What
am I doing wrong? Thanks

=IF(ROWS(GISData!H$8:H$500)<=COUNTIF(rng,"<n/a"),INDEX(rng,SMALL(IF(rng<"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS(GISData!H$8:H$500))),"")

"T. Valko" wrote:

Let's assume you have this data in A1:A5 -

n/a
blue
n/a
n/a
red

You want to extract all entries that are not n/a to another location.

If there are no empty/blank cells as above try this array** formula:

=IF(ROWS($1:1)<=COUNTIF(rng,"<n/a"),INDEX(rng,SMALL(IF(rng<"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"")

Copy down until you get blanks.

If there might be empty/blank cells try this array** formula:

=IF(ROWS($1:1)<=SUMPRODUCT(--(rng<""),--(rng<"n/a")),INDEX(rng,SMALL(IF((rng<"n/a")*(rng<""),ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"")

Copy down until you get blanks.

** array formulas *MUST* be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"UT" wrote in message
...
Hi,

The "n/a" values are valid text entry. However in another worksheet I want
to ignore the "n/a" text and only link to other cell values in that
column. I
have read about the formatting the not required values in white font in
the
conditional formatting. But that does not serve my purpose. I only want to
link to the text values other than "n/a". Is there a way to do this?
Thanks.

"T. Valko" wrote:

Can you provide some more specific details?

Is "n/a" a TEXT entry or is it the Excel "error" value #N/A ?

Biff

"UT" wrote in message
...
Hi,

I am trying to do something similar. I want to call values from another
worksheet but ignore the values that are "n/a". The link you posted
does
not
have the formula anymore.

Thanks for your time, UT

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used. This
demonstrates the method I prefer. Just one formula copied to the
cells.
It's
an array formula, though, and array formulas take longer to calculate.
Depending on the size of your file and other factors, you might not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to see how it
updates.

If in your situation you expect that there may be 20-40 rows that meet
the
criteria, then you'd need to copy the formula to AT LEAST 40 rows. In
the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects performance
and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample file
that
demonstrates this (unless Max beats me to it!<g) and post a link to
the
file.

Biff

"Rikki-Handgards" wrote
in
message ...
Thanks Biff. Would it still be practical if I had 250 rows of data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot table?

Yes, but whether or not it's practical depends on how many cells
from
the
row you need returned and in general, how big is the table of data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns and you
need
all
150 columns of data extracted for each instance of "value", then
a
formula
approach is not practical.

Biff

"Rikki-Handgards"
wrote
in
message ...
Hi. Is there a way to list, on a separate sheet, only rows of
data
with a
value in a specific column? I want to list inventory items (and
all
data
in
that row) as long as there is a value in column F. If there is
no
value
in
column F for that inventory item, I don't want it to be the
separate
summary
sheet. Is there a way to do this with a formula and not a pivot
table?
Thanks!
















  #29   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Getting all rows of data that have a value in a particular col

Change each instance of:

ROWS(GISData!H$8:H$500)

To:

ROWS($1:1)

ROWS($1:1) is just a "counter" and has nothing to do with the actual range
GISData!H$8:H$500

Biff

"UT" wrote in message
...
Thanks for the tip. I had never used range before.

I named my column range - "rng1" (without quotes), in the other worksheet.
In this worksheet I entered the range name in the following formula. There
are blank cells and cells with "n/a" value that I want to ignore.

The formula still doesn't work though. The cell remains blank after I
entered this formula.

=IF(ROWS(GISData!H$8:H$500)<=COUNTIF(rng1,"<n/a"),INDEX(rng1,SMALL(IF(rng1<"n/a",ROW(rng1)-MIN(ROW(rng1))+1),ROWS(GISData!H$8:H$500))),"")

Thanks for your help.
"JMay" wrote:

In the formula where you see rng << This is referring to a named
Range, which in your case is Range(A1:A5). Select the Range A1:A5 (and
while selected (In the NamedRange Box type in "rng" (without the
quotes),
thereby creating a named range equal to Range A1:A5) then the formulas
will work,,,

HTH




"UT" wrote in message
:

Hi Biff:

I tried the following formula but it didn't work. It give "name" error.
What
am I doing wrong? Thanks

=IF(ROWS(GISData!H$8:H$500)<=COUNTIF(rng,"<n/a"),INDEX(rng,SMALL(IF(rng<"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS(GISData!H$8:H$500))),"")

"T. Valko" wrote:

Let's assume you have this data in A1:A5 -

n/a
blue
n/a
n/a
red

You want to extract all entries that are not n/a to another location.

If there are no empty/blank cells as above try this array** formula:

=IF(ROWS($1:1)<=COUNTIF(rng,"<n/a"),INDEX(rng,SMALL(IF(rng<"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"")

Copy down until you get blanks.

If there might be empty/blank cells try this array** formula:

=IF(ROWS($1:1)<=SUMPRODUCT(--(rng<""),--(rng<"n/a")),INDEX(rng,SMALL(IF((rng<"n/a")*(rng<""),ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"")

Copy down until you get blanks.

** array formulas *MUST* be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"UT" wrote in message
...
Hi,

The "n/a" values are valid text entry. However in another worksheet
I want
to ignore the "n/a" text and only link to other cell values in that
column. I
have read about the formatting the not required values in white
font in
the
conditional formatting. But that does not serve my purpose. I only
want to
link to the text values other than "n/a". Is there a way to do
this?
Thanks.

"T. Valko" wrote:

Can you provide some more specific details?

Is "n/a" a TEXT entry or is it the Excel "error" value #N/A ?

Biff

"UT" wrote in message
...
Hi,

I am trying to do something similar. I want to call values from
another
worksheet but ignore the values that are "n/a". The link you
posted
does
not
have the formula anymore.

Thanks for your time, UT

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used.
This
demonstrates the method I prefer. Just one formula copied to
the
cells.
It's
an array formula, though, and array formulas take longer to
calculate.
Depending on the size of your file and other factors, you might
not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to see
how it
updates.

If in your situation you expect that there may be 20-40 rows
that meet
the
criteria, then you'd need to copy the formula to AT LEAST 40
rows. In
the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total
and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects
performance
and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample
file
that
demonstrates this (unless Max beats me to it!<g) and post a
link to
the
file.

Biff

"Rikki-Handgards"
wrote
in
message
...
Thanks Biff. Would it still be practical if I had 250 rows
of data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot
table?

Yes, but whether or not it's practical depends on how many
cells
from
the
row you need returned and in general, how big is the table
of data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns
and you
need
all
150 columns of data extracted for each instance of
"value", then
a
formula
approach is not practical.

Biff

"Rikki-Handgards"

wrote
in
message
...
Hi. Is there a way to list, on a separate sheet, only
rows of
data
with a
value in a specific column? I want to list inventory
items (and
all
data
in
that row) as long as there is a value in column F. If
there is
no
value
in
column F for that inventory item, I don't want it to be
the
separate
summary
sheet. Is there a way to do this with a formula and not
a pivot
table?
Thanks!


















  #30   Report Post  
Posted to microsoft.public.excel.misc
UT UT is offline
external usenet poster
 
Posts: 39
Default Getting all rows of data that have a value in a particular col

The formula worked perfectly. Thanks a ton.

One more question, my worksheet is linked to sql server database. Everytime
I refresh data on the worksheet the column width is adjusted to the column
heading size. I have tried to specify the column width but it changes upon
refreshing the data. Is there a way to lock the column width or row height.

Thanks.

"T. Valko" wrote:

Change each instance of:

ROWS(GISData!H$8:H$500)

To:

ROWS($1:1)

ROWS($1:1) is just a "counter" and has nothing to do with the actual range
GISData!H$8:H$500

Biff

"UT" wrote in message
...
Thanks for the tip. I had never used range before.

I named my column range - "rng1" (without quotes), in the other worksheet.
In this worksheet I entered the range name in the following formula. There
are blank cells and cells with "n/a" value that I want to ignore.

The formula still doesn't work though. The cell remains blank after I
entered this formula.

=IF(ROWS(GISData!H$8:H$500)<=COUNTIF(rng1,"<n/a"),INDEX(rng1,SMALL(IF(rng1<"n/a",ROW(rng1)-MIN(ROW(rng1))+1),ROWS(GISData!H$8:H$500))),"")

Thanks for your help.
"JMay" wrote:

In the formula where you see rng << This is referring to a named
Range, which in your case is Range(A1:A5). Select the Range A1:A5 (and
while selected (In the NamedRange Box type in "rng" (without the
quotes),
thereby creating a named range equal to Range A1:A5) then the formulas
will work,,,

HTH




"UT" wrote in message
:

Hi Biff:

I tried the following formula but it didn't work. It give "name" error.
What
am I doing wrong? Thanks

=IF(ROWS(GISData!H$8:H$500)<=COUNTIF(rng,"<n/a"),INDEX(rng,SMALL(IF(rng<"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS(GISData!H$8:H$500))),"")

"T. Valko" wrote:

Let's assume you have this data in A1:A5 -

n/a
blue
n/a
n/a
red

You want to extract all entries that are not n/a to another location.

If there are no empty/blank cells as above try this array** formula:

=IF(ROWS($1:1)<=COUNTIF(rng,"<n/a"),INDEX(rng,SMALL(IF(rng<"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"")

Copy down until you get blanks.

If there might be empty/blank cells try this array** formula:

=IF(ROWS($1:1)<=SUMPRODUCT(--(rng<""),--(rng<"n/a")),INDEX(rng,SMALL(IF((rng<"n/a")*(rng<""),ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"")

Copy down until you get blanks.

** array formulas *MUST* be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"UT" wrote in message
...
Hi,

The "n/a" values are valid text entry. However in another worksheet
I want
to ignore the "n/a" text and only link to other cell values in that
column. I
have read about the formatting the not required values in white
font in
the
conditional formatting. But that does not serve my purpose. I only
want to
link to the text values other than "n/a". Is there a way to do
this?
Thanks.

"T. Valko" wrote:

Can you provide some more specific details?

Is "n/a" a TEXT entry or is it the Excel "error" value #N/A ?

Biff

"UT" wrote in message
...
Hi,

I am trying to do something similar. I want to call values from
another
worksheet but ignore the values that are "n/a". The link you
posted
does
not
have the formula anymore.

Thanks for your time, UT

"Biff" wrote:

Here's a link to a sample file:

http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU

There are a few different formula methods that could be used.
This
demonstrates the method I prefer. Just one formula copied to
the
cells.
It's
an array formula, though, and array formulas take longer to
calculate.
Depending on the size of your file and other factors, you might
not
notice
any difference.

Try deleting some data in column F then look at sheet 2 to see
how it
updates.

If in your situation you expect that there may be 20-40 rows
that meet
the
criteria, then you'd need to copy the formula to AT LEAST 40
rows. In
the
sample I copied the formula to 10 rows by 10 columns.

Biff

"Biff" wrote in message
...
Would it still be practical if I had 250 rows of data total
and
needed to pull in 10 columns for only about 20-40 rows?

That should be Ok but you'll have to see how it affects
performance
and
then make that determination for yourself.

I'm going to break for dinner but I'll put together a sample
file
that
demonstrates this (unless Max beats me to it!<g) and post a
link to
the
file.

Biff

"Rikki-Handgards"
wrote
in
message
...
Thanks Biff. Would it still be practical if I had 250 rows
of data
total
and
needed to pull in 10 columns for only about 20-40 rows?

"Biff" wrote:

Is there a way to do this with a formula and not a pivot
table?

Yes, but whether or not it's practical depends on how many
cells
from
the
row you need returned and in general, how big is the table
of data
this
data
is being extracted from.

For example, if your table is 50,000 rows by 150 columns
and you
need
all
150 columns of data extracted for each instance of
"value", then
a
formula
approach is not practical.

Biff

"Rikki-Handgards"

wrote
in
message
...
Hi. Is there a way to list, on a separate sheet, only
rows of
data
with a
value in a specific column? I want to list inventory
items (and
all
data
in
that row) as long as there is a value in column F. If
there is
no
value
in
column F for that inventory item, I don't want it to be
the
separate
summary
sheet. Is there a way to do this with a formula and not
a pivot
table?
Thanks!





















  #31   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 155
Default Getting all rows of data that have a value in a particular column

I have exactly the same question. I have the list for inventory and if the
item number is lower than reorder level, then there will be a "warning"
written at the beginning of the row automatically. Then I want to copy those
rows with "warning" to another sheet, then I can generate my reorder list. So
how can I do?? Thank you!
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
From several workbooks onto one excel worksheet steve Excel Discussion (Misc queries) 6 December 1st 05 09:03 AM
In Excel: select the last 20 rows of data in a column Tad Blanchard Excel Worksheet Functions 2 November 25th 05 10:35 AM
Help PLEASE! Not sure what answer is: Match? Index? Other? baz Excel Worksheet Functions 7 September 3rd 05 03:47 PM
How can I convert a data from multiple rows into 1 column? yudi_lks Excel Worksheet Functions 10 January 30th 05 04:47 AM
find rows for unique data in 1 column and different data in other. Dot Majewski Excel Discussion (Misc queries) 1 January 21st 05 01:23 AM


All times are GMT +1. The time now is 08:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"