I have a column filled with urls in the chronological order in which they were visited. I want to count instances of each duplicate and list the count alongside the url in a new column to the right, so I can see which pages got the most hits.

I've been able to do this in the past with KuTools for excel, but my free trial ran out. KuTools would count each duplicate and then display the count next to the first instance of each duplicate while deleting the others. It was great and I wish my office would just pay for it.

I want to know if there is a way to do this with formulas or VBA. I've tried using countif and countifs but the problem is that I have to apply the formula for every URL, and there are thousands of them. What am I missing? What can I do differently? Thank you very much for the help!

  • Hey I figured it out! I just used this formula =IF(COUNTIF(A:A,A2)>1,COUNTIF(A$2:A2,A2),"") to B2 and then copied it to on down the column, then I filtered out dupes to get my list. I'll leave the question up in case anyone has the same question. – Nilco Mar 26 '19 at 17:35
  • 3
    You can answer your own question and mark the answer as correct, that way, others will know it has been answered – cybernetic.nomad Mar 26 '19 at 17:36

Hey I figured it out!

The short story is that I copied this formula:


in B2 and then clicked and dragged to copy it on down the column, then I filtered out duplicates to get my list.

Specifically, I copied the formula into cell B2, then adjusted the range and operator so I would still include pages with only one hit.

It looked like this:


Then I clicked and dragged down the entire list to apply the formula to column B.

To clean up the list, I cut column B and did a "paste special" to copy the results as values.

Then I could "remove duplicates" without messing up the formula. This was just a personal touch because I'd rather remove duplicates than filter them out.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.