count how many of any variable are in a worksheet?

[jifop]

hello all, i have a call logger program that exports all data to a dbf file which i then use excel to display, my question is how can i search for the most frequently used numbers in any specific column? below is an example of the data pulled out! (i have removed the phone numbers)

09/02/2005 12:32 00/01/1900 00:00 899 9999 9 90123456 0 VCE 150 83 16 1 OG 150 82264 A
09/02/2005 12:32 00/01/1900 00:00 888 0101 999999 0 VCE 150 51 16 1 IC 150 82259 A
09/02/2005 12:29 00/01/1900 00:04 821 9999 9 90028563 0 VCE 150 61 16 20 OG 150 82249 A
09/02/2005 12:34 00/01/1900 00:00 840 0104 999999 0 VCE 150 568 16 11 IC 150 82274 U
09/02/2005 12:34 00/01/1900 00:01 883 9999 999999 0 VCE 150 199 16 1 IC 150 82279 A
09/02/2005 12:32 00/01/1900 00:03 884 0101 9 904577 0 VCE 150 94 16 1 OG 150 82268 A
09/02/2005 12:36 00/01/1900 00:00 841 0104 9 9078888 0 VCE 150 94 16 1 OG 150 82281 A
09/02/2005 12:32 00/01/1900 00:04 821 9999 999999 0 VCE 150 199 16 8 IC 150 82255 A
09/02/2005 12:37 00/01/1900 00:00 821 9999 999999 0 VCE 150 156 16 8 IC 150 82287 A
09/02/2005 12:36 00/01/1900 00:00 883 9999 9 9087888 0 VCE 150 29 16 1 OG 150 82283 A
09/02/2005 12:38 00/01/1900 00:00 892 9999 9 9088522 0 VCE 150 83 16 1 OG 150 82292 A
09/02/2005 12:41 00/01/1900 00:00 883 9999 851 0 VCE 150 0 0 8 IN 150 82294 A
09/02/2005 12:43 00/01/1900 00:00 891 9999 500 0 VCE 150 0 0 5 OG 150 82306 A
09/02/2005 12:43 00/01/1900 00:00 821 0104 999999 0 VCE 150 67 0 8 IC 150 82306 A
09/02/2005 12:43 00/01/1900 00:00 891 0104 999999 0 VCE 150 0 16 3 IC 150 82306 A
09/02/2005 12:42 00/01/1900 00:02 884 0101 999999 0 VCE 150 22 16 1 IC 150 82302 A

[d61helix]

trim the line with functions like left(), right(), mid() then sort. Viola!!

cheers

[jifop]

trim the line with functions like left(), right(), mid() then sort. Viola!!

cheers

thanks i dont quite understand, so i can use that command to go through and tell me which numbers apear most frequently

[d61helix]

1st of all to help me understand, do the phone numbers appear on just one column or are they together with rest of data? thxs

[jifop]

in three of the colums, one being extension number (3 digit) one being dialled numbers (3-15digits) and fianally the incoming number (11 digits max (9999 is witheld))

the analysis will only be on one colum at a time though so i'd like to know the most called number, number that dialled in most etc

[d61helix]

heres a rough solution, i think the way to do it is probably sort out the numbers and consolilate them then count them by using loop, my sub is to allow user to select the column and tells which number is most frequent in the column and how many times...but theres a catch..haha..if theres more than 1 number which is most freq, my sub will only return the 1st counted one, so ill jus leave it for you to figure it out.

Cheers

Please Login or Register  to view this content.

[jifop]

you are indeed a clever person, although can i make it exclude 9999 and 999999 ?