How to Compare Comma-Separated Strings and Mark Data in Excel
Question details
The user needs to automate an Excel process to compare comma-separated strings across two columns, find mutual citations, look up their application years, and mark the older related patents for deletion.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Data analysis of patent citations requiring complex string manipulation, array comparison, and conditional row marking.
- Observed behavior
- Currently, the user has comma-separated strings in backward and forward citation columns and needs a formula-based workflow to extract matches, retrieve years, and flag older entries automatically.
Ensure you are using a modern version of Excel or WPS Spreadsheet that supports dynamic array formulas like TEXTSPLIT and XLOOKUP, as these are essential for splitting strings and retrieving data efficiently.
Use Dynamic Array Formulas to Compare and Mark Patents
Combine TEXTSPLIT to break comma-separated strings into arrays, use XLOOKUP to fetch matching years, and apply an IF statement to flag older patents.
This method leverages modern spreadsheet functions to handle comma-separated lists within a single cell, cross-reference them, and apply conditional logic without needing VBA macros.
In column E, use the TEXTSPLIT function to break the comma-separated strings from the backward and forward citation columns into arrays. Combine this with a filtering function like FILTER or XMATCH to compare the two arrays and return the mutual citation.
In column F, use the XLOOKUP function. Set the extracted citation in column E as your lookup value, select column B (containing all patent numbers) as the lookup array, and select the corresponding year column as the return array to fetch the application year.
In column G, write an IF formula to compare the retrieved application year in column F with the current row's year. Use a formula like =IF(F2<C2, "DELETE", "") so that if the matched patent's year is earlier, it outputs 'DELETE', otherwise it remains blank.
Easily Handle Complex Data Comparisons in WPS Spreadsheet
WPS Spreadsheet fully supports modern dynamic array functions like XLOOKUP and TEXTSPLIT, making it highly capable of parsing comma-separated strings and automating complex multi-step data lookups seamlessly.
- 1. Open your dataset: Launch WPS Spreadsheet and open your workbook containing the patent citation data.
- 2. Apply string splitting formulas: Click on the target cell and use the formula bar to input TEXTSPLIT alongside array-matching formulas to extract your mutual citations.
- 3. Lookup and flag data: Implement XLOOKUP to fetch the application years and use IF conditions in the adjacent columns to automatically mark the older rows for deletion.
Frequently Asked Questions
What if my spreadsheet software does not support TEXTSPLIT?
If TEXTSPLIT is not available in your version, you can use the 'Text to Columns' feature under the Data tab to separate the comma-separated strings into individual cells across columns. Once separated, you can use standard MATCH, VLOOKUP, or COUNTIF functions to compare the ranges.
How can I prevent the XLOOKUP formula from returning #N/A errors?
You can utilize the built-in [if_not_found] argument within the XLOOKUP function. By adding a specific text string (like "Not Found") as the fourth argument, the formula will display a clean message instead of an error when no matching patent is located.
Can I use conditional formatting instead of marking 'DELETE' in a column?
Yes. Instead of creating a text flag in column G, you can create a new Conditional Formatting rule using the exact same logical test (comparing the year values) to automatically highlight the entire row in red if the matched patent is older.
How do I delete the flagged rows automatically?
Formulas alone cannot delete rows. After flagging rows with 'DELETE' in column G, highlight your header row, turn on Data Filtering, and filter column G to only show 'DELETE'. Select all the visible rows, right-click, choose 'Delete Row', and then clear the filter.
