need help with a lookup and count and if statemnt
 

Hi,

I have 2 rows and 12 columns. The first rows is the headers second is the
values.

Ld1 Ld2 Ld3 Ld4 Ld5 Ld6 Ld7 Ld8 Ld9 Ld10 ....
2 10 0 0 0 98 0 0 10 15
......

it looks sumthing like this. I want a formula to go through the row and tell
me which colmns in row 2 have a number greater than 0 in this format :
Ld1 + Ld2 + Ld6 + Ld9 + Ld10

Any suggestions??

need help with a lookup and count and if statemnt
 

You need an UDF to do that:

Function specconc(header As Range, nums As Range)
Dim h As Range, n As Range
colcounter = 0
retvalue = ""
For Each n In nums
colcounter = colcounter + 1
If n 0 Then retvalue = retvalue & header.Item(1, colcounter) & " + "
Next n
If retvalue = "" Then
specconc = retvalue
Else
specconc = Left(retvalue, Len(retvalue) - 3)
End If
End Function

Usage:

Sub test()
x = specconc(Range("A1:J1"), Range("A2:J2"))
End Sub

Regards,
Stefi

€žDingerz€� ezt Ã*rta:

Hi,

I have 2 rows and 12 columns. The first rows is the headers second is the
values.

Ld1 Ld2 Ld3 Ld4 Ld5 Ld6 Ld7 Ld8 Ld9 Ld10 ....
2 10 0 0 0 98 0 0 10 15
.....

it looks sumthing like this. I want a formula to go through the row and tell
me which colmns in row 2 have a number greater than 0 in this format :
Ld1 + Ld2 + Ld6 + Ld9 + Ld10

Any suggestions??

need help with a lookup and count and if statemnt
 

Thanks Stefi,

I am unfamilar with UDF. Do i paste this into a new module? And type =test()
into the cell i want the answer?

It doesnt seem to be working.

Thanks

Dingerz

"Stefi" wrote:

You need an UDF to do that:

Function specconc(header As Range, nums As Range)
Dim h As Range, n As Range
colcounter = 0
retvalue = ""
For Each n In nums
colcounter = colcounter + 1
If n 0 Then retvalue = retvalue & header.Item(1, colcounter) & " + "
Next n
If retvalue = "" Then
specconc = retvalue
Else
specconc = Left(retvalue, Len(retvalue) - 3)
End If
End Function

Usage:

Sub test()
x = specconc(Range("A1:J1"), Range("A2:J2"))
End Sub

Regards,
Stefi

€žDingerz€� ezt Ã*rta:

Hi,

I have 2 rows and 12 columns. The first rows is the headers second is the
values.

Ld1 Ld2 Ld3 Ld4 Ld5 Ld6 Ld7 Ld8 Ld9 Ld10 ....
2 10 0 0 0 98 0 0 10 15
.....

it looks sumthing like this. I want a formula to go through the row and tell
me which colmns in row 2 have a number greater than 0 in this format :
Ld1 + Ld2 + Ld6 + Ld9 + Ld10

Any suggestions??

need help with a lookup and count and if statemnt
 

€žDingerz€� ezt Ã*rta:

Thanks Stefi,

I am unfamilar with UDF. Do i paste this into a new module?

YES


And type =test()
into the cell i want the answer?

NO

type in the result cell =specconc(A1:J1,A2:J2)

Watch list separator! Use comma (,) or semicolon (;) according to your Excel
version!

Stefi

need help with a lookup and count and if statemnt
 

Thanks was helpful, I have a follow on question about another UDF, can you
tell me how i would make a function lookup a list of values which are in a
column and make a new list with ONLY values greater than 0 in assending
order. For example:

Column A New List in Column B
2 0.134
2.5 0.22
5 2
0 2.5
0 3
0 5
3
0.22
0.134

please help

Thanks


"Stefi" wrote:



€žDingerz€� ezt Ã*rta:

Thanks Stefi,

I am unfamilar with UDF. Do i paste this into a new module?

YES


And type =test()
into the cell i want the answer?

NO

type in the result cell =specconc(A1:J1,A2:J2)

Watch list separator! Use comma (,) or semicolon (;) according to your Excel
version!

Stefi

need help with a lookup and count and if statemnt
 

You can't do that with a function, you need a Sub for it, something like this:

Sub filterandsort()
Range("A1:A9").Select
Selection.Cut Destination:=Range("A2:A10")
Range("A1").Select
ActiveCell.FormulaR1C1 = "old"
Columns("A:A").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="0", Operator:=xlAnd
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFilter
Range("B1").Select
ActiveCell.FormulaR1C1 = "new"
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Regards,
Stefi


€žDingerz€� ezt Ã*rta:

Thanks was helpful, I have a follow on question about another UDF, can you
tell me how i would make a function lookup a list of values which are in a
column and make a new list with ONLY values greater than 0 in assending
order. For example:

Column A New List in Column B
2 0.134
2.5 0.22
5 2
0 2.5
0 3
0 5
3
0.22
0.134

please help

Thanks


"Stefi" wrote:



€žDingerz€� ezt Ã*rta:

Thanks Stefi,

I am unfamilar with UDF. Do i paste this into a new module?

YES


And type =test()
into the cell i want the answer?

NO

type in the result cell =specconc(A1:J1,A2:J2)

Watch list separator! Use comma (,) or semicolon (;) according to your Excel
version!

Stefi