Help with Concatenate Data

Hello All

I have a problem I hope you all can help me with.

I have data in column's A & B. I am trying to combine data from Column B based on data in Column A to Column C. I was trying do something similar to SUMIF, to concatenate but it doesn't work.

I am trying to achieve the following in Column C:

Col. A Col. B Col. C
1 dog dog, chicken
2 cat cat, pig, rat
1 chicken dog, chicken
7 sheep sheep
4 dog dog, chicken
5 cow cow
2 pig cat, pig, rat
5 cow cow
2 rat cat, pig, rat

Shg, thank you so much for your help!

I added the function and input the formula then dragged it down, but it is displaying the same information for all the rows.

For example:

c1 is populating: dog, cat, chicken, sheep, dog, cow, pig, cow, rat

I would like:
c1: dog, chicken
c2: cat, pig, rat
c3: dog, chicken
c4: sheep
c5: dog, chicken

Thanks again!

Don't understand why row 5 is "dog, chicken" since 4 is unique in col.A.

Function ConcUniq(rng As Range, myItem) As String
    Dim e
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For Each e In Filter(rng.Parent.Evaluate("transpose(if(" & rng.Columns(1).Address & _
                            "=" & myItem & "," & rng.Columns(2).Address & "))"), False, 0)
            .Item(e) = Empty
        Next
        ConcUniq = Join(.keys, ", ")
    End With
End Function

Originally Posted by jindon

Don't understand why row 5 is "dog, chicken" since 4 is unique in col.A.

Hi Jindon,

Row 5 is "dog, chicken" since row 5's b value "dog" was combined with another value. However that would not be the case if it were not, like row 4.

Is there a way to get it to combine?

Thanks!

Still not clear.

What if multiple "dog" where col.A have many different/multiple numbers?

For example:

there could be multiple. There would probably need to be a loop.

Since "dog and chicken" were combined, any where either word is found would need to be combined.

If row 4's B value was "chicken" instead of "sheep", C4 would need to be "dog, chicken".

or if B8's value was "rat" instead of "cow" then C8 would need to be "cat, pig, rat, cow".

Sorry but logic is not clear so I will leave this to someone else.

thanks for responding

The only issue is row number 5 in the table above.

I somehow need the C5 value to be "dog, chicken", since dog was combined with chicken in another cell in column C (c1 & c3).

Not sure if it possible to do.

Originally Posted by shg

I don't understand the logic, but expect it doesn't relate to dog and chickens.

The animals in this example represent ppl conducting transactions.
In some instances dog may conduct transactions for 1 (column A) and also 4, but chicken also conducted transactions for 1.

So row 1 and 3 show dog, and chicken as conductors. But since dog also did transactions for 4 I need to combine this transaction with the others. I can sort by column c in the end and subtotal at each change in c. However with the current formula dogs transaction (4) will not be combined with the other transactions he conducted.