Count unique values across 3 columns

Suppose I have the following:

Col A Col B Col C Result
1/7/2015 M 1 0
1/7/2015 M 2 0
2/7/2015 A 1 1
2/7/2015 A 1 1
3/7/2015 C 2 0


I wish to count the number of occurances where col A and Col B and Col C are the same (this would appear in the result column.)

I have used countifs(A$2:A$100,A2,B$2:B100,B2,C$2:C100,C2) as array (ctrl+shift+enter) but I have 100,000+ rows and the copy and calculation is extremely slow.

Does anyone know of a faster way?

Thank you so much

I did not know it could be entered as a regular formula.

Is there a faster way that the same result can be achieved? When I copy the forumula down 100,000+ rows, it takes a VERY long time. I have searched everywhere but cannot seem to come up with a solution.

Well I have 100,000+ rows and approx. 50+ columns.

I am looking to identify the like values (those that return 2 above in the formula) and delete the other rows.

Is there a faster and/or better way this can be achieved?

I could do a pivot table, but then I need to go back and delete the rows I don't need?

Surely the result can be achieved quicker in another way or with a different formula?

I have sometimes found that, if I am dealing with historic data - where the answers to formulas will no longer change - it may help to "value" those formulas down a feww '000 rows.

Unfortunately the values will always change depending on parameters so I cannot "value" down the formulas.

Not sure what you mean by that. PT's are seperate tables that will interogate a data base for you, and can be substantially faster than formulas

Well you mentioned this earlier

Perhaps a Pivot Table, or a table with formulas showing just the unique values would suite you better?

But then I would not be able to delete the rows I don't need would I? The pivot table would show me the unqiue values and where they appear, but that would not achieve the goal of deleting irrelevant data??

ok thanks no problem. I thought I had,

I am looking to identify the like values (those that return 2 above in the formula) and delete the other rows.

I believe it's time consuming as the calc is taking so long but could be finding duplicates as well.

I have the data sorted by col A then by col B then by col C, so when A and B are the same, the countifs looks for col C to be the same also. I want to keep those rows and delete the others. But the countifs formula down the rows (which will always change, sometimes I could have less) takes a very long time.

So just looking for some alternative options from others to achieve this result??

I must have missed that part, sorry

That's ok

For the deleting, have you considered using filters?

Yes I have considered this, however I haven't been able to get to that yet as the formula takes so long

Can you post a SMALL sample file and show us what results you expect?

Sure, I have attached it.

You can see in the example, I would insert the countifs formula in col J, then I would copy it down.

I would then use VBA to delete the rows I don't need, in this case I would only keep rows 20, 21 and 29, 30.

This may be "faster"...

Enter this formula in J17:

=A17&" "&B17&" "&C17

Enter this formula in K17:

=COUNTIF(J$17:J$35,J17)

Select J17:K17 and copy down as needed.

I guess you want to delete the rows where column K=1.

Thanks but unfortunately it was pretty much the same as the original method. Still takes at least 2 mins to calculate

Well, I'm out of suggestions.

ok thanks anyway. I am looking for something much quicker but if not then so be it.

Maybe post this in the Excel Programming / VBA / Macros sub-forum. Someone might be able to come up with a macro that'll do this.

Good luck!

ok thanks so much! Is there a way I can move this post or do I just submit the question again in the other sub-forum?