# Thread: counting Thanks:  4 Post #5279428 (1)Post #5279430 (1)Post #5279483 (1)Post #5279486 (1) Likes:  5 Post #5279428 (1)Post #5279430 (1)Post #5279483 (1)Post #5279344 (1)Post #5279486 (1)

1. ## Re: counting Originally Posted by montecarlo2012 As you can see, at the beginning the post has a little Matrix, what I was trying to request is to count the value of following position for each number. I don't know if make sence,
like how many times 3 is after 2, and so on, but I really apreciate the answers, I am working now trying to find the way, for me is really dificult, but....
List and resulting matrix

 A B C D E F G H 1 1 1 2 3 4 5 2 2 1 0 2 3 0 0 3 3 2 5 0 0 0 0 4 1 3 1 3 0 0 0 5 2 4 0 0 1 0 0 6 3 5 0 0 0 1 0 7 1 8 2 9 3 10 1 11 2 12 1 13 2 14 1 15 3 16 4 17 5

Try this (With the formula sum, Rick's suggestion)

Code:
```Sub counting_n()
Dim r As Range, wMin As Double, wMax As Double, wRow As Long, wCol As Long, i As Long, j As Long

Set r = Range("A1", Range("A" & Rows.Count).End(xlUp))
wMin = WorksheetFunction.Min(r)
wMax = WorksheetFunction.Max(r)
r.Offset(0, 2).Resize(r.Count, wMax + 1).ClearContents

wRow = 2
For i = wMin To wMax
wCol = 4
Cells(wRow, "C").Value = i
For j = wMin To wMax
Cells(1, wCol).Value = j
Cells(wRow, wCol).Value = Evaluate("SUM((" & r.Address & "=" & j & ")*(" & r.Offset(1).Address & "=" & i & "))")
wCol = wCol + 1
Next
wRow = wRow + 1
Next
MsgBox "Done"
End Sub```  Reply With Quote

2. ## Re: counting

Another way without using loop

Code:
```Sub counting_2()
Dim r As Range, wMin As Double, wMax As Double

Set r = Range("A1", Range("A" & Rows.Count).End(xlUp))
wMin = WorksheetFunction.Min(r)
wMax = WorksheetFunction.Max(r)
r.Offset(0, 2).Resize(r.Count, wMax + 1).ClearContents
Range("D1, C2") = wMin
Range("D1").AutoFill Destination:=Range("D1").Resize(, wMax), Type:=xlFillSeries
Range("C2").AutoFill Destination:=Range("C2").Resize(wMax), Type:=xlFillSeries

With Range("D2").Resize(wMax, wMax)
.Formula = "=SUMPRODUCT((R1C1:R" & r.Count & "C1=R1C)*(R2C1:R" & r.Count + 1 & "C1=RC3))"
.Value = .Value
End With
MsgBox "Done"
End Sub```  Reply With Quote

3. ## Re: counting

Thank you DanteAmor, You are really Good, great job.  Reply With Quote

4. ## Re: counting  Reply With Quote

## User Tag List

cell, code, count, end, vba & excel 2010 