count duplicates

[loade]

hello!

in a list of duplicate rows, I want to count the duplicates of each different row, like in the below:
Book3.xls

how can I do this?

thanks!

[zbor]

Here, try this in F7:

=SUMPRODUCT(--($A$1:$A$300=D7),--($B$1:$B$300=E7))

[loade]

I want it to be independent of the amount of rows
also, independent of the length of each row (so it should display below the last row)

[Marcol]

See the formulae in this workbook, drag the table whereever you want.

Note.
D7 is different to D8, D8 can be dragged.

This just adds to what zbor stated.

[WHER]

One more, UDF approach (can be placed anywhere on the worksheet):

[loade]

is it possible to do it by simply click a button in any xls file?
ie, by clicking the button, to display that "report" below the last non-empty row?

[loade]

can you tell me the macro code and how to trigger it?
thanks

[WHER]

It's a User Defined Function (UDF) not a macro, it's not "triggered" but entered like a normal function (e.g. "Sum(A1:A10)").
In the example it's entered in cell H7, then dragged sideways, then down (or vice versa).
The code can be found in the attachment: Alt+F11 will open the Visual Basic Editor, then open "Module 1", there you find the code.

Public Function splt(rng As Range, lng As Long, i As Integer)

Dim arr, str, lng2 As Long, lng3 As Long, dic As Object
Application.Volatile
Set dic = CreateObject("Scripting.Dictionary")
With dic
    For lng3 = 1 To rng.Rows.Count
       str = ""
       str = rng(lng3, 1) & "|" & rng(lng3, 2)
       .Item(str) = .Item(str) + 1
    Next
arr = Array(.Keys, .Items)
  If i = 1 Then
   splt = Split(arr(0)(lng - 1), "|")(0)
  ElseIf i = 2 Then
   splt = Split(arr(0)(lng - 1), "|")(1)
  ElseIf i = 3 Then
    splt = arr(1)(lng - 1)
  End If
End With
End Function

[loade]

thanks but the problem with this is that I need to enter it manually
while a macro is always there, ready to be triggered
cant it be assigned somehow to a macro?

[WHER]

It's probably possible to re-think this as a macro, but as explained above, it only needs to be entered in 1 cell (20 seconds worth of typing), then dragged across (another 5 seconds), hardly a "problem"?

[Marcol]

thanks but the problem with this is that I need to enter it manually
while a macro is always there, ready to be triggered

....

“We do the impossible every day. Miracles take a little longer.”

A little effort goes a long way ...