excel function that reads a value than 0
 

I have two columns, one with cost codes and another the values of those cost
codes....in another worksheet I need to bring in that information. first the
function has to read down the column with the values and if it identifies a
values greater than zero I need the function to bring in the cost code. can
anyone help with this?

excel function that reads a value than 0
 

One way:

A1:A100 = codes
B1:B100 = values

Enter this array formula** in D1:

=IF(ROWS(D$1:D1)<=COUNTIF(values,"<0"),INDEX(codes ,SMALL(IF(values<0,ROW(codes)-MIN(ROW(codes))+1),ROWS(D$1:D1))),"")

Copy down until you get blanks meaning the data has been exhausted.

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

--
Biff
Microsoft Excel MVP


"jcheko" wrote in message
...
I have two columns, one with cost codes and another the values of those
cost
codes....in another worksheet I need to bring in that information. first
the
function has to read down the column with the values and if it identifies
a
values greater than zero I need the function to bring in the cost code.
can
anyone help with this?

excel function that reads a value than 0
 

Lets put the codes in column B and the costs in column C of Sheet2. We are
going to column A as a helper column. In A1 enter:

=IF(C10,1,"")

In A2 enter:

=IF(C20,MAX($A$1:A1)+1,"") and copy down. Might look like:

abcdddd1 $0.00
1 abcdddd2 $2.00
2 abcdddd3 $3.00
3 abcdddd4 $4.00
4 abcdddd5 $5.00
abcdddd6 $0.00
5 abcdddd7 $7.00
6 abcdddd8 $8.00
7 abcdddd9 $9.00
abcdddd10 $0.00
8 abcdddd11 $11.00
9 abcdddd12 $12.00
10 abcdddd13 $13.00
11 abcdddd14 $14.00
12 abcdddd15 $15.00
abcdddd16 $0.00
13 abcdddd17 $17.00
14 abcdddd18 $18.00
abcdddd19
abcdddd20
abcdddd21
15 abcdddd22 $22.00
16 abcdddd23 $23.00
17 abcdddd24 $24.00
18 abcdddd25 $25.00

Now every non-zero value in column C has a unique ID in column A

Now VLOOKUP() can pick the correct rows:

In another sheet in B1 enter:

=VLOOKUP(ROW(),Sheet2!A$1:C$25,2,FALSE) and copy down

and in C1 enter:

=VLOOKUP(ROW(),Sheet2!A$1:C$25,3,FALSE) and copy down

What we see in the new sheet is:

abcdddd2 2
abcdddd3 3
abcdddd4 4
abcdddd5 5
abcdddd7 7
abcdddd8 8
abcdddd9 9
abcdddd11 11
abcdddd12 12
abcdddd13 13
abcdddd14 14
abcdddd15 15
abcdddd17 17
abcdddd18 18
abcdddd22 22
abcdddd23 23
abcdddd24 24
abcdddd25 25


--
Gary''s Student - gsnu200769


"jcheko" wrote:

I have two columns, one with cost codes and another the values of those cost
codes....in another worksheet I need to bring in that information. first the
function has to read down the column with the values and if it identifies a
values greater than zero I need the function to bring in the cost code. can
anyone help with this?

excel function that reads a value than 0
 

Correction. The OP wanted greater than 0, not less than. The formula works
great.

=IF(ROWS(D$2:D2)<=COUNTIF(values,"0"),INDEX(codes ,SMALL(IF(values0,ROW(codes)-MIN(ROW(codes))+1),ROWS(D$2:D2))),"")

Tryo


"T. Valko" wrote in message
...
One way:

A1:A100 = codes
B1:B100 = values

Enter this array formula** in D1:

=IF(ROWS(D$1:D1)<=COUNTIF(values,"<0"),INDEX(codes ,SMALL(IF(values<0,ROW(codes)-MIN(ROW(codes))+1),ROWS(D$1:D1))),"")

Copy down until you get blanks meaning the data has been exhausted.

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

--
Biff
Microsoft Excel MVP


"jcheko" wrote in message
...
I have two columns, one with cost codes and another the values of those
cost
codes....in another worksheet I need to bring in that information. first
the
function has to read down the column with the values and if it identifies
a
values greater than zero I need the function to bring in the cost code.
can
anyone help with this?

excel function that reads a value than 0
 

The OP wanted greater than 0, not less than.

So, I'm dyslexic! <not really but if it gets me off the hook then I'm goin
with it!

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
...
Correction. The OP wanted greater than 0, not less than. The formula works
great.

