Function Problems

How to Create a Next Step ID Column from Previous Step IDs in Excel

Maira Mehtab Oct 9, 2026 868 views

Question details

The user needs to generate a 'Next Step ID' column based on existing 'ID' and 'Previous Step ID' values for a flowchart dataset.

How to Create a Next Step ID Column from Previous Step IDs in Excel

Product: Excel
Device & OS: not provided
Scenario: Preparing an Excel dataset for a Visio flowchart where relationships between steps need to be mapped forwards as well as backwards, handling rows with multiple comma-separated IDs.
Observed behavior: Using standard search functions causes partial matches, where searching for ID '1' incorrectly returns steps associated with IDs '10', '11', or '12'.

Before you start

Ensure you are using a modern spreadsheet application, such as Microsoft 365, Excel 2021, or WPS Office, that fully supports dynamic array functions like FILTER and TEXTJOIN.

Solution 1Recommended

Use FILTER and TEXTJOIN with Exact Match Search

This formula reliably maps the Next Step IDs by searching for exact ID matches within the Previous Step ID column, preventing partial match errors.

By dynamically wrapping the search strings and data range values in commas, the formula forces the SEARCH function to look for distinct, whole numbers. This completely prevents single digits from matching double digits.

The FILTER function then isolates the matching rows, and TEXTJOIN combines them if there are multiple next steps associated with a single node.

Prepare your dataset ranges

Ensure your primary step IDs are located in column A (e.g., A2:A11) and your 'Previous Step ID' values are located in column B (e.g., B2:B11).

Enter the array formula

In cell C2 (or your first 'Next Step ID' cell), enter the following formula: =IFERROR(TEXTJOIN(",",TRUE,FILTER($A$2:$A$11,ISNUMBER(SEARCH(","&A2&",",","&$B$2:$B$11&",")))),"")

Fill the formula down

Press Enter to calculate the first result. Click the fill handle (the small square at the bottom-right corner of the cell) and drag it down to apply the formula to the rest of the rows in your flowchart table.

Use FILTER and TEXTJOIN with Exact Match Search

Avoid Partial Matches: The key to this formula is the expression ","&A2&",". Adding commas around each value ensures that a search for ID 1 looks for ",1," instead of just "1", preventing it from falsely matching ",10," or ",11,".

Map Flowchart Data Effortlessly in WPS Office

WPS Office Spreadsheet fully supports advanced array functions like FILTER, TEXTJOIN, and SEARCH. You can effortlessly manage complex Visio flowchart datasets, perform reliable cross-references, and avoid partial string match errors seamlessly.

1. Open WPS Spreadsheet: Launch WPS Office and open your flowchart data workbook.
2. Input the Formula: Select the target Next Step ID cell and paste the provided TEXTJOIN and FILTER formula.
3. Apply to All Rows: Use the intuitive fill handle to drag the formula down and generate the Next Step IDs for all your flowchart nodes in one go.

Fully compatible with Microsoft Excel formulas and functionsNative support for dynamic arrays including FILTER and TEXTJOINLightweight application with a fast, intuitive tabbed interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does my standard SEARCH formula find ID 1 inside ID 10 or 11?

The standard SEARCH function performs a partial string match. Because the text '1' is found inside the text '10', '11', or '21', it returns a false positive. Concatenating commas to the beginning and end of both the search text and the target text forces an exact match.

What if my version of Excel doesn't support the FILTER function?

The FILTER function is available in Microsoft 365, Excel 2021, and modern versions of WPS Office. If you are using an older version (like Excel 2016 or 2019), it will return a #NAME? error. You will need to upgrade to a supported spreadsheet software like WPS Office Free to use dynamic arrays easily.

How does TEXTJOIN help when generating flowchart IDs?

When a single step has multiple subsequent steps branching off it, the FILTER function returns an array of multiple IDs. TEXTJOIN takes this array and seamlessly combines them into a single, comma-separated text string (e.g., '2,3') in one cell.

Excel & Data Problems