Remove All Identical Rows

[Lasantha]

Dear Team,

Kindly please look in to the attached file. Could you please give me a formula or method to remove all identical raws.

example raw 1 and raw 2 are identical so i need to remove both of them from the file.
example raw 21 to raw 24 are the identical columns. so i need to remove them as well.
example raw 33 to 37 are identical , so have to remove them.

kindly look in to this and help me.
thank you for the time.
Lasantha.

 

[Marc L]

Hi !

Just use the remove duplicates Excel inner feature …​

 

[Lasantha]

Hi Marc,
remove duplicate option is not working for this, bcoz it doesn't remove all raws. it keeps 1 raw. here I have to remove all duplicate raw.

thank you.
Lasantha.

 

[bosco_yip]

Maybe

In B2, copied down :

=IFERROR(AGGREGATE(15,6,A$2:A$4417/(COUNTIF(A$2:A$4417,A$2:A$4417)=1),ROW(A1)),"")

or,

=IFERROR(AGGREGATE(15,6,A$2:A$4417/(FREQUENCY(A$2:A$4417,$A$2:A$4417)=1),ROW(A1)),"")

Regards
Bosco

 

[Lasantha]

Hi Bosco,
please let me know how to use this formula to remove identical raws.

thank you for ur time.
Lasantha.

 

[GraH - Guido]

Hi Marc,
remove duplicate option is not working for this, bcoz it doesn't remove all raws. it keeps 1 raw. here I have to remove all duplicate raw.

thank you.
Lasantha.

Hi Lasantha,
If it keeps 1 row,.... it has removed the duplicates, no? I think Mark L did offer you the easiest of solutions to be honest.
Alternatively try the advanced filter like this...

 

[NARAYANK991]

Hi Lasantha,
If it keeps 1 row,.... it has removed the duplicates, no? I think Mark L did offer you the easiest of solutions to be honest.
Alternatively try the advanced filter like this...
View attachment 51156

Hi ,

I think OP has clearly explained with an example.

row 1 and row 2 are identical so i need to remove both of them from the file.

Narayan

 

[Lasantha]

Yes, I don't need any duplicate lines. I need to remove them all. when I use above methods, it keeps 1 raw.
so can you please give me a method to delete all identical raws.

thank you.

 

[GraH - Guido]

Oh, yes... Read it too quickly (again) My bad.
Then again, solution of Bosco (the formula) and using remove duplicates from this range would work, no?

 

[Lasantha]

How to use that formula to remove identical raws.

 

[NARAYANK991]

Hi ,

Using Bosco's formula returns all those items which have only one occurrence in the entire list.

Copying it down as far as possible will return all the account numbers which have only one occurrence in the original list ; there are 838 such numbers.

See the attached file.

Narayan

 

[Lasantha]

this is awesome... thank you very much all.
thanks for your time.

 

[Lasantha]

Maybe

In B2, copied down :

=IFERROR(AGGREGATE(15,6,A$2:A$4417/(COUNTIF(A$2:A$4417,A$2:A$4417)=1),ROW(A1)),"")

or,

=IFERROR(AGGREGATE(15,6,A$2:A$4417/(FREQUENCY(A$2:A$4417,$A$2:A$4417)=1),ROW(A1)),"")

Regards
Bosco

thank you very much.