Forum Discussion
Combine 2 cells if one cell contains same text
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 |
Hello, please see attached document with proposed solution. It combines functions like textjoin with filters. Hope it works!
Martin
Hello, please see attached document with proposed solution. It combines functions like textjoin with filters. Hope it works!
Martin
- Works perfectly. If you don't mind I want to understand how the code works - could you kindly explain what the code does?
Thank youSure! 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.
