Display multiple unique values from column in one cell?

[ivanribic]

I've been fiddling with this for hours and can't make sense of it so figured I'd ask for suggestions.

I'm trying to work out a formula in column C that will display select values from column A as pictured below. I'd like it to do this only if there is more than one occurrence of a value in column B, and want it to omit the value in A that is in the current row. Hopefully that makes sense. I have been trying with an INDEX MATCH combination but cannot figure out how to display more than the first value from Column A.

=IF(COUNTIF($B$2:$B2,$B2)>1,INDEX($A$2:$A$400,MATCH($B2,$B$2:B$400,0),0),"")

I'm not sure if this can be done with a formula or if VBA makes more sense? Thanks in advance for any direction you can provide.

 

[njimack]

I think a UDF is best...

Function Compiled(rng As Range)

Application.Volatile


Dim c As Range


For Each c In rng
    If c.Row <> Application.Caller.Row Then
        If c = Application.Caller.Offset(, -1) Then Compiled = Compiled & c.Offset(, -1) & ", "
    End If
Next c


If Right(Compiled, 2) = ", " Then Compiled = Left(Compiled, Len(Compiled) - 2)
If Len(Compiled) = 0 Then Compiled = ""




End Function

Sheet1

	A	B	C
1	Type	Name	Compiled_Types
2	Curd	Lemon
3	Pie	Banana
4	Pie	Apple	Sauce, Juice
5	Sauce	Apple	Pie, Juice
6	Juice	Apple	Pie, Sauce
7	Jam	Stawberry
8	Juice	Orange	Zest
9	Zest	Orange	Juice
10	Soda	Grape

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:68px;"><col style="width:68px;"><col style="width:68px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C2 =Compiled($B$2:$B$10) C3 =Compiled($B$2:$B$10) C4 =Compiled($B$2:$B$10) C5 =Compiled($B$2:$B$10) C6 =Compiled($B$2:$B$10) C7 =Compiled($B$2:$B$10) C8 =Compiled($B$2:$B$10) C9 =Compiled($B$2:$B$10) C10 =Compiled($B$2:$B$10) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[ivanribic]

Neil, that worked brilliantly. Thank you so much for the help, I never would have worked that one out.

 

[njimack]

You're welcome

 

[Momentman]

If you use 2016 then maybe
=IF(COUNTIF($B$2:$B$10,$B2)=1,"",TEXTJOIN(",",TRUE,IF($B$2:$B$10=$B2,IF(ROW($B$2:$B$10)<>ROW(),$A$2:$A$10,""),"")))

Entered with CTRl+SHIFT+ENTER

Just to show that a formula can do it, with 40,000 rows, it might choke though