counting

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,208
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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,208
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
 

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
536
Office Version
2010
Platform
Windows
Thank you DanteAmor, You are really Good, great job.
 

DanteAmor

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

Watch MrExcel Video

Forum statistics

Threads
1,102,706
Messages
5,488,414
Members
407,638
Latest member
brandynl

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top