Keep Only the First Occurrence of Each Phone Number in WPS Spreadsheet
Question details
Keep only the first appearance of every phone number in a list without manually deleting duplicate rows.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user has a column of phone numbers containing duplicates and needs a separate output list that preserves the original first occurrence of each number.
- Observed behavior
- The final output should include each phone number once, in the order in which it first appears in the source list.
Place the phone numbers in one continuous column with a header-free data range, such as A1:A8. Ensure the destination cells for the FILTER result are empty so the formula can spill the results into them.
Use COUNTIFS and FILTER to return first-occurrence phone numbers
Use this method when you want a formula-based result that automatically keeps the first instance of each phone number in its original order.
The COUNTIFS formula assigns 1 to the first time a phone number appears and a higher number to later duplicates. FILTER then returns only the rows marked with 1.
In cell B1, next to the first phone number in A1, enter =COUNTIFS($A$1:A1,A1) and press Enter.
Select B1, then drag the fill handle down to the last row of the phone-number list. A value of 1 identifies the first occurrence of each number.
Click an empty output cell, such as C1, enter =FILTER(A1:A8,B1:B8=1), and press Enter. Replace A1:A8 and B1:B8 with the full ranges of your list.
Use Remove Duplicates for a one-time cleanup
Use this option when you want to permanently reduce the original list to unique phone numbers instead of creating a formula-driven output.
Create a Dynamic First-Occurrence List in WPS Spreadsheet
WPS Spreadsheet can identify repeated phone numbers and display only their first appearances with COUNTIFS and FILTER. The formula-based approach updates the unique output as your source list changes.
- 1. Enter phone numbers in a source column: Paste or type the phone-number list into column A in WPS Spreadsheet.
- 2. Mark first appearances: Enter =COUNTIFS($A$1:A1,A1) in B1 and fill the formula down alongside the source list.
- 3. Generate the unique result: In an empty cell, enter =FILTER(A1:A8,B1:B8=1) and adjust the ranges for your data.
- 4. Use the dynamic output: Review the spilled results, which show each phone number once based on its first position in the source column.
Frequently Asked Questions
Why does COUNTIFS return 1 for the first phone number only?
The expanding range $A$1:A1 starts at the first source cell and grows as you fill down. When a phone number is encountered for the first time, COUNTIFS finds only one match and returns 1.
Can I use COUNTIF instead of COUNTIFS for one phone-number column?
Yes. For a single criterion, enter =COUNTIF($A$1:A1,A1) in the helper column. It produces the same first-occurrence markers as the COUNTIFS formula.
How do I make the formula include more than eight rows?
Replace A1:A8 and B1:B8 in the FILTER formula with ranges that extend through your last data row, such as =FILTER(A1:A500,B1:B500=1).
Does FILTER preserve the order of the original phone-number list?
Yes. FILTER returns matching values in their existing source order, so the first occurrence of each phone number remains in the same sequence as the original list.
What should I do if the FILTER result shows a spill error?
Clear any values, merged cells, or other content in the cells below and beside the FILTER formula. The result needs an empty area to expand into.