=IF(ROWS(D$2:D2)<=COUNTIF(values,"0"),INDEX(codes ,SMALL(IF(values0,ROW(codes)-MIN(ROW(codes))+1),ROWS(D$2:D2))),"")

Tryo


"T. Valko" wrote in message
...
One way:

A1:A100 = codes
B1:B100 = values

Enter this array formula** in D1:

=IF(ROWS(D$1:D1)<=COUNTIF(values,"<0"),INDEX(codes ,SMALL(IF(values<0,ROW(codes)-MIN(ROW(codes))+1),ROWS(D$1:D1))),"")

Copy down until you get blanks meaning the data has been exhausted.

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

--
Biff
Microsoft Excel MVP


"jcheko" wrote in message
...
I have two columns, one with cost codes and another the values of those
cost
codes....in another worksheet I need to bring in that information. first
the
function has to read down the column with the values and if it
identifies a
values greater than zero I need the function to bring in the cost code.
can
anyone help with this?

excel function that reads a value than 0
 

that was very helpful, thank you.

"T. Valko" wrote:

One way:

A1:A100 = codes
B1:B100 = values

Enter this array formula** in D1:

=IF(ROWS(D$1:D1)<=COUNTIF(values,"<0"),INDEX(codes ,SMALL(IF(values<0,ROW(codes)-MIN(ROW(codes))+1),ROWS(D$1:D1))),"")

Copy down until you get blanks meaning the data has been exhausted.

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

--
Biff
Microsoft Excel MVP


"jcheko" wrote in message
...
I have two columns, one with cost codes and another the values of those
cost
codes....in another worksheet I need to bring in that information. first
the
function has to read down the column with the values and if it identifies
a
values greater than zero I need the function to bring in the cost code.
can
anyone help with this?

excel function that reads a value than 0
 

that was very helpful, thank you.

"Gary''s Student" wrote:

Lets put the codes in column B and the costs in column C of Sheet2. We are
going to column A as a helper column. In A1 enter:

=IF(C10,1,"")

In A2 enter:

=IF(C20,MAX($A$1:A1)+1,"") and copy down. Might look like:

abcdddd1 $0.00
1 abcdddd2 $2.00
2 abcdddd3 $3.00
3 abcdddd4 $4.00
4 abcdddd5 $5.00
abcdddd6 $0.00
5 abcdddd7 $7.00
6 abcdddd8 $8.00
7 abcdddd9 $9.00
abcdddd10 $0.00
8 abcdddd11 $11.00
9 abcdddd12 $12.00
10 abcdddd13 $13.00
11 abcdddd14 $14.00
12 abcdddd15 $15.00
abcdddd16 $0.00
13 abcdddd17 $17.00
14 abcdddd18 $18.00
abcdddd19
abcdddd20
abcdddd21
15 abcdddd22 $22.00
16 abcdddd23 $23.00
17 abcdddd24 $24.00
18 abcdddd25 $25.00

Now every non-zero value in column C has a unique ID in column A

Now VLOOKUP() can pick the correct rows:

In another sheet in B1 enter:

=VLOOKUP(ROW(),Sheet2!A$1:C$25,2,FALSE) and copy down

and in C1 enter:

=VLOOKUP(ROW(),Sheet2!A$1:C$25,3,FALSE) and copy down

What we see in the new sheet is:

abcdddd2 2
abcdddd3 3
abcdddd4 4
abcdddd5 5
abcdddd7 7
abcdddd8 8
abcdddd9 9
abcdddd11 11
abcdddd12 12
abcdddd13 13
abcdddd14 14
abcdddd15 15
abcdddd17 17
abcdddd18 18
abcdddd22 22
abcdddd23 23
abcdddd24 24
abcdddd25 25


--
Gary''s Student - gsnu200769


"jcheko" wrote:

I have two columns, one with cost codes and another the values of those cost
codes....in another worksheet I need to bring in that information. first the
function has to read down the column with the values and if it identifies a
values greater than zero I need the function to bring in the cost code. can
anyone help with this?

excel function that reads a value than 0
 

that was very helpful, thank you.

"Tyro" wrote:

Correction. The OP wanted greater than 0, not less than. The formula works
great.

=IF(ROWS(D$2:D2)<=COUNTIF(values,"0"),INDEX(codes ,SMALL(IF(values0,ROW(codes)-MIN(ROW(codes))+1),ROWS(D$2:D2))),"")

Tryo


"T. Valko" wrote in message
...
One way:

A1:A100 = codes
B1:B100 = values

Enter this array formula** in D1:

=IF(ROWS(D$1:D1)<=COUNTIF(values,"<0"),INDEX(codes ,SMALL(IF(values<0,ROW(codes)-MIN(ROW(codes))+1),ROWS(D$1:D1))),"")

Copy down until you get blanks meaning the data has been exhausted.

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

--
Biff
Microsoft Excel MVP


"jcheko" wrote in message
...
I have two columns, one with cost codes and another the values of those
cost
codes....in another worksheet I need to bring in that information. first
the
function has to read down the column with the values and if it identifies
a
values greater than zero I need the function to bring in the cost code.
can
anyone help with this?

excel function that reads a value than 0
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"jcheko" wrote in message
...
that was very helpful, thank you.

"T. Valko" wrote:

One way:

A1:A100 = codes
B1:B100 = values

Enter this array formula** in D1:

=IF(ROWS(D$1:D1)<=COUNTIF(values,"<0"),INDEX(codes ,SMALL(IF(values<0,ROW(codes)-MIN(ROW(codes))+1),ROWS(D$1:D1))),"")

Copy down until you get blanks meaning the data has been exhausted.

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

--
Biff
Microsoft Excel MVP


"jcheko" wrote in message
...
I have two columns, one with cost codes and another the values of those
cost
codes....in another worksheet I need to bring in that information.
first
the
function has to read down the column with the values and if it
identifies
a
values greater than zero I need the function to bring in the cost code.
can
anyone help with this?

excel function that reads a value than 0
 

You and your big words.

What the does hell lysdexic mean?


Gord

On Thu, 14 Feb 2008 22:07:28 -0500, "T. Valko" wrote:

So, I'm dyslexic! <not really but if it gets me off the hook then I'm goin
with it!

excel function that reads a value than 0
 

What the does hell lysdexic mean?

Like something that.

<g

On a serious note, sometimes I "forget" to type entire words!

I'll want to type:

On a serious note, sometimes I "forget" to type entire words!

And I'll end up with:

On a serious note, sometimes I "forget" to entire words!


--
Biff
Microsoft Excel MVP


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
You and your big words.

What the does hell lysdexic mean?


Gord

On Thu, 14 Feb 2008 22:07:28 -0500, "T. Valko"
wrote:

So, I'm dyslexic! <not really but if it gets me off the hook then I'm goin
with it!

excel function that reads a value than 0
 

Yep

Know all about that.

On Fri, 15 Feb 2008 14:14:04 -0500, "T. Valko" wrote:

What the does hell lysdexic mean?

Like something that.

<g

On a serious note, sometimes I "forget" to type entire words!

I'll want to type:

On a serious note, sometimes I "forget" to type entire words!

And I'll end up with:

On a serious note, sometimes I "forget" to entire words!

excel function that reads a value than 0
 

quick question for you...I am trying to work the formula starting in cell 14
on another worksheet...and it brings it in blank, probably because it is
reading the first 13 cells before that one. and all of them are blank....in
this worksheet is were I have set up the report I print but I can't get it to
work....can you help with this.

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"jcheko" wrote in message
...
that was very helpful, thank you.

"T. Valko" wrote:

One way:

A1:A100 = codes
B1:B100 = values

Enter this array formula** in D1:

=IF(ROWS(D$1:D1)<=COUNTIF(values,"<0"),INDEX(codes ,SMALL(IF(values<0,ROW(codes)-MIN(ROW(codes))+1),ROWS(D$1:D1))),"")

Copy down until you get blanks meaning the data has been exhausted.

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

--
Biff
Microsoft Excel MVP


"jcheko" wrote in message
...
I have two columns, one with cost codes and another the values of those
cost
codes....in another worksheet I need to bring in that information.
first
the
function has to read down the column with the values and if it
identifies
a
values greater than zero I need the function to bring in the cost code.
can
anyone help with this?

excel function that reads a value than 0
 

Hard to tell without seeing the *exact* formula you tried but this is
usually where the "trouble spots" a

=IF(ROWS(D$1:D1)...............ROWS(D$1:D1)......)

Whatever the *first* cell address is where you're entering the formula, use
that address in the ROWS function. If the *first* cell to hold the formula
is H14, then:

=IF(ROWS(H$14:H14)...............ROWS(H$14:H14)... ...)


--
Biff
Microsoft Excel MVP


