Get frequency of elements in Range

[always_confused]

Hello,

I have a spreadsheet with error codes (column A) and timestamps (column B) . Every time there is an error, it is recorded and time stamped. I would like to write a macro that writes in another column (column D) the frequency of each error. I have filled column C with the distinct error codes possible, and I would like to go through column A and count the occurrence of each code in column C.

However, there are about 1 000 error codes possible and about 75 000 timestamped values, so I would like to avoid going through all 75 000 values for every one of the 1 000 codes possible. I there a better way to do this?

Thanks

 

[Akuini]

Hi, @always_confused

so I would like to avoid going through all 75 000 values for every one of the 1 000 codes possible

I have a code to do that.
I assumed your data is sorted by col B.
Could you sort your data by col A? you can sort it back by col B after running the code.

Sub a1167835a()
'https://www.mrexcel.com/board/threads/get-frequency-of-elements-in-range.1167835/
Dim i As Long, j As Long, k As Long
Dim va, vb

va = Range("A1", Cells(Rows.Count, "A").End(xlUp))
ReDim vb(1 To UBound(va, 1), 1 To 2)

For i = 2 To UBound(va, 1)
    j = i
    Do
        i = i + 1
        If i > UBound(va, 1) Then Exit Do
    Loop While va(i, 1) = va(i - 1, 1)
    
        i = i - 1
        k = k + 1
        vb(k, 1) = va(j, 1)
        vb(k, 2) = i - j + 1

Next

'put result in col D:E
Range("D2:E10000").ClearContents
Range("D2").Resize(k, 2) = vb

End Sub

Example:

Book1
	A	B	C	D	E
1	code			code	frequency
2	G			G	9
3	G			K	4
4	G			T	4
5	G
6	G
7	G
8	G
9	G
10	G
11	K
12	K
13	K
14	K
15	T
16	T
17	T
18	T
Sheet5

It doesn't need the 1000 error codes possible, it just count the frequency of code in col A.
I tested it on 100K rows of data, it took a split second to finish.

 

[always_confused]

@Akuini That works great, thank you so much. Do you know how I could sort both columns to have the most frequent error and its frequency at the top?

 

[always_confused]

@Akuini That works great, thank you so much. Do you know how I could sort both columns to have the most frequent error and its frequency at the top?

Oh nevermind I figured it out

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[always_confused]

You're welcome, glad to help & thanks for the feedback.

Hello again,

Turns out I'm having an issue with this bit of code. I have adjusted it a bit to fit my sheet and when I fill the column with the frequencies, there is an extra value that doesn't belong to any code. For 1 000 codes, I get 1 001 frequencies. I'm not sure what this is or where it's coming from.

This is the codes as I have modified it

Sub a1167835a()
Dim i As Long, j As Long, k As Long
Dim va, vb

va = Range("F1", Cells(Rows.Count, "F").End(xlUp))
ReDim vb(1 To UBound(va, 1), 1 To 2)

For i = 2 To UBound(va, 1)
    j = i
    Do
        i = i + 1
        If i > UBound(va, 1) Then Exit Do
    Loop While va(i, 1) = va(i - 1, 1)
    
        i = i - 1
        k = k + 1
        vb(k, 1) = va(j, 1)
        vb(k, 2) = i - j + 1

Next
Range("P2:Q10000").ClearContents
Range("P2").Resize(k, 2) = vb

End Sub

I end up with an extra 1500 value at the end of the list of frequencies.

 

[always_confused]

I've tacked

lastrow = Range("Q" & Rows.Count).End(xlUp).Row
Range("Q" & lastrow).Clear

Onto the end, so now the output is ok, but I'd prefer not to assume it's wrong and correct and instead know what's going wrong and prevent it....

 

[Akuini]

when I fill the column with the frequencies, there is an extra value that doesn't belong to any code. For 1 000 codes, I get 1 001 frequencies. I'm not sure what this is or where it's coming from.

I end up with an extra 1500 value at the end of the list of frequencies.

Did you sort data by col A before running the code?
If yes then it means that there are more than 1000 unique code in col A.
Is it possible you have trailing space in your data?
I couldn't test it without your original data.

 

[always_confused]

Did you sort data by col A before running the code?
If yes then it means that there are more than 1000 unique code in col A.
Is it possible you have trailing space in your data?
I couldn't test it without your original data.

Oh yes thanks, hadn't realized but an issue elsewhere had added a bunch of extra rows. Fixed it and now it works again. Thank you ?