Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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?? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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?? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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?? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup and count | Excel Discussion (Misc queries) | |||
lookup count | Excel Discussion (Misc queries) | |||
count and lookup | Excel Worksheet Functions | |||
count in a lookup ? | Excel Discussion (Misc queries) | |||
I'm using an if statemnt and the result if true points to one cell | Excel Discussion (Misc queries) |