How to Combine Excel Cells When a Drug Code Matches
Question details
The user needs to combine multiple generic drug names into a single comma-separated cell whenever their corresponding drug codes match.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing pharmaceutical or inventory data where multiple items share the same identification code and need to be grouped into one cell for reporting.
- Observed behavior
- The user wants to dynamically extract and merge text from a column (generic names) based on a condition in another column (drug codes) without manual copying and pasting.
Ensure your dataset is organized with drug codes in one column and generic names in another. Verify that your version of the spreadsheet software supports dynamic array functions like FILTER and UNIQUE.
Use TEXTJOIN and FILTER to Combine Matching Cells
This method dynamically extracts all matching generic names based on the drug code and joins them with a comma separator.
The FILTER function extracts an array of items that meet your specific criteria, while TEXTJOIN merges this array into a single text string, automatically ignoring any empty cells.
Assume your drug codes are in column A (A2:A100) and generic names are in column B (B2:B100). The specific drug code you want to match is located in cell A2.
Click on the destination cell where you want the combined result. Type the formula: =TEXTJOIN(", ",TRUE,FILTER($B$2:$B$100,$A$2:$A$100=A2,""))
Press Enter to apply the formula. Click the cell again, hover over the bottom-right corner until the cursor turns into a cross, and drag the fill handle down to apply this logic to other rows.
Combine Matching Cells While Removing Duplicates
If multiple identical generic names exist for the same drug code, use the UNIQUE function to ensure each name appears only once in the combined cell.
Efficiently Process Spreadsheets in WPS Office
WPS Spreadsheet fully supports advanced dynamic array functions like TEXTJOIN, FILTER, and UNIQUE, allowing you to manipulate and combine complex data effortlessly.
- 1. Download and Install: Download WPS Office from the official website and install it on your computer.
- 2. Open your Spreadsheet: Launch WPS Spreadsheet and open your existing Excel file containing the drug codes and generic names.
- 3. Apply the Formula: Select the target cell and use the TEXTJOIN and FILTER functions exactly as you would in Microsoft Excel to combine your data.
- 4. Save your Work: Save the document in .xlsx format by going to Menu > Save As, ensuring seamless sharing with other Office users.
Frequently Asked Questions
What if my spreadsheet software returns a #NAME? error?
A #NAME? error usually means your spreadsheet version does not support modern dynamic array functions like FILTER or TEXTJOIN. You will need to upgrade to a newer version, such as Office 365, Excel 2019+, or the latest version of WPS Office.
Can I use a different separator instead of a comma?
Yes, the first argument in the TEXTJOIN function dictates the separator. You can change ", " to a semicolon "; ", a dash "-", or even a line break CHAR(10) depending on your formatting needs.
How does the TRUE argument in TEXTJOIN work?
The TRUE argument tells the TEXTJOIN function to ignore any empty cells within the selected range. If you change it to FALSE, it will include empty cells, which might result in extra commas or awkward spacing in your combined text.
Will this formula work with data formatted as a Table?
Absolutely. You can use structured references if your data is in an Excel Table format. For example, your formula would look like: =TEXTJOIN(", ",TRUE,FILTER(Table1[GenericNames],Table1[DrugCodes]=[@[Drug Code]],"")). This makes your formulas easier to read and automatically adjusts as new rows are added.
