0
| A            | B      | C     | D      | E      |
| Product code | Length | Width | Height | Weight |

I want to remove a row if all the values in that row are duplicates else where. Note that values in B, C, D and E may repeat elsewhere in the table, so conditional formatting doesn't help. I want to ensure the values in B, C, D and E are unique to each Product code in its row.

I would also like to delete rows that have the same Product code but have 0's in B, C, D and E.

Can anybody please point me in the right direction in what I need to look up on in order to achieve the above?

2

Select the whole table and go to the ribbon under data. Click remove duplicates. This only removes rows where all the columns selected match another row keeping one of each unique row.

For finding all the rows where there is 0s in a bunch of columns you could use a helper column with something like

=countif(B2:E2,0) 

If this equals 4 then you know all the columns are zero so filter the table for 4s and delete all the rows (although this doesn't check for duplicate product codes).

If you need to find rows that have a duplicate in column A and all 0s in columns B:E you could use the following

=IF(AND(COUNTIF(B2:E2,0)=4,COUNTIF(A:A,A2)>1),TRUE,FALSE)

The first countif checks to see if all the columns from B to E are 0 (4 cells). The second counts the number of cells in column A that match the current row (greater then 1 there are duplicates). If both these are true the formula returns TRUE. After filter the rows to shows only the TRUEs and delete entire row

| improve this answer | |
  • Sounds good, I didn't know this facility was available in the ribbon. So how can I remove entire rows that are duplicates but keep cells that have values greater than 0 in B, C, D and E. Must be some sort of conditional deletion function somewhere? – adampski Sep 23 '14 at 10:10
  • There isn't really a facility for conditional delete function, but we could create a extra column that you can use as a flag. I will update my answer. – gtwebb Sep 23 '14 at 16:18
1

Select the Cells, Goto to Data tab, click on Remove Duplicates.

http://www.excel-easy.com/examples/remove-duplicates.html

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.