Formula: Duplicate Numbers

[pochie2741]

Hello Everyone,

Need help please for a formula under Results Column:

• If the ticket numbers are the same (with duplicates below it), results will show combination of the fruits separated by a slash. The result should only show on the first ticket number. The rest of the same ticket numbers will show as blank.
• If the ticket number does not have duplicate tickets below it, it will show the assigned fruit.

Ticket	Fruit	Result
123	Apple	Apple/Orange/Mango
123	Orange
123	Mango
456	Banana	Banana
789	Apple	Apple/Banana
789	Banana
321	Grapes	Grapes

 

[Fluff]

Hi & welcome to MrExcel.
How about

Function pochie(Rng As Range, Crit As Range) As String
   Dim cl As Range
   
   For Each cl In Rng.Columns(1).Cells
      If cl.Value = Crit Then pochie = pochie & "/" & cl.Offset(, 1).Value
   Next cl
   pochie = Mid(pochie, 2)
End Function

used like

+Fluff 1.xlsm
	A	B	C
1	Ticket	Fruit	Result
2	123	Apple	Apple/Orange/Mango
3	123	Orange
4	123	Mango
5	456	Banana	Banana
6	789	Apple	Apple/Banana
7	789	Banana
8	321	Grapes	Grapes
Data

Cell Formulas
Range	Formula
C2:C8	C2	=IF(COUNTIFS(A$2:A2,A2)=1,pochie(A2:B8,A2),"")

 

[pochie2741]

Sorry, I'm not familiar with VBA code. Is there a way to change "pochie" into a formula?

 

[Fluff]

With your version of Xl, not really. If you had xl2019 or newer you could use the TextJoin function, but that does not exist in xl2016.

 

[pochie2741]

Yes, im using 2019. How do I winsert textjoin in the formula?
=IF(COUNTIFS(A$2:A2,A2)=1,pochie(A2:B8,A2),"")

 

[Fluff]

In that case how about

+Fluff 1.xlsm
	A	B	C
1	Ticket	Fruit	Result
2	123	Apple	Apple/Orange/Mango
3	123	Orange
4	123	Mango
5	456	Banana	Banana
6	789	Apple	Apple/Banana
7	789	Banana
8	321	Grapes	Grapes
Data

Cell Formulas
Range	Formula
C2:C8	C2	=IF(COUNTIFS(A$2:A2,A2)=1,TEXTJOIN("/",,IF($A$2:$A$8=A2,$B$2:$B$8,"")),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[pochie2741]

In that case how about

+Fluff 1.xlsm
	A	B	C
1	Ticket	Fruit	Result
2	123	Apple	Apple/Orange/Mango
3	123	Orange
4	123	Mango
5	456	Banana	Banana
6	789	Apple	Apple/Banana
7	789	Banana
8	321	Grapes	Grapes
Data

Cell Formulas
Range	Formula
C2:C8	C2	=IF(COUNTIFS(A$2:A2,A2)=1,TEXTJOIN("/",,IF($A$2:$A$8=A2,$B$2:$B$8,"")),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you very much! it works perfectly

 

[Fluff]

You're welcome & thanks for the feedback.