Count duplicate rows then delete duplicates

[dougf]

I have two columns of data, and I need to create a third column to count the number of times that same line appears in the document (and then remove all but the first copy of that line).

my data looks as follows (and it is sorted so all duplicate rows appear directly next to each other):

Adam1998 | Jan
Adam1998 | John
Adam1998 | John
Adam1998 | Paul
Adam1998 | Peter
Adam1998 | Peter
Adam1998 | Peter
Adam1999 |John
Adam1999 | Paul


I need this to look as follows:
Adam1998 | Jan | 1
Adam1998 | John | 2
Adam1998 | Paul | 1
Adam1998 | Peter | 3
Adam1999 |John | 1
Adam1999 | Paul | 1

Thank you

[rylo]

Hi

See how this goes.

Please Login or Register  to view this content.

rylo

[dougf]

This worked perfectly. Thank you for your help!

At first I accidentally selected the wrong sheet - which is why the data did not format correctly.

[Matt Cannister]

Thanks for this post. Great to see someone's response from 2009 helping me out today.

I was able to adapt this code for a dataset with more columns (7) by increasing the array size and range formulas, but now I have a similar situation with a dataset continues 50+ columns of data. Do you know of a way to reproduce this same output WITHOUT having to manually type out the coding for each new column (i.e. "=sumproduct(--($A$2:$A$" & lastrow & "=A2),--($B$2:$B$" & lastrow & "=b2))"?