0

How to filter duplicate values in column A and filter out rows which have column C blank.

I have 2200 rows in Col A (the first 1400 rows also have data in Col B & C, the next 800 rows are duplicates of the first 1400 rows, but don't have data in Col B or C).

I want to only keep the 800 duplicate rows, but keep the one which has data in Col B & C, and remove the row which is has a blank value in Col B & C.

3 Answers 3

1

Try using this as formula for a fourth column, D

=IF(ISNUMBER(IFNA(MATCH(A2,$A$1:$A1,0),"")),"",COUNTIF($A4:$A$5000, "="&A2))

This counts all occurrences of a name only the first time the name is encountered. Thus the names to keep will have value of column D to be equal to a number whereas others (one's that have no duplicates and the actual duplicates) will have 0 or blank as the value of column D.

This can be observed in the image below. Here only Adam, David and Michael are to be kept cause they have duplicate entries and rightly so they have computed values for column D.

Once this is done, you can simply sort column D (Descending) and then remove all the rows at the very end of the list which have 0 or blank as the value of column D.

enter image description here

0
1

Removing duplicates

You can use the Remove Duplicates feature on the Data tab of the Ribbon. Select all of your data and then choose "Remove Duplicates".

Remove Duplicates

Then, check the boxes for the columns that you want to check for duplicates. Make sure to indicate whether your data selection contains headers. The duplicate entries will be deleted.

Duplicates dialog

Removing blanks

This is a little bit harder. You can use an AutoFilter by choosing Filter on the Data tab of the Ribbon. Then, in the header column that contains the blanks, choose the arrow button. You can modify these checkboxes so that only "Blanks" is selected to identify which rows contain blanks.

Filter

Once the blank rows are being shown, you can hold down Ctrl and select each one of them, and then delete them. Make sure not to select them all at once because you will also select the filtered rows in between them.

0

I cannot comment to @bdr9, but if I could, I would add that you can also find blanks by:

  1. Highlight range
  2. Press F5
  3. Select Special...
  4. Select Blanks
  5. Now all rows with a blank are highlighted
  6. Right click on the row number of one of the highlighted rows and delete or hide as necessary.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .