concatenate product name based to matching product values.

[longstick]

I am trying to concatenate names based on matching criteria.

Column A - Product #'s

123
243
632A
756A
213A

Column B - Product Values

34.5
23.3
34.5
22.1
23.3

I would like to be able to automaticly combine the product names that have matching product values.

Column C

123, 632A
243, 213A
756A

There could be no matching values or 25 different products with the same product values. ie "123, 243, 632A, 756A, 213A, etc..."

Any thoughts?

 

[cyrilbrd]

Hi,

with vba.
if data contain a list such as:

Products#	prod Value	conc
vtn345	34.5	vtn345
vtn346	22.1	vtn346, vtn348, vtn350, vtn358, vtn368
vtn347	22.5	vtn347, vtn351, vtn359, vtn365, vtn369
vtn348	22.1	vtn346, vtn348, vtn350, vtn358, vtn368
vtn349	23.3	vtn349, vtn361, vtn371
vtn350	22.1	vtn346, vtn348, vtn350, vtn358, vtn368
vtn351	22.5	vtn347, vtn351, vtn359, vtn365, vtn369
vtn352	27.3	vtn352
vtn353	28.2	vtn353
vtn354	23.1	vtn354, vtn360, vtn370
vtn355	21.1	vtn355, vtn356, vtn366
vtn356	21.1	vtn355, vtn356, vtn366
vtn357	21.4	vtn357, vtn367
vtn358	22.1	vtn346, vtn348, vtn350, vtn358, vtn368
vtn359	22.5	vtn347, vtn351, vtn359, vtn365, vtn369
vtn360	23.1	vtn354, vtn360, vtn370
vtn361	23.3	vtn349, vtn361, vtn371
vtn362	23.4	vtn362, vtn372
vtn363	25.5	vtn363
vtn364	25.7	vtn364
vtn365	22.5	vtn347, vtn351, vtn359, vtn365, vtn369
vtn366	21.1	vtn355, vtn356, vtn366
vtn367	21.4	vtn357, vtn367
vtn368	22.1	vtn346, vtn348, vtn350, vtn358, vtn368
vtn369	22.5	vtn347, vtn351, vtn359, vtn365, vtn369
vtn370	23.1	vtn354, vtn360, vtn370
vtn371	23.3	vtn349, vtn361, vtn371
vtn372	23.4	vtn362, vtn372

then vba code:

Function Concat_Unique(Lookup_Value As String, Lookup_Column As Range, Concat_column As Range)

Dim i As Single
Dim Unique As New Collection
Dim Value As Variant
Dim result As String

For i = 1 To Lookup_Column.Cells.Rows.Count
    If Lookup_Value = Lookup_Column.Cells(i).Value Then
        If Len(Concat_column.Cells(i)) > 0 Then
            On Error Resume Next
            Unique.Add Concat_column.Cells(i), CStr(Concat_column.Cells(i))
            On Error GoTo 0
        End If
    End If
Next i

For Each Value In Unique
    result = result & Value & ", "
Next Value

If Len(result) = 0 Then
    Concat_Unique = ""
Else
    Concat_Unique = Left(result, Len(result) - 2)
End If

End Function

Now in C type:
=Concat_Unique(B2,Data!$B$2:$B$100,$A$2:$A$100)
copy down till needed.

but it would maybe be better to extract all unique product values in B and then create a list from it that would return the concatenated products...

such as:

21.1	vtn355, vtn356, vtn366
21.4	vtn357, vtn367
22.1	vtn346, vtn348, vtn350, vtn358, vtn368
22.5	vtn347, vtn351, vtn359, vtn365, vtn369
23.1	vtn354, vtn360, vtn370
23.3	vtn349, vtn361, vtn371
23.4	vtn362, vtn372
25.5	vtn363
25.7	vtn364
27.3	vtn352
28.2	vtn353
34.5	vtn345

would that work for you?