Count number of occurence of value with condition

[ibmy]

Hi,

Sample of data:

1.11.xlsb
	B	C	D	E	F
1	A	B		A	Expected Result
2	1.3	1.2		1.3	4
3	1.3	11.9		11.9	2
4	1.2	1.2
5	11.9	1.3
6	1.2	1.2
7	11.9	11.9
8	11.9	1.3
9	1.3	65.7
10	11.9	11.9
11	11.9	1.3
12	11.9	7.8
13	1.2	11.9
14	1.2	11.9
15	1.2	11
16	65.7	7.8
17	65.7	1.3
18	8.9	11.9
19	6.6	1.3
20	88	1.2
21	4	1.3
Sheet3

Before counting number of occurence value in Column E, these 2 steps must follow :

Step 1) Locate value Column E in Column B, it must exist last (if from top to down) or exist first (from down to top)
Example: E1 value is "1.3", it occur in B2, B3, B9.
B9 is choose because it exist last in column B (from top to down) or exist first in column B (from down to top)

Step 2) Then, count number of occurence the value in Column C, starting from row of it occur in column B (Step 1)
Example: Value "1.3" happen last in B9 so count the number of occurence in Column C starting form C9 to last row in Column C

Properties of Real Data:
1. Row data in Column B and C ranges 100k-450k , for now "countif" works fine but my problem how to do Step 1.
2. Number mostly are in decimal point (3-5 decimal points), Column B, C, E

p/s: sorry for bad English

 

[AlphaFrog]

This will match the E2 value and find the last occurrence in column B, and then count all occurrences in column C after the last match from column B

=COUNTIF(INDIRECT("C" & LOOKUP(2,1/(B2:B500000=E2),ROW(B2:B500000)) & ":C500000"), E2)

 

[pitchoute]

=TEXTJOIN(", ", TRUE, IF(ISNUMBER(SEARCH(Sheet2!$A$2:$A$100, A2)), Sheet2!$B$2:$B$100, ""))

 

[ibmy]

Thanks @pitchoute for the reply.

Thanks @AlphaFrog , the formula produce the result I was expected.
I just add "$" at

B$2:B$50000=E2

and

B$2:B$50000

Is there any way that the time to produce the result can be shorten. Currently :
For 100k row Column B,C and 100 row Column E, it take about 1 minute.
For 450k row Column B,C and 1000 row Column E, it take about 5 minute.

I was thinking, if firstly, finding the last row of value (Column F) then apply "COUNTIF" (Column G), does the time can be shorten?
So there will be 2 formula in Column F and Column G, like this :

1.11.xlsb
	B	C	D	E	F	G
1	A	B		A	Last Row	Expected Result
2	1.3	1.2		1.3	9	4
3	1.3	11.9		11.9	12	2
4	1.2	1.2
5	11.9	1.3
6	1.2	1.2
7	11.9	11.9
8	11.9	1.3
9	1.3	65.7
10	11.9	11.9
11	11.9	1.3
12	11.9	7.8
13	1.2	11.9
14	1.2	11.9
15	1.2	11
16	65.7	7.8
17	65.7	1.3
18	8.9	2
19	6.6	1.3
20	88	1.2
21	4	1.3
Sheet4

 

[Alex Blakenburg]

Your previous posts indicate you might be open to a VBA option. If so give this a try.
Note:
• This will count values that never appear in Column B but have values in Column C. Let me know if you want to handle those differently.
• It assumes no duplicates in column E

