Count duplicates and return that value in a cell

[cboz]

I am trying to write a macro that will count the number of times an item in ColA repeats,return that value in ColB then delete all of the rows except for the first row for that item.
ColA ColB
71699 18961-1
71699 18962-1
71699 18963-1
71699 18964-1
71699 18965
71699 18966

Results should look like this
71699 6

I do not want to use a pivot table because if the item repeats less than 6 times I move the value in ColB
horizontally across the row so that it looks something like this:
71669 18961-1 18962-1 18963-1 etc.

I can move the values horizontally using "offset"
but I don't know how to do the counting part.

I remember something from a VB class I took about indexes but it's a bit vague. Much like myself.

Please help.

 

[SkipVought]

Hi,

1. Create a Pivot table with COUNT of ColA
2. Use the Pivot Table results as a lookup table for the occurrence count
3. Use this kind of formula to determine which rows to keep...

=IF(AND(A1=A2,K2>=6),0,1)

where col is the K is the lookup counts

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[cboz]

I thought of something similar after I made the request.
Thank you for your quick response.

I was trying to make it too complicated.