repetitiveness of numbers with the same ending

[DANNYOCEAN]

hello guys!
how are you?
I'd like to know to calculate the frequency/repetition of all possible numbers with the same ending (doing this for all endings from 0 to 1) for each line
for example:
line a:65 67 10 20 51 55 68 35 69 75 79 80 84 85 90 95 30 40 98
frequency of ending 0: 7
frequency of ending 1: 1
frequency of ending 2: 0
frequency of ending 3: 0
frequency of ending 4: 1
frequency of ending 5: 6
frequency of ending 6: 0
frequency of ending 7: 0
frequency of ending 8: 2
frequency of ending 9: 2

line b: 05 12 94 77 56 62 74 97 55 10 23 85 60 26 68 99

frequency of ending 0: 2
frequency of ending 1: 0
frequency of ending 2: 2
frequency of ending 3: 0
frequency of ending 4: 2
frequency of ending 5: 3
frequency of ending 6: 2
frequency of ending 7: 2
frequency of ending 8: 1
frequency of ending 9: 1
and so on... for line c , line d, line e, ... .
I´d like to this, to calculate the frequency/repetition for each line for a huge number list in a spreadsheet like excel.

The numbers in each line may not be happened in sequence order as present above, may appear in different order, numbers order is not relevant. (05, 12, 94, 77, 56, 62, 74, 97, 55, 10, 23, 85, 60, 26 ,68, 99 ) is the same set as (99,68,60,26,23 ,85,10,55,97,74 ,62,56,77,94,12 ,05).

I was wondering that's possible to make this in visual basic/ excel but I don´t know how to start.
My visual basic is 6.3

Can someone give a hand??

I'm looking forward to receiving good news.

Thanks in advance

[Guido Geurs]

This can be done in Excel with VBA.
I have a question: how are the numbers presented: I mean: is it in a textfile ? and if so, what are the delimiters (space, comma,...) or is it in an Excel file?

Is it possible to attach an limited example in BYTES so we can work directly on a good example (with the different possibilities of lines = is there a difference in length ?)?

The method will be:
- If it's in a TXT file, read the lines in an array and split hem in a second array.
- Find the last digit in each number, count them and place the result in an array.
- Dump the last array in a sheet.

I will be glad to help You

[DANNYOCEAN]

I was wondering that's possible to make this in visual basic/ excel but I don´t know how to start.
My visual basic is 6.3
I´d like to this, to calculate the frequency/repetition for each line for a huge number list in a spreadsheet like excel. See the attachment as example for a extremely large number list

Can you give some help??
Thanks in advance

[Guido Geurs]

Run the macro "FrequentyLastN umber" (see attachment).
It will place the columns F-0 to F-9 and the frequenties of the last digit in each datacell.

[Guido Geurs]

attached is a tidy-up version.

[Rodney Roe]

ggeu

just for curiosity i tried out the program you created but it only figures out the right side and doesn't account for single digits. Try adding this to your code.

If ARRAYDATA(ARRAYDATAidx, ARRAYDATACOLidx) < 10 Then
  Select Case Right(ARRAYDATA(ARRAYDATAidx, ARRAYDATACOLidx), 1)
  Case 0: .FRQ0 = .FRQ0 + 1
  Case 1: .FRQ1 = .FRQ1 + 1
  Case 2: .FRQ2 = .FRQ2 + 1
  Case 3: .FRQ3 = .FRQ3 + 1
  Case 4: .FRQ4 = .FRQ4 + 1
  Case 5: .FRQ5 = .FRQ5 + 1
  Case 6: .FRQ6 = .FRQ6 + 1
  Case 7: .FRQ7 = .FRQ7 + 1
  Case 8: .FRQ8 = .FRQ8 + 1
  Case 9: .FRQ9 = .FRQ9 + 1
  End Select
