Excel: Identify Sleeve IDs Covering a Defect Range
Question details
The user needs to return every sleeve ID that covers a defect range, including intermediate, first, and last sleeves.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Sleeve records contain axial positions, repair statuses, defect IDs, and sleeve IDs. The user wants to match those records to a defect's start position and coverage length.
- Observed behavior
- The required sleeve IDs are not yet consolidated into a single deduplicated result.
Confirm that the sleeve records are stored in an Excel table named Table2 and that the headers match the formula, especially Axial Start (m), Repair Status, Sleeve ID, and Defect ID. Make sure your Excel version supports dynamic-array functions such as FILTER, MAP, XLOOKUP, UNIQUE, and TEXTJOIN.
Use dynamic-array functions to return all sleeve IDs for the defect range
Use this approach when the sleeve data is in a structured Excel table and the result should update automatically as records change.
Filter the records to the required defect and the SLEEVED repair status, map each relevant position to its sleeve, remove duplicate IDs, and join the results into one cell. Replace A3 and any table column references with the cells and headers used in your worksheet.
Enter the target defect ID in cell A3, or replace A3 in the formula with the cell that contains your defect identifier. Confirm that the defect start position, length, and required coverage fields are available in the worksheet.
Select the result cell and choose Home > AutoSum > More Functions, or type the formula directly in the formula bar. Use a formula based on FILTER, MAP, XLOOKUP, UNIQUE, and TEXTJOIN, such as =TEXTJOIN(" ",TRUE,UNIQUE(FILTER(Table2[Defect ID],MAP(Table2[Axial Start (m)],Table2[Repair Status],LAMBDA(x,y,IF(y="SLEEVED",XLOOKUP(x,Table2[Axial Start (m)],Table2[Sleeve ID],"",-1,1),"")))=A3,""))).
If the matching records already contain the required sleeve identifiers, change the first FILTER range to Table2[Sleeve ID]. Keep the SLEEVED condition and the UNIQUE and TEXTJOIN functions so the output contains each applicable sleeve ID once.
Press Enter and review the space-separated result. Verify that the output includes the first sleeve, intermediate sleeves, and last sleeve required to cover the defect range.
Filter sleeve positions directly within the defect range
Use this simpler option when the sleeve start positions can be compared directly with the defect start and end positions.
Find and combine sleeve IDs in WPS Spreadsheets
WPS Spreadsheets can perform the same dynamic-array workflow: filter SLEEVED records, match positions with XLOOKUP, remove duplicate sleeve IDs, and combine the results with TEXTJOIN. Its familiar spreadsheet interface and Excel-compatible formulas make it easy to migrate this worksheet.
- 1. Open the workbook: Launch WPS Office, select Spreadsheets, and open the XLSX workbook containing the sleeve table.
- 2. Check the table and criteria cells: In the worksheet, confirm the Table2 headers and locate the defect ID, start position, length, and coverage criteria cells.
- 3. Enter the formula: Select an empty result cell and paste the dynamic-array formula using the WPS Spreadsheets formula bar. Update the table and cell references to match the workbook.
- 4. Verify the sleeve list: Press Enter and check that the returned text includes the first, intermediate, and last sleeve IDs without duplicates.
Frequently Asked Questions
Which Excel functions are used to identify the sleeve IDs?
The dynamic approach uses FILTER to select records, MAP and XLOOKUP to match positions to sleeves, UNIQUE to remove duplicates, and TEXTJOIN to combine the IDs into one cell.
How do I include only repaired or sleeved defects?
Add or retain the condition Table2[Repair Status]="SLEEVED" inside FILTER or MAP. This excludes records with other repair statuses.
Why does the formula return a blank result?
Check that the defect ID matches the value in the criteria cell, the table name and headers are correct, and at least one record has the SLEEVED status. Also confirm that your spreadsheet version supports the required dynamic-array functions.
How can I return sleeve IDs instead of defect IDs?
Change the first FILTER range to the sleeve identifier column, such as Table2[Sleeve ID], while retaining the status and position-matching conditions.
Can the result include duplicate sleeve IDs?
Yes, if UNIQUE is removed. To list each sleeve only once, keep UNIQUE inside TEXTJOIN, for example TEXTJOIN(" ",TRUE,UNIQUE(FILTER(...))).
