Getting all rows of data that have a value in a particular column

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
news
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
news 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!

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
news
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
news 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!

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!