Use a WPS Spreadsheets Formula to List People with a Blank REDIE ID
Question details
The user needs a formula that returns the last name, first name, and date of birth in one cell for every row where the REDIE ID column is blank.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with a Linelist worksheet that contains a REDIE ID column and wants to create a compact list of people who are missing a REDIE ID.
- Observed behavior
- A single spilled formula should output matched records as LastName, FirstName MM/DD/YYYY in one cell for each row that has a blank REDIE ID.
Open your workbook in WPS Spreadsheets and verify the sheet name and exact column letters for last name, first name, date of birth, and REDIE ID. Make sure you have a blank area where the formula results can spill without overwriting existing data.
Use LET, FILTER, TAKE, and CHOOSECOLS to Return Each Blank REDIE ID Record in One Cell
Best when your WPS Spreadsheets or Excel 365 build supports dynamic arrays and you want a single formula to spill all matching names and birth dates automatically.
This formula filters the Linelist data for rows where the REDIE ID column is blank and the last name column is not empty, then combines the first three columns into one text string per matching row.
Adjust the range references to match the actual number of data rows in the workbook.
Launch WPS Office, open the workbook that contains the Linelist worksheet, and select a blank cell where the results can appear.
Type or paste: =LET(missing,FILTER(Linelist!B2:D4,(Linelist!AU2:AU4="")*(Linelist!B2:B4<>"")),TAKE(missing,,1)&", "&CHOOSECOLS(missing,2)&" "&TEXT(TAKE(missing,,-1),"mm/dd/yyyy"))
Replace B2:D4, AU2:AU4, and B2:B4 with the actual row range of the Linelist data, keeping the same number of rows in the filter and result ranges.
Press Enter. If dynamic arrays are enabled, the formula spills all matching rows as combined LastName, FirstName MM/DD/YYYY entries.
Create a Helper Column to Identify Blank REDIE ID Rows Without Dynamic Array Functions
Use this when your WPS Spreadsheets version does not support LET, FILTER, TAKE, or CHOOSECOLS, or when you prefer a row-by-row helper-column approach.
List People with a Blank REDIE ID Using WPS Spreadsheets
Open the same Excel workbook in WPS Spreadsheets and apply a dynamic array formula that filters blank REDIE ID rows and combines last name, first name, and date of birth in one cell.
- 1. Launch WPS Spreadsheets: Open WPS Office and create a new spreadsheet or open the existing Linelist workbook.
- 2. Select the result cell: Click a blank cell outside the data range where the formula results can spill without overwriting data.
- 3. Apply the formula: Paste the LET/FILTER/CHOOSECOLS formula and replace the range references with the actual row numbers in the sheet.
- 4. Confirm the list: Press Enter and check that each row with a blank REDIE ID appears as one combined text value.
Frequently Asked Questions
Will this formula work in all versions of WPS Spreadsheets?
The main formula uses LET, FILTER, TAKE, and CHOOSECOLS, which require a recent WPS Spreadsheets version that supports dynamic arrays. If your version does not recognize these functions, update WPS Office or use the helper-column approach with IF and TEXTJOIN.
How do I change the formula if my data has more than row 4?
Replace B2:D4, AU2:AU4, and B2:B4 with the actual last row, for example B2:D500, AU2:AU500, and B2:B500, keeping the row counts consistent.
Why is the date of birth wrapped in the TEXT function?
The TEXT function formats the underlying date serial number as mm/dd/yyyy so the combined cell displays a readable date instead of a number.
Can I output each person on a separate row instead of a combined text string?
Yes. You can use FILTER(Linelist!B2:D4,Linelist!AU2:AU4="") as a spill range to return Last Name, First Name, and Date of Birth in separate columns without combining them.
What if the formula only returns one row or no rows?
Confirm that the blank REDIE ID cells are truly empty and not containing spaces. The condition Linelist!AU2:AU4="" only matches empty cells.