"jcheko" wrote in message
...
quick question for you...I am trying to work the formula starting in cell
14
on another worksheet...and it brings it in blank, probably because it is
reading the first 13 cells before that one. and all of them are
blank....in
this worksheet is were I have set up the report I print but I can't get it
to
work....can you help with this.

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"jcheko" wrote in message
...
that was very helpful, thank you.

"T. Valko" wrote:

One way:

A1:A100 = codes
B1:B100 = values

Enter this array formula** in D1:

=IF(ROWS(D$1:D1)<=COUNTIF(values,"<0"),INDEX(codes ,SMALL(IF(values<0,ROW(codes)-MIN(ROW(codes))+1),ROWS(D$1:D1))),"")

Copy down until you get blanks meaning the data has been exhausted.

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

--
Biff
Microsoft Excel MVP


"jcheko" wrote in message
...
I have two columns, one with cost codes and another the values of
those
cost
codes....in another worksheet I need to bring in that information.
first
the
function has to read down the column with the values and if it
identifies
a
values greater than zero I need the function to bring in the cost
code.
can
anyone help with this?

excel function that reads a value than 0
 

thanks that worked very well!!!

"T. Valko" wrote:

Hard to tell without seeing the *exact* formula you tried but this is
usually where the "trouble spots" a

=IF(ROWS(D$1:D1)...............ROWS(D$1:D1)......)

Whatever the *first* cell address is where you're entering the formula, use
that address in the ROWS function. If the *first* cell to hold the formula
is H14, then:

=IF(ROWS(H$14:H14)...............ROWS(H$14:H14)... ...)


--
Biff
Microsoft Excel MVP


"jcheko" wrote in message
...
quick question for you...I am trying to work the formula starting in cell
14
on another worksheet...and it brings it in blank, probably because it is
reading the first 13 cells before that one. and all of them are
blank....in
this worksheet is were I have set up the report I print but I can't get it
to
work....can you help with this.

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"jcheko" wrote in message
...
that was very helpful, thank you.

"T. Valko" wrote:

One way:

A1:A100 = codes
B1:B100 = values

Enter this array formula** in D1:

=IF(ROWS(D$1:D1)<=COUNTIF(values,"<0"),INDEX(codes ,SMALL(IF(values<0,ROW(codes)-MIN(ROW(codes))+1),ROWS(D$1:D1))),"")

Copy down until you get blanks meaning the data has been exhausted.

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

--
Biff
Microsoft Excel MVP


"jcheko" wrote in message
...
I have two columns, one with cost codes and another the values of
those
cost
codes....in another worksheet I need to bring in that information.
first
the
function has to read down the column with the values and if it
identifies
a
values greater than zero I need the function to bring in the cost
code.
can
anyone help with this?

excel function that reads a value than 0
 

You're welcome!

--
Biff
Microsoft Excel MVP


"jcheko" wrote in message
...
thanks that worked very well!!!

"T. Valko" wrote:

Hard to tell without seeing the *exact* formula you tried but this is
usually where the "trouble spots" a

=IF(ROWS(D$1:D1)...............ROWS(D$1:D1)......)

Whatever the *first* cell address is where you're entering the formula,
use
that address in the ROWS function. If the *first* cell to hold the
formula
is H14, then:

=IF(ROWS(H$14:H14)...............ROWS(H$14:H14)... ...)


--
Biff
Microsoft Excel MVP


"jcheko" wrote in message
...
quick question for you...I am trying to work the formula starting in
cell
14
on another worksheet...and it brings it in blank, probably because it
is
reading the first 13 cells before that one. and all of them are
blank....in
this worksheet is were I have set up the report I print but I can't get
it
to
work....can you help with this.

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"jcheko" wrote in message
...
that was very helpful, thank you.

"T. Valko" wrote:

One way:

A1:A100 = codes
B1:B100 = values

Enter this array formula** in D1:

=IF(ROWS(D$1:D1)<=COUNTIF(values,"<0"),INDEX(codes ,SMALL(IF(values<0,ROW(codes)-MIN(ROW(codes))+1),ROWS(D$1:D1))),"")

Copy down until you get blanks meaning the data has been exhausted.

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

--
Biff
Microsoft Excel MVP


"jcheko" wrote in message
...
I have two columns, one with cost codes and another the values of
those
cost
codes....in another worksheet I need to bring in that
information.
first
the
function has to read down the column with the values and if it
identifies
a
values greater than zero I need the function to bring in the cost
code.
can
anyone help with this?