Sub CountFromPrevColLast()

    Dim sht As Worksheet
    Dim rngCnt As Range, rngData As Range
    Dim arrCnt As Variant, arrData As Variant
    Dim rowLastCnt As Long, rowLastData As Long
    Dim dictCnt As Object, dictKey As String
    Dim rowCnt    
    Dim i As Long
    
    Set sht = ActiveSheet
    
    With sht
        rowLastCnt = .Range("E" & Rows.Count).End(xlUp).Row
        Set rngCnt = .Range("E2:E" & rowLastCnt)
        arrCnt = rngCnt.Value
        
        rowLastData = .Range("B" & Rows.Count).End(xlUp).Row
        Set rngData = .Range("B1:C" & rowLastData)
        arrData = rngData.Value
        ' Expand array by 2 columns
        ReDim Preserve arrCnt(1 To UBound(arrCnt), 1 To 3)
    End With

    Set dictCnt = CreateObject("Scripting.dictionary")
    
    ' Load details range into Dictionary
    For i = 1 To UBound(arrCnt)
        dictKey = arrCnt(i, 1)
        dictCnt(dictKey) = i
    Next i

    ' Count occurences in C from last occurence in column B
    For i = UBound(arrData) To 1 Step -1
        ' Check if value in list and have not yet registered last occurence in column B
        dictKey = arrData(i, 2)
        If dictCnt.exists(dictKey) Then
            rowCnt = dictCnt(dictKey)
            If arrCnt(rowCnt, 3) = "" Then
                arrCnt(rowCnt, 2) = arrCnt(rowCnt, 2) + 1
            End If
        End If
        
        ' If last occurence in column B record row number
        dictKey = arrData(i, 1)
        If dictCnt.exists(dictKey) Then
            rowCnt = dictCnt(dictKey)
            If arrCnt(rowCnt, 3) = "" Then
                arrCnt(rowCnt, 3) = i
            End If
        End If
    Next i
    
    For i = 1 To UBound(arrCnt)
        If arrCnt(i, 2) = "" And arrCnt(i, 3) <> "" Then arrCnt(i, 2) = 0
    Next i

    rngCnt.Offset(, 1).Value = Application.Index(arrCnt, , 2)
End Sub

 

[ibmy]

Thanks @Alex Blakenburg for the code, Im really appreciate your spending time on helping me.
The code works really fast, it took less than 5 seconds to produce the result.

• This will count values that never appear in Column B but have values in Column C.

I do not get it, sorry since my English not so good but I will try explain about the data.
This is what I understand by above sentence : Let say value "100" not appear in Column B but value "100" could be appear in Column C. If that what it means by above sentence then yes it is.
There are value not appear in Column B but appear in Column C and vice versa.
Also, there are value appear in Column B but not appear in Column C and vice versa. (this will produce result "0" or result "0" could be another reason after last row, the value not exist in Column C)

• It assumes no duplicates in column E

Yes, there no duplicates in column E
Actually, all value column E are distinct value from Column B. I copy all value from Column B to Column E then "remove duplicate". So the number row of Column E always lower than both Column B and Column C. Column B and Column C always have same total row.
From sample, it should be look like this :

1.11.xlsb
	B	C	D	E	F
1	A	B		A	Expected Result
2	1.3	1.2		1.3	4
3	1.3	11.9		1.2	2
4	1.2	1.2		11.9
5	11.9	1.3		65.7
6	1.2	1.2		8.9
7	11.9	11.9		6.6
8	11.9	1.3		88
9	1.3	65.7		4
10	11.9	11.9
11	11.9	1.3
12	11.9	7.8
13	1.2	11.9
14	1.2	11.9
15	1.2	11
16	65.7	7.8
17	65.7	1.3
18	8.9	2
19	6.6	1.3
20	88	1.2
21	4	1.3
mrexcel

Finally, by using same code, change a few lines, then I would like also run for value in Column C in Column K.
Value in Column K are distinct value from Column C same as I did for value in Column B in Column E
Hopefully I can get same code but 2 code (change a few lines) , instead of 1 code that produce all the result because I might change placement of value in different column later on if there is new variable that interest me.

1.11.xlsb
	B	C	D	E	F	G	H	I	J	K	L
1	A	B		A	Result					B	Result
2	1.3	1.2		1.3	4					1.2	0
3	1.3	11.9		11.9	2					11.9
4	1.2	1.2		1.2						1.3
5	11.9	1.3		65.7						65.7
6	1.2	1.2		8.9						7.8
7	11.9	11.9		6.6						11
8	11.9	1.3		88						2
9	1.3	65.7		4
10	11.9	11.9
11	11.9	1.3
12	11.9	7.8
13	1.2	11.9
14	1.2	11.9
15	1.2	11
16	65.7	7.8
17	65.7	1.3
18	8.9	2
19	6.6	1.3
20	88	1.2
21	4	1.3
mrexcel 2

