Forum Discussion
Combine 2 cells if one cell contains same text
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.
