excel function that reads a value > than 0

  • Thread starter Thread starter jcheko
  • Start date Start date
J

jcheko

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?
 
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)
 
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(C1>0,1,"")

In A2 enter:

=IF(C2>0,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
 
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(values>0,ROW(codes)-MIN(ROW(codes))+1),ROWS(D$2:D2))),"")

Tryo
 
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!>
 
that was very helpful, thank you.

T. Valko said:
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)
Click to expand...
 
that was very helpful, thank you.

Gary''s Student said:
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(C1>0,1,"")

In A2 enter:

=IF(C2>0,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
Click to expand...
 
that was very helpful, thank you.

Tyro said:
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(values>0,ROW(codes)-MIN(ROW(codes))+1),ROWS(D$2:D2))),"")

Tryo
Click to expand...
 
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!
 
Yep

Know all about that.

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!
Click to expand...
 
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.
 
Hard to tell without seeing the *exact* formula you tried but this is
usually where the "trouble spots" are:

=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)......)
 
thanks that worked very well!!!

T. Valko said:
Hard to tell without seeing the *exact* formula you tried but this is
usually where the "trouble spots" are:

=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)......)
Click to expand...
 
Back
Top