Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
concatenate if
Hi,
I want to lookup a value, and concatenate the results. Example: Col A; Col B apple; 1 pear; 1 naartjie; 2 I want to lookup values with the value of "1" Result apple, pear(in one cell) I've seen the same query on other sites, with the answer to use concatif(A:A,B:B,",), but I can't find the function on excel, or the function doesn't work when I use it. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
concatenate if
Hi,
Try this UDF. Alt +F11 to open VB editor. Right click 'ThisWorkbook' and insert module and paste the code below in call with =concat(a1:a10,1) Adjust the range to suit and 1 is the lookup value Function concat(rng As Range, condition As Long) As String For Each r In rng If r.Offset(, 1) = condition Then concat = concat + r & " ," End If Next r concat = Left(concat, Len(concat) - 1) End Function Mike "Betty" wrote: Hi, I want to lookup a value, and concatenate the results. Example: Col A; Col B apple; 1 pear; 1 naartjie; 2 I want to lookup values with the value of "1" Result apple, pear(in one cell) I've seen the same query on other sites, with the answer to use concatif(A:A,B:B,",), but I can't find the function on excel, or the function doesn't work when I use it. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
concatenate if
Hi Mike,
Thanks for the prompt response. This is my actual example, the value to lookup won't always be 1 as in my previous example. My vallue to lookup is abcc61 in parent_code column, and if true return all matching sku's concatenated in children column. sku parent_code children abcc61 abcc21, abcc25, abcc101, abcc1 abcc21 abcC61 abcc25 abcC61 abcc101 abcC61 abcc1 abcC61 bcdc61 bcdc21, bcdc25, bcdc101 bcdc21 bcdc61 bcdc25 bcdc61 bcdc101 bcdc61 I've used the concatif function, but I'm not clued up with VB editor The function works for me, but I don't really know how to implement it. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate help | Excel Discussion (Misc queries) | |||
Concatenate | Excel Discussion (Misc queries) | |||
Concatenate | Excel Worksheet Functions | |||
De-concatenate? | Excel Discussion (Misc queries) | |||
I know how to concatenate ,can one de-concatenate to split date? | New Users to Excel |