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
Type	Name	Compiled_Types
Curd	Lemon
Pie	Banana
Pie	Apple	Sauce, Juice
Sauce	Apple	Pie, Juice
Juice	Apple	Pie, Sauce
Jam	Stawberry
Juice	Orange	Zest
Zest	Orange	Juice
Soda	Grape

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:68px;"><col style="width:68px;"><col style="width:68px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

</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