concatenate if

[Betty]

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.

 

[Mike H]

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]

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