Cell Formulas
Range	Formula
F2	F2	=COUNTIF(INDIRECT("C" & LOOKUP(2,1/(B$2:B$500000=E2),ROW(B$2:B$500000)) & ":C500000"), E2)
L2	L2	=COUNTIF(INDIRECT("B" & LOOKUP(2,1/(C$2:C$500000=K2),ROW(C$2:C$500000)) & ":B500000"), K2)

This is real data if you would like to take a look : Real Data

 

[Alex Blakenburg]

I have logged out for the night but so I know for tomorrow, is the logic of column C & B reversed for column K ?
ie Looking for last row in column C and counting matches in Column B that are below that last row ?

 

[ibmy]

@Alex Blakenburg , Exactly, column C & B reversed for column K.

Column E : Look for last row in Column B then count number of occurence in Column C
Column K : Look for last row in Column C then count number of occurence in Column B

 

[Alex Blakenburg]

Give this a try for column K. Only 4 lines are changed (they all use the word Swapped in the comments)
Ideally we would change it to a called sub and feed it the parameters that change.

Sub CountFromPrevColLast_SumB()

    Dim sht As Worksheet
    Dim rngCnt As Range, rngData As Range
    Dim arrCnt As Variant, arrData As Variant
    Dim rowLastCnt As Long, rowLastData As Long
    Dim rowCnt
    
    Dim i As Long
    
    Set sht = ActiveSheet
    
    With sht
        rowLastCnt = .Range("K" & Rows.Count).End(xlUp).Row     ' Swapped from column E to Column K - Filter list
        Set rngCnt = .Range("K2:K" & rowLastCnt)                ' Swappedfrom column E to Column K - Filter list
        arrCnt = rngCnt.Value
        
        rowLastData = .Range("B" & Rows.Count).End(xlUp).Row
        Set rngData = .Range("B1:C" & rowLastData)
        arrData = rngData.Value
        ' Expand array by 2 columns
        ReDim Preserve arrCnt(1 To UBound(arrCnt), 1 To 3)
    End With

    Dim dictCnt As Object, dictKey As String

    Set dictCnt = CreateObject("Scripting.dictionary")

    ' Load details range into Dictionary
    For i = 1 To UBound(arrCnt)
        dictKey = arrCnt(i, 1)
        'If Not dictCnt.exists(dictKey) Then
            dictCnt(dictKey) = i
        'End If
    Next i

    ' Count occurences in C from last occurence in column B
    For i = UBound(arrData) To 1 Step -1
        ' Check if value in list and have not yet registered last occurence in column C
        ' Column to count
        dictKey = arrData(i, 1)                                     ' Swapped from 2 to 1 being column C from Column B - column to count
        If dictCnt.exists(dictKey) Then
            rowCnt = dictCnt(dictKey)
            If arrCnt(rowCnt, 3) = "" Then
                arrCnt(rowCnt, 2) = arrCnt(rowCnt, 2) + 1
            End If
        End If
        
        ' If last occurence in column C record row number
        ' Criteria Column
        dictKey = arrData(i, 2)                                     ' Swapped from 1 to 2 being column B from Column C - criteria column
        If dictCnt.exists(dictKey) Then
            rowCnt = dictCnt(dictKey)
            If arrCnt(rowCnt, 3) = "" Then
                arrCnt(rowCnt, 3) = i
            End If
        End If
    Next i
    
    For i = 1 To UBound(arrCnt)
        If arrCnt(i, 2) = "" And arrCnt(i, 3) <> "" Then arrCnt(i, 2) = 0
    Next i

    rngCnt.Offset(, 1).Value = Application.Index(arrCnt, , 2)
End Sub

 

[ibmy]

Thanks @Alex Blakenburg , for well written code.
It is working as I hope so.