OmniDocExcel extracts non-duplicate data: function method to remove duplicate items from array
Explain clearly how to use functions to extract unique values from a column of data in Excel: the new version uses UNIQUE, and the old version uses INDEX, MATCH and COUNTIF combination formulas, and gives the check items and results verification methods before starting.
Key points of this article
- Let’s look at the Excel version first: Excel 2021 and the subscription version of Excel can use the UNIQUE function directly. Earlier versions such as 2016 and 2019 need to use the INDEX, MATCH and COUNTIF combination formulas.
- UNIQUE outputs the data in the order in which it appears. When sorting is required, a layer of SORT is placed outside.
- Check spaces, textual numbers, and case before removing duplicates. These are common reasons why the number of results does not match.
- Deduplication and finding duplicates are two different things: deduplication keeps each value once, and finding duplicates can be done by counting the number of times using COUNTIF and filtering.
- After getting the result, use the counting method to check the number of different values and check whether there are overflows or wrong values.
If you have a customer list in your hand and the same name appears several times and you want each name to be listed only once, many people will subconsciously delete it manually. When there is too much data, it is easy to make mistakes: missing deletions, omissions, or inconsistent spaces and capitalization, and the final results do not match.
A more common requirement is to deduplicate a column of data and obtain a new list containing only different values, while the original data remains unchanged. This article is written for people who often deal with tables but don’t want to manually filter every time. It explains several ways to use functions to extract unique data in Excel, which one is suitable for which version, and how to check when the results are wrong.
After reading, you can do three things: decide which function you should use, write a formula that can be pasted directly, and check whether the result is complete.
How does the EXCEL function extract a duplicate item from an array? One sentence conclusion
One sentence conclusion: Excel 2021 and the subscription version of Excel can directly use the UNIQUE function to obtain a unique list in one step; earlier versions can use array formulas composed of INDEX, MATCH and COUNTIF to achieve the same effect.
| your situation | Recommended practices | reason |
|---|---|---|
| Use Excel 2021 or a subscription version of Excel | Use the UNIQUE function | One formula outputs all deduplication results, and the results will be automatically updated when the source data changes. |
| Use older versions such as 2016, 2019, etc. | Combine formulas with INDEX, MATCH, and COUNTIF | These versions do not have UNIQUE, and the combined formula can extract unique values one by one. |
| Need to be sorted alphabetically or numerically | Put a layer of SORT on UNIQUE | UNIQUE is output in order of appearance and will not be automatically sorted. |
| You only want to retain values that appear only once in the original data | Using the third parameter of UNIQUE | This is different from deduplication: deduplication keeps each value once, here keeps values that are never repeated |
| The data is still in the PDF table | Export to Excel first and then process | Formulas can only be applied to cells. Tables in PDF need to be converted first. |
Before you start: Confirm these points first
- Excel Version: View the version number on the Account or About page. UNIQUE relies on the dynamic array function of the new version. When opening a table with it in the old version, a name error will be displayed. In this case, a combination formula should be used instead.
- Data range: Determine which column is to be emphasized, and whether the header row and blank row are mixed in the range. Empty cells will be output as a value, and header rows will be treated as normal data.
- Data Type: Numbers and text that look like numbers are different values in Excel and will be separated during deduplication. Data copied from other systems often has this problem. Check a few lines first.
- Spaces and Case: "Zhang San" is different from "Zhang San" with an extra space at the end. UNIQUE is not case sensitive in English, so "ABC" and "abc" will be treated as the same value.
- Where to put the result: The result of the new version of the function will be output overflowing downward and to the right. If there is content below or to the right of the overflow area, the result will be blocked, so space must be made first.
Steps to use UNIQUE function to extract unique data
- Select the source data columns A2 to A31. For example, this is a customer list with 30 rows. The first row is the title. Enter "List" in the name box and press Enter to name this area "List". Subsequent formulas can directly reference it without having to repeatedly enter the area address.
- Select a blank cell as the upper left corner of the result, for example C2. The deduplication results will be sorted downward from here.
- Enter the formula =UNIQUE(list) and press Enter. The result will list each different value in the order in which the data appears.
- If you need to sort, change the formula to =SORT(UNIQUE(list)) and press Enter, the results will be sorted in ascending order.
- Check whether the output is complete: the number of result rows should be less than or equal to the number of source data rows, and there should be no overflow prompt in the cell. If so, clear the blocked area and re-enter the formula.
- If you need to count how many times each value appears, enter =COUNTIF(list, C2) next to the result and fill down to see the number of times each value appears.
- If you want to keep a copy of the results that does not change with the source data, select the output area to copy, and then use the value in Paste Special to paste it elsewhere.
Combination formulas for Excel 2016 and 2019
Earlier versions do not have UNIQUE, you can enter the following formula in cell C2 and then fill down:
=IFERROR(INDEX(List,MATCH(0,COUNTIF(INDIRECT("C1:C"&ROW()-1),List),0)),"")
The idea of this formula is: COUNTIF counts whether each value in the list has appeared in the above result; MATCH finds the first value that has not appeared before, which is the position where the count is 0; INDEX takes out this value. INDIRECT causes the range of the result area to automatically expand with line numbers, starting at C1 and continuing to the line immediately preceding the current line. C1 must be a header cell and its content cannot be the same as the value in the list. When there are no more distinct values, the formula returns blank and IFERROR takes care of masking errors.
In some older versions, this formula needs to be confirmed by pressing Ctrl+Shift+Enter before it can be run as an array formula; in newer versions, just press Enter. When filling down, the number of rows should be greater than or equal to the number of different values, and the extra rows will be displayed as blank.
Check each item after completion
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Quantities of different values | If there are no empty cells in the source data, use =SUMPRODUCT(1/COUNTIF) to calculate the number of different values and compare them with the number of rows in the result | If not, check for spaces, text-type numbers, or extra header lines |
| Check if any values are missing | Next to the results, use COUNTIF to count the number of times each value is used, ensuring that every result can be found in the source data | Check if the area named "List" has fewer rows |
| Whether there is a mistaken merging or separation | Check if values with different case or spaces at the beginning and end have been merged or separated | Use TRIM to clean up spaces; If case-sensitive is needed, use other formulas |
| Are there error values? | Find overflow tips, #NAME?, and #N/A | Overflow clears the blocking area; Name errors are usually version issues; The blank at the end of the combination formula is not an error |
| Will the results be updated? | Modify a value in the source data and observe whether the result changes | If the result has been pasted as a value, it will not update and will need to be regenerated |
If the source data comes from OCR recognition or machine translation, important details should be checked before and after deduplication, especially names, serial numbers, and amounts.
❓ Frequently Asked Questions (FAQ)
1. How to remove deduplication when data is in PDF spreadsheets?
First, convert the table from the PDF to Excel. If it is a scan, OCR recognition must be done first, then the recognition result verified, as the recognition may be mistaken. The PDF editor in the OmniDoc desktop client supports OCR and can export PDFs to Excel. The download link is download.omnidoc.top. After exporting, deduplicate as described in this article.
2. After deduplication, how do you check how many times each value appears?
Next to the result, use COUNTIF, for example, =COUNTIF(list, C2), and fill it down. This column of numbers is the number of times each value appears in the source data.
3. What if you just want to find duplicate records instead of removing duplicates?
Finding repetition is another need. You can enter =COUNTIF next to the source data; rows greater than 1 are duplicate records, and then use the filter function to display these rows.
4. What is the reason for the result being one less value than expected, or an extra blank?
There are three common reasons: first, some values end with spaces that look the same but are mistaken for different values; Second, numbers and text-based numbers are mixed together; Third, the source data range contains empty cells. First use TRIM to handle the spaces, then check the numeric format, and finally confirm that the area named "List" is correct.
📝 Summary
How to extract a duplicate item from an array in an EXCEL function depends on the version: the new version uses UNIQUE, while the old version uses INDEX, MATCH, and COUNTIF combination formulas. Before starting, check the spaces, data types, and reference ranges. After completion, use COUNTIF to compare the results to confirm deduplication is complete.
