display data for non zero

  • Thread starter Thread starter crapit
  • Start date Start date
C

crapit

How do I get the follow if the raw data is available from the same
spreadsheet but from a hidden column M, N if Hoursrepresent is not equal to
value '0.00' using formula and NO Filter is used.
A B
couponID Hoursrepresent
00312 4.00
00315 2.50
00319 1.75
00323 3.60


M N
couponID Hoursrepresent
00310 0.00
00311 0.00
00312 4.00
00313 0.00
00314 0.00
00315 2.50
00316 0.00
00317 0.00
00318 0.00
00319 1.75
00320 0.00
00321 0.00
00322 0.00
00323 3.60
00324 0.00
 
In A2, ctrl+shift+enter:
=INDEX(M:M,SMALL(IF($N$2:$N$16<>0,ROW($N$2:$N$16),""),ROW(A1)))

in B2, ctrl+shift+enter:
=INDEX(N:N,SMALL(IF($N$2:$N$16<>0,ROW($N$2:$N$16),""),ROW(A1)))

then grab A2:B2, fill down.
If you fill down too far, you'll see #NUM, but you can eliminate that with
conditional formatting, making the font white if the result is an error
value...


Bob Umlas
Excel MVP
 
if dont use array??

Bob Umlas said:
In A2, ctrl+shift+enter:
=INDEX(M:M,SMALL(IF($N$2:$N$16<>0,ROW($N$2:$N$16),""),ROW(A1)))

in B2, ctrl+shift+enter:
=INDEX(N:N,SMALL(IF($N$2:$N$16<>0,ROW($N$2:$N$16),""),ROW(A1)))

then grab A2:B2, fill down.
If you fill down too far, you'll see #NUM, but you can eliminate that with
conditional formatting, making the font white if the result is an error
value...


Bob Umlas
Excel MVP
Click to expand...
 
Not that I can come up with -- what's your hesitation about using CSE? There
shouldn't be any! (hesitation, that is!)
 
Hi,

First let me second Bob's comment - you should have no reason not to use
arrays, they're just part of the toolkit that is Excel. That said:

You can get rid of the array in the second column by using

=LOOKUP(A2,M$2:N$16)

and you can get rid of the array in the first column by using a custom VBA
function.

=OrderedItems(N$2:N$16,ROW(A1))

where the functin is

Function OrderedItems(Rng1 As Range, theRow As Long)
Dim I As Long
Dim cell As Range
Dim x
I = 1
For Each cell In Rng1
If cell <> 0 Then
x = cell.Offset(0, -1)
If I = theRow Then
OrderedItems = x
Exit Function
End If
I = I + 1
End If
Next cell
End Function
 
OK, I use the array, as I dont want to use VBA.
Shane Devenshire said:
Hi,

First let me second Bob's comment - you should have no reason not to use
arrays, they're just part of the toolkit that is Excel. That said:

You can get rid of the array in the second column by using

=LOOKUP(A2,M$2:N$16)

and you can get rid of the array in the first column by using a custom VBA
function.

=OrderedItems(N$2:N$16,ROW(A1))

where the functin is

Function OrderedItems(Rng1 As Range, theRow As Long)
Dim I As Long
Dim cell As Range
Dim x
I = 1
For Each cell In Rng1
If cell <> 0 Then
x = cell.Offset(0, -1)
If I = theRow Then
OrderedItems = x
Exit Function
End If
I = I + 1
End If
Next cell
End Function

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire
Click to expand...
 
Back
Top