To find out the duplicates in the columns

[piyushau]

I have 5 columns and i have to search each values of column B in the entire column E. If the value is found in the column E then this row( column E cell) must be deleted and the row containing the values in column B should be there in output!!!

I know how to find duplicates and deleting if there is one column but not sure about the above one

Please help!!!!

[Pete_UK]

... and the row containing the values in column B should be there in output!!!

I don't know what you mean by this part, but you can put this formula in F2:

=IF(COUNTIF(E:E,B2)>0,"Delete","")

and then copy down. All duplicated rows will be marked with the word delete, so you could apply autofilter to column F to select "delete", highlight all the visible rows, and then click the [delete] icon, and then re-set the filter.

Hope this helps.

Pete

[piyushau]

i tried already with such type of formula that was given in this forum... but my problem is like this :

suppose B2 contains the value 28 and this value is occurring again in cell E 15 and E18 then the rows containg these two cells must be deleted!!!!

and row containg B2 cell must be present !!

Hope i am bit clear now!!!

Regards,
Piyush

[Pete_UK]

No, that's a bit different from what you said before. And if E2 contains a value which is also present in column B, wouldn't that mean that row 2 should be deleted anyway?

Why don't you post a sample workbook and then it might become clearer what it is that you really want.

Pete

[piyushau]

What u said is right .. but i thought i will do it seprately .. Attached is the sheet !!

My original problem was

1) i have to search out all the values of column B in the column E and if value is being found ..i have to delete that row containg cell of column E ( not column B).

2) Again i have to do the vice versa that is to check the value of column E in column B and then if the value is find the row containg the cell of column B needs to be deleted.

3). again i have to check the duplicates in column B and deleting those retaing the first one ... this part i have done in column F.

Please let me know if anything else is required!!!

Regards,
Piyush

[mycon73]

Hi Pyushau,

You have a similar situation like me in that you also have a handful of columns & trying to find duplicates or match data. I'm still trying to get a function that will work with my Match & Determine Frequency post inquiry.

I just found this function: =IF(B3="","",VLOOKUP(B3,$G$2:$H$5,2,0)) in one of the posts here - Data Validation.

I'm in process of trying it. Perhaps, a similar function will also work for you.