Concatenate matching Row Data in single cell with exclusions

[rpouvreau]

I have been trying to determine how to search matching values in a column and return another column's value on the same row into a concatenated string, excluding the data in the second column from the string in the third

Using =Lookup_concat(A12,'other-finishes'!$C:$C,'other-finishes'!$A:$A) I am able to pull the values I need from the sheet, however, I do not want the item in the second column to also be listed in the string in the third.

As an example:

Base Sku id id string

244-117-01	baby-appleseed-millbury-3-in-1-convertible-crib-espresso	baby-appleseed-millbury-3-in-1-convertible-crib-slate baby-appleseed-millbury-3-in-1-convertible-crib-coco baby-appleseed-millbury-3-in-1-convertible-crib-espresso baby-appleseed-millbury-3-in-1-convertible-crib-moon-gray baby-appleseed-millbury-3-in-1-convertible-crib-pure-white
244-117-01	baby-appleseed-millbury-3-in-1-convertible-crib-moon-gray	baby-appleseed-millbury-3-in-1-convertible-crib-slate baby-appleseed-millbury-3-in-1-convertible-crib-coco baby-appleseed-millbury-3-in-1-convertible-crib-espresso baby-appleseed-millbury-3-in-1-convertible-crib-moon-gray baby-appleseed-millbury-3-in-1-convertible-crib-pure-white
244-117-01	baby-appleseed-millbury-3-in-1-convertible-crib-pure-white	baby-appleseed-millbury-3-in-1-convertible-crib-slate baby-appleseed-millbury-3-in-1-convertible-crib-coco baby-appleseed-millbury-3-in-1-convertible-crib-espresso baby-appleseed-millbury-3-in-1-convertible-crib-moon-gray baby-appleseed-millbury-3-in-1-convertible-crib-pure-white
244-117-01_244-119-09	baby-appleseed-nursery-set-millbury-crib-double-dresser-slate	baby-appleseed-nursery-set-millbury-crib-double-dresser-slate baby-appleseed-nursery-set-millbury-crib-double-dresser-espresso baby-appleseed-nursery-set-millbury-crib-double-dresser-pure-white baby-appleseed-nursery-set-millbury-crib-double-dresser-moon-gray baby-appleseed-millbury-nursery-set-convertible-crib-double-dresser-coco
244-117-01_244-119-09	baby-appleseed-nursery-set-millbury-crib-double-dresser-espresso	baby-appleseed-nursery-set-millbury-crib-double-dresser-slate baby-appleseed-nursery-set-millbury-crib-double-dresser-espresso baby-appleseed-nursery-set-millbury-crib-double-dresser-pure-white baby-appleseed-nursery-set-millbury-crib-double-dresser-moon-gray baby-appleseed-millbury-nursery-set-convertible-crib-double-dresser-coco
244-117-01_244-119-09	baby-appleseed-nursery-set-millbury-crib-double-dresser-pure-white	baby-appleseed-nursery-set-millbury-crib-double-dresser-slate baby-appleseed-nursery-set-millbury-crib-double-dresser-espresso baby-appleseed-nursery-set-millbury-crib-double-dresser-pure-white baby-appleseed-nursery-set-millbury-crib-double-dresser-moon-gray baby-appleseed-millbury-nursery-set-convertible-crib-double-dresser-coco

<tbody>
</tbody>

I know I need to insert an IF function that determines if the value in id is in the string, to exclude that id, I just can't get my head around how to do so.

Thanks,

Renee

 

[Thomas2016]

Your Lookup_concat function is a UDF; without knowing how it's written, your question is going to be difficult to answer.

 

[rpouvreau]

Your Lookup_concat function is a UDF; without knowing how it's written, your question is going to be difficult to answer.

I was able to find a UDF on another site,

Function Lookup_concat(Search_string As String, _
Search_in_col As Range, Return_val_col As Range)


Dim i As Long
Dim temp() As Variant
Dim result As String
ReDim temp(0)


For i = 1 To Search_in_col.Count
If Search_in_col.Cells(i, 1) = Search_string Then
temp(UBound(temp)) = Return_val_col.Cells(i, 1).Value
ReDim Preserve temp(UBound(temp) + 1)
End If
Next


If temp(0) <> "" Then
ReDim Preserve temp(UBound(temp) - 1)
Unique temp
For i = LBound(temp) To UBound(temp)
result = result & " " & temp(i)
Next i
Lookup_concat = Trim(result)
Else
Lookup_concat = ""
End If


End Function

Renee