display data for non zero

[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

 

[Bob Umlas]

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

 

[crapit]

if dont use array??

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

 

[Bob Umlas]

Then you get #VALUE! error!

if dont use array??

 

[crapit]

No other way without using array CTRL-SHIFT-ENTER??

 

[Bob Umlas]

Not that I can come up with -- what's your hesitation about using CSE? There
shouldn't be any! (hesitation, that is!)

 

[Shane Devenshire]

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

 

[crapit]

OK, I use the array, as I dont want to use VBA.

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