Else
  Select Case Left(ARRAYDATA(ARRAYDATAidx, ARRAYDATACOLidx), 1)
  Case 0: .FRQ0 = .FRQ0 + 1
  Case 1: .FRQ1 = .FRQ1 + 1
  Case 2: .FRQ2 = .FRQ2 + 1
  Case 3: .FRQ3 = .FRQ3 + 1
  Case 4: .FRQ4 = .FRQ4 + 1
  Case 5: .FRQ5 = .FRQ5 + 1
  Case 6: .FRQ6 = .FRQ6 + 1
  Case 7: .FRQ7 = .FRQ7 + 1
  Case 8: .FRQ8 = .FRQ8 + 1
  Case 9: .FRQ9 = .FRQ9 + 1
  End Select
  Select Case Right(ARRAYDATA(ARRAYDATAidx, ARRAYDATACOLidx), 1)
  Case 0: .FRQ0 = .FRQ0 + 1
  Case 1: .FRQ1 = .FRQ1 + 1
  Case 2: .FRQ2 = .FRQ2 + 1
  Case 3: .FRQ3 = .FRQ3 + 1
  Case 4: .FRQ4 = .FRQ4 + 1
  Case 5: .FRQ5 = .FRQ5 + 1
  Case 6: .FRQ6 = .FRQ6 + 1
  Case 7: .FRQ7 = .FRQ7 + 1
  Case 8: .FRQ8 = .FRQ8 + 1
  Case 9: .FRQ9 = .FRQ9 + 1
  End Select
End If

I tried it like that and it caught all the numbers.

[Guido Geurs]

I am sorry, my mistake, I understood your call (...of all possible numbers with the same ending... ) as for each digit ENDING on 1 it's : FRQ1= FRQ1 + 1
So for 1, 11, 21 it's for me FRQ1=3.

But it must be FRQ1=4 !
1x from "1" + 2x from "11" and 1x from "21" = 4

Am I right ??
If so, then Your code is OK but can be reduced like: see attachment.

PS
I have added a column to check the total of the frequencies.
It must be equal to the number of digits in the data.

[Guido Geurs]

There is an error in my code (not valid for the data= "10")
It must be: (see also attachment)

         If ARRAYDATA(ARRAYDATAidx, ARRAYDATACOLidx) > 9 Then _

or :

         If Len(ARRAYDATA(ARRAYDATAidx, ARRAYDATACOLidx)) > 1 Then _

[DANNYOCEAN]

I opened the file, but it runs then stops. the follow error message appears : error in execution time "13"
and in vb appears in yellow in the code, with an arrow point on it: Call Set_Frequency(R ight(ARRAYDATA( ARRAYDATAidx, ARRAYDATACOLidx ), 1), ARRAYDATAidx)

[Guido Geurs]

You can't run the macro twice because the first row has a header without data!
I have attached the same macro but in the sheet is the first row deleted and the freq results also.Now You can run the macro !
Maybe You have to insert a protection in the macro to prevent that the macro runs when row 1 has no data.

[DANNYOCEAN]

I opened the file, I runned the macro that you provided.
It works well,
but it´s counting the wrong way: it´s counting 14 as 41. but 14 is not 41.
I mean that the macro should count all possible numbers with the same numbers ending... 21, 61 must count 2 not 3 is the spreadsheet.
I think that this macro is counting 14 as 41. See the 1st line the spreadsheet. I think that this error happens in other lines, cause I compared the results column with the data inputed.

Can you give a hand to solve this problem?

[DANNYOCEAN]

I read the notation that you used above. May I borrow to explain better:
for each digit ENDING on 1 it's :
So for 1, 11, 21 it should counting FRQ1=3, not 4 as it seems that the last macro is counting in the wrong way.

for each digit ENDING on 7 it's :
67 20 55 74 45 48 85 91 79 57 47 22 17 54

So for 67,57, 47, 17 But it must be FRQ7=4 not 6, cause it´s counting 74 and 79, but both don´t end in 7. it seems that the last macro that you posted is doing this way.

I think that this error happens in other lines, cause I compared the results column with the data inputed.
Can you help solving this problem?

[Guido Geurs]

I'm sorry but I was confused because the first macro seems to be the right one no?

I have attached the 1st one again with some improvements: last column is the total of the frequencies and this must be equal to the number of data in each line no?

If I'm still wrong, please attach an example of a sheet (only the first 5 lines of data) with the columns of the frequencies like it should be.