counting

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,739
Office Version
2007
Platform
Windows
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

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:38.02px;" /><col style="width:35.17px;" /><col style="width:24.71px;" /><col style="width:24.71px;" /><col style="width:24.71px;" /><col style="width:24.71px;" /><col style="width:24.71px;" /><col style="width:24.71px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; text-align:right; ">1</td><td > </td><td style="background-color:#c2d69a; "> </td><td style="background-color:#8db4e3; text-align:right; ">1</td><td style="background-color:#8db4e3; text-align:right; ">2</td><td style="background-color:#8db4e3; text-align:right; ">3</td><td style="background-color:#8db4e3; text-align:right; ">4</td><td style="background-color:#8db4e3; text-align:right; ">5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ffff00; text-align:right; ">2</td><td > </td><td style="background-color:#8db4e3; text-align:right; ">1</td><td style="background-color:#8db4e3; text-align:right; ">0</td><td style="background-color:#8db4e3; text-align:right; ">2</td><td style="background-color:#8db4e3; text-align:right; ">3</td><td style="background-color:#8db4e3; text-align:right; ">0</td><td style="background-color:#8db4e3; text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#ffff00; text-align:right; ">3</td><td > </td><td style="background-color:#8db4e3; text-align:right; ">2</td><td style="background-color:#8db4e3; text-align:right; ">5</td><td style="background-color:#8db4e3; text-align:right; ">0</td><td style="background-color:#8db4e3; text-align:right; ">0</td><td style="background-color:#8db4e3; text-align:right; ">0</td><td style="background-color:#8db4e3; text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#ffff00; text-align:right; ">1</td><td > </td><td style="background-color:#8db4e3; text-align:right; ">3</td><td style="background-color:#8db4e3; text-align:right; ">1</td><td style="background-color:#8db4e3; text-align:right; ">3</td><td style="background-color:#8db4e3; text-align:right; ">0</td><td style="background-color:#8db4e3; text-align:right; ">0</td><td style="background-color:#8db4e3; text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#ffff00; text-align:right; ">2</td><td > </td><td style="background-color:#8db4e3; text-align:right; ">4</td><td style="background-color:#8db4e3; text-align:right; ">0</td><td style="background-color:#8db4e3; text-align:right; ">0</td><td style="background-color:#8db4e3; text-align:right; ">1</td><td style="background-color:#8db4e3; text-align:right; ">0</td><td style="background-color:#8db4e3; text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#ffff00; text-align:right; ">3</td><td > </td><td style="background-color:#8db4e3; text-align:right; ">5</td><td style="background-color:#8db4e3; text-align:right; ">0</td><td style="background-color:#8db4e3; text-align:right; ">0</td><td style="background-color:#8db4e3; text-align:right; ">0</td><td style="background-color:#8db4e3; text-align:right; ">1</td><td style="background-color:#8db4e3; text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#ffff00; text-align:right; ">1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#ffff00; text-align:right; ">2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#ffff00; text-align:right; ">3</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#ffff00; text-align:right; ">1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="background-color:#ffff00; text-align:right; ">2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="background-color:#ffff00; text-align:right; ">1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="background-color:#ffff00; text-align:right; ">2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="background-color:#ffff00; text-align:right; ">1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="background-color:#ffff00; text-align:right; ">3</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="background-color:#ffff00; text-align:right; ">4</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="background-color:#ffff00; text-align:right; ">5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table>


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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,739
Office Version
2007
Platform
Windows
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,739
Office Version
2007
Platform
Windows
I'm glad to help you. I appreciate your kind comments.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,249
Messages
5,467,529
Members
406,544
Latest member
semoredhawk

This Week's Hot Topics

Top