How to find dupelicate coordinates values store in 2 different column?

[VBA_Cancer]

Hi, right now I have a spreadsheet like this:

A | ​B
1 | 2
2 | 2
1 | 5
2 | 2
1 | 2
3 | 4

I want to return the number of duplicate coordinates for each coordinates. So an EXCEL VBA function that loops through each coordinates and msgbox how many times it's duplicated. Assume the number of rows is 6.

Ex. for (1, 2) it would return 2.

Is it possible to use countif for this? Thanks

 

[Fluff]

You can do that with Countifs
=COUNTIFS(A$1:A$6,A1,B$1:B$6,B1)

 

[Joe4]

You don't really need VBA for this. You can show the number of times each set appears in another column in the same row.
So, let's say that you have a header/title row in row 1, and your data begins on row 2.
Then, in cell C2 you can enter this formula and copy down:

=COUNTIFS($A:$A,$A2,$B:$B,$B2)

 

[vandango05]

You could use countif. In column C string together the values of A & B =concatenate(A1,b1) then in column D you can do a countif =COUNTIF(C:C,C1)

 

[Joe4]

You could use countif. In column C string together the values of A & B =concatenate(A1,b1) then in column D you can do a countif =COUNTIF(C:C,C1)

I would not recommend that. That could lead to incorrect results.
For example, if one row had 12 in column A, and 3 in column B, and another row had 1 in column A and 23 in column B, that methodology would incorrectly identify them as duplicates.
Use COUNTIFS, which allows for multiple conditions.

 

[Joe4]

You could use countif. In column C string together the values of A & B =concatenate(A1,b1) then in column D you can do a countif =COUNTIF(C:C,C1)

As a follow-up to my previous response, if you had wanted to use that method (maybe because you are on an old version of Excel that does not have COUNTIFS), you could make a slight modification that would allow it to work.
Use something like:

[COLOR=#333333]=concatenate(A1,[/COLOR][COLOR=#ff0000]","[/COLOR][COLOR=#333333],B1)[/COLOR]

 

[VBA_Cancer]

thank you for your response, but I need it in VBA as I am writing a program.

 

[Rick Rothstein]

thank you for your response, but I need it in VBA as I am writing a program.

How about this UDF (user defined function)...

[table="width: 500"]
[tr]
	[td]Function CoordCount(Rng As Range, Acoord As String, Bcoord As String)
  CoordCount = UBound(Split(Join(Evaluate("TRANSPOSE(" & Rng.Columns(1).Address & "&""|""&" & Rng.Columns(2).Address & ")"), "@"), Acoord & "|" & Bcoord))
End Function[/td]
[/tr]
[/table]

This function takes three arguments... the range containing all of the coordinates, the coordinate from Column A and the coordinate from Column B. So, for the example you gave, you could use this formula...

=CoordCount($A$1:$B$6,1,2)

or...

=CoordCount($A$1:$B$6,A1,B1)