Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: counting

  1. #11
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,230
    Post Thanks / Like
    Mentioned
    70 Post(s)
    Tagged
    14 Thread(s)

    Default Re: counting

    Quote Originally Posted by montecarlo2012 View Post
    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

     ABCDEFGH
    11  12345
    22 102300
    33 250000
    41 313000
    52 400100
    63 500010
    71       
    82       
    93       
    101       
    112       
    121       
    132       
    141       
    153       
    164       
    175       



    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
    Regards Dante Amor

  2. #12
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,230
    Post Thanks / Like
    Mentioned
    70 Post(s)
    Tagged
    14 Thread(s)

    Default 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
    Regards Dante Amor

  3. #13
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: counting

    Thank you DanteAmor, You are really Good, great job.

  4. #14
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,230
    Post Thanks / Like
    Mentioned
    70 Post(s)
    Tagged
    14 Thread(s)

    Default Re: counting

    I'm glad to help you. I appreciate your kind comments.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •