Combine 2 cells if one cell contains same text

[AnusornKh]

Hi, 

I want to combine the Column "Generic Name" into one cell seperated by a comma if Column "Drug Code" has the same code

As you see from the example below

Drug Code 0401000010 has 2 Generic Name

0401000010 = Amoxicillin

0401000010 = Clavulanic Acid

i want it to be combined into one cell (Coumn "Combined") 

0401000010 = Amoxicillin, Clavulanic Acid

What formula can i use to achieve this result

Thank you

DRUG CODE	Generic Name	Combined
0401000010	Amoxicillin	Amoxicillin,Clavulanic acid
0401000010	Clavulanic acid	Amoxicillin,Clavulanic acid
0401000011	Amoxicillin	Amoxicillin,Clavulanic acid
0401000011	Clavulanic acid	Amoxicillin,Clavulanic acid
0401000056	Cetirizine	Cetirizine, Cetirizine Hydrochloride
0401000056	Cetirizine Hydrochloride	Cetirizine, Cetirizine Hydrochloride

[#2 · Accepted solution]

@AnusornKh 

Hello, please see attached document with proposed solution. It combines functions like textjoin with filters. Hope it works!

Martin

[#3]

Works perfectly. If you don't mind I want to understand how the code works - could you kindly explain what the code does?

Thank you

[#4]

@AnusornKh 

Sure! Let's decompose this;

First, I used =FILTER($C$4:$D$9,$C$4:$C$9=C4) which would return all the rows in the table that have the same code as the cell C4:

Then I apply =CHOOSECOLS(FILTER($C$4:$D$9,$C$4:$C$9=C4),2) to select the second column of this result. The output of this is just a single column with Amoxicillin on the first row and Clavulanic acid on the second one. I do this because I know I will have to work with this in the next step.

Next step is to use =TEXTJOIN(",",,CHOOSECOLS(FILTER($C$4:$D$9,$C$4:$C$9=C4),2)) to join these two rows into a single cell, separated by a "," as you desired. Now I only have the names, but I also want the codes to be there.

I use again =CHOOSECOLS(FILTER($C$4:$D$9,$C$4:$C$9=C4),1) but note that this time I want the first column (Codes) not the second one. I then apply a UNIQUE function to this previous formulation in order to return a single row, to be coherent with the previos single row that we got from joining the text together in a single cell.

The final step is to use HSTACK to create a little array of two adjacent cells, one with the drug code and another one with the combined text.