Excel find duplicate rows and blank them out
![excel find duplicate rows and blank them out excel find duplicate rows and blank them out](https://i1.wp.com/windowsloop.com/wp-content/uploads/2021/03/clear-conditional-formatting-in-excel-150321.jpg)
![excel find duplicate rows and blank them out excel find duplicate rows and blank them out](https://spreadsheetplanet.com/wp-content/uploads/2020/09/How-to-find-duplicates-in-excel-using-filter.png)
The main formula is an INDEX function, broken down as follows: The first thing you'll notice is that the primary function, the INDEX function, is wrapped in an IFERROR statement, so that I can copy the formula all the way down to row 76 and after it finds the last of the unique names, it will just insert a blank. They are identical except that the one in column F refers to the first names in column A and the other to the last names in column B. Notice that they have curly brackets around them so they are array formulas that need to be entered using Ctrl + Shift + Enter. Now the key to this process is the formulas that are in columns F & G, and those are, respectively: The result of this is to put a 1 into the first unique instance of a concatenated name, and a 2, 3, 4, etc.
![excel find duplicate rows and blank them out excel find duplicate rows and blank them out](https://cdn.extendoffice.com/images/stories/doc-excel/find-duplicate-rows/doc-find-duplicate-rows-1.png)
This also checks if there is anything in column A and if so, will do a count of the concatenated names from cell C2 down to the row the formula is in. In column D I inserted the following formula: If not it inserts a blank, if so it concatenates the first and last names so I have a unique identifier for each row. This checks if there is anything in column A. In column C I inserted the following formula: As you can see here, they go down to only row 22:įirst I inserted some helper columns in C & D. In columns F & G I want to pull in only the unique names from that list. They are filled in to row 60, and there are several duplicates, some as many as four times. So here I have a data range from A1:B76 which has a column for first names and last names.
#EXCEL FIND DUPLICATE ROWS AND BLANK THEM OUT DOWNLOAD#
When you get a preview, look for Download in the upper right hand corner. You can download the file here and follow along. In this tutorial we are going to extract only the unique list of names from a long list that includes many duplicates, but the first and last names are in different columns.