Count and remove duplicate rows

Hi Somebody,

I need your help with a formula or a vba code.


This is what I am trying to do.
I have a spreadsheet with some records with many columns filled. I am
trying to find duplicate rows and count them and enter the count
in a new column.

So the code should check that how many similar rows there is and count them
And then remove dublicates.

This is the example of the sheet


Title1 Title2 Title3 Title4 Title5
1234 5678 91011 1213 1415
1234 5678 91011 1213 1415
1234 5678 91011 1213 1415
3333 4444 5555 6666 7777
3333 4444 5555 6666 7777
1111 2222 3333 4444 5555

and the result.

Title1 Title2 Title3 Title4 Title5 Result
1234 5678 91011 1213 1415 3
3333 4444 5555 6666 7777 2
1111 2222 3333 4444 5555 1


I hope this message is clear
Thanks
Efu

I've done this before using 2 methods, 1 is via formula and another via VBA code. There might be other neater ways but I'll share mine using the formula.

I would create a 6th column F to concatenate the first 5 columns. The formula for each cell in this column would be "=A2&B2&C2&D2&E2". Then in the 7th column G, do a count. The formula is "=COUNTIF(F:F,F2)". Drag down to fill all rows with these formula. Then set autofilter on and do a custom filter values >1. Delete all these duplicate rows.

Originally Posted by mudraker

Chips site has info dealing with duplictes.

It will not tell you how many of each there are but will highlight the duplicates

http://www.cpearson.com/excel/Duplicates.aspx

I read through but somehow cannot get the formula to work for "Highlight Second And Subsequent Duplicates Only". Anybody tried and succeed?

This does not help me, I need to do this via VBA code so I can use macros.