1

I run a report daily which lists various files based on a particular identifier which indicates what is within the file. In the hopes of expediting my workflow, I'd like to set these files in a simple report by identifier, along with how frequently each identifier occurred, and organize this list from most to least frequent. Below is what I'm working with:

  1. This is a VBA based terminal emulator with limited object library references, so Excel isn't an option
  2. There's usually anywhere from 0-200 separate identifiers on any report, and they don't always appear on each. Since there are thousands of possible identifiers that could appear, I'd rather just have the macro list what it finds than look for them specifically.
  3. I use a bit of code utilizing ADOdb streaming to write similar reports to a .txt file, which I would like to use here. I'm sure I can make that happen after the data is sorted, but felt it worth mentioning in case the nature of how the filtered/organized list would be utilized would affect how it is addressed.
  4. I'm a novice, self-taught programmer with a particular fear of arrays (and I know this is going to come down to arrays...). So I don't necessarily know what commands and options are available to me in coding. (ie, a lot of what I've read involved possibly using dictionaries or collections, but I'm unsure I even have these available, let alone how to use them.)

I'd rather avoid creating multiple arrays, and creating multiple For loops if possible. It seems like a two dimensional dynamic array SortList(category, frequency) would be the route to go, but I can't find a way to filter unique values into it, while counting any of the values it has already found, and then sorting it afterwards.

I found a REALLY nifty bit of code on one site that can filter unique values without using a loop... (credit to where it's due: http://www.jpsoftwaretech.com/finding-values-in-an-array-without-looping/), but it seems it can only work for one dimensional arrays: 

    Function IsInArray(arr As Variant, valueToFind As Variant) As Boolean
    IsInArray = (UBound(Filter(arr, valueToFind)) > -1)
    End Function

Does anyone have any suggestions on how I should try and tackle this dilemma?

8/14 - Suppose this is also important to note: these are not individual files one can access in a folder somewhere; the data I'm working with is presented simply as a list. It might be better to state what I'm doing is having these identifiers read as strings. It's the strings I want to count, sort, and organize... it seems like something that can be done with beginner's programming techniques, I just can't seem to find a way to do it without extraneous arrays or unnecessary nested loops and if/then conditions. Thoughts?

C. Jack
  • 13
  • 5

2 Answers2

1

If you are familiar with ADO/DAO you could try querying the files ensuring the field in question is in GROUP BY and outputting the result of a COUNT() of the identifier.

DaveMac
  • 76
  • 10
0

Alternatively you could use a combination of collections and arrays.

GSeg illustrates quite well how you could utilise a collection of arrays here. Collection of Arrays

By using a collection you can add an identifier as a Key and its count as the second array element. As you cant duplicate keys in collections, if you use the identifier as a key it will give you a unique list. When you try and add a duplicate key you therefre just need to trap the error and increase the count for that key. The count would be the second element in the array for that collections key.

Hope this makes sense. Good luck.

Community
  • 1
  • 1
DaveMac
  • 76
  • 10