Position in array, best way?

hypnotoad

New Member
Joined
Feb 7, 2012
Messages
47
I have an array called FileRefArr (dimmed as (1 To number_of_files, 1 To 8)) that stores information of 500-1000 text files. The (x, 2) value within the array contains an identifier. The identifier is not exclusive to each file (100 or so of the files might have the same identifier). I wrote a function to count the number of occurrences of each identifier, but I'm not sure how to gather the position of each occurrence. It needs to be dynamic, since the number of files and identifiers is always changing.

I made another array called Ident_List that stores only unique identifiers (1 - 15 unique identifiers for every 1000 files) and number of occurrences of each identifier. I was hoping to store an array of the positions in that array, but I'm not sure how to do that (see table below).

What I want to do:

UniqueIdent1, 55, Array of Positions
UniqueIdent2, 99, Array of Positions
UniqueIdent3, 8, [1,2,3,4,8,17,98,161]
...etc


Does anyone have any ideas? Would a 3-dimensional array work fine?

Code of occurrences function...
Code:
Function OccurrencesInArray(InputArray() As String, Match_Name As String) As String

    Dim i                   As Long
    Dim o_counter           As Integer
    Dim TempArray()         As String
    
    ReDim TempArray(1 To UBound(InputArray))
        
    For i = LBound(InputArray) To UBound(InputArray)
        TempArray(i) = InputArray(i, 2)
    Next i

    For i = LBound(TempArray) To UBound(TempArray)
        If TempArray(i) = Match_Name Then
            o_counter = o_counter + 1
        End If
    Next i
  
    OccurrencesInArray = o_counter
    Erase TempArray
    
End Function
 
Last edited:

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
I don't think there's any right or wrong here - chances are that even a brute force solution will work very quickly since arrays are very efficient storage when you know the address of the element (x, 2). So iterating your 500-100 identifiers when/as needed likely works just fine (even to retrieve counts). I'd probably write small functions to handle this, and you are done. In short, you're probably just fine already.

However, my experience is that over time you find all kinds of different ways to use arrays, collections, and (scripting) dictionaries. Not that it's necessarily better, but here I've used a dictionary with the identifier as the key, and a comma separated list of "positions" as the item. Something similar could be done with VBA collections (which, btw, are always 1-based). In fact, in an Excel environment, you could also just store the information on a hidden worksheet too... !

Sample "Dictionary" solution (not necessarily a better one though, as stated):
Code:
[COLOR="Navy"]Sub[/COLOR] Foo()

[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] sTemp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] arrTemp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR]
[COLOR="Navy"]Dim[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR] * 18
[COLOR="Navy"]Dim[/COLOR] a(1 [COLOR="Navy"]To[/COLOR] 500, 1 [COLOR="Navy"]To[/COLOR] 8)
[COLOR="Navy"]Dim[/COLOR] vKey [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR]
[COLOR="Navy"]Dim[/COLOR] dic [COLOR="Navy"]As[/COLOR] Scripting.Dictionary

[COLOR="SeaGreen"]'//Create Dictionary Object[/COLOR]
[COLOR="Navy"]Set[/COLOR] dic = CreateObject("Scripting.Dictionary")

[COLOR="SeaGreen"]'//Load array with test data (repeating identifiers in the second element of the array)[/COLOR]
[COLOR="Navy"]For[/COLOR] i = 1 [COLOR="Navy"]To[/COLOR] 20
    a(i, 2) = Int((15 - 0 + 1) * Rnd + 0)
[COLOR="Navy"]Next[/COLOR] i

[COLOR="SeaGreen"]'//Populate dictionary with identifiers as keys and positions as items[/COLOR]
[COLOR="Navy"]For[/COLOR] i = 1 [COLOR="Navy"]To[/COLOR] 20
    [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] dic.Exists(CStr(a(i, 2))) [COLOR="Navy"]Then[/COLOR] [COLOR="SeaGreen"]'//possibly overkill but I always explicitly cast keys as strings[/COLOR]
        dic.Add CStr(a(i, 2)), i
    [COLOR="Navy"]Else[/COLOR]
        dic(CStr(a(i, 2))) = dic(CStr(a(i, 2))) & "," & i
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]Next[/COLOR] i

[COLOR="SeaGreen"]'//Show list of identifiers directly from the array[/COLOR]
[COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] "identifiers: "
sTemp = ""
[COLOR="Navy"]For[/COLOR] i = 1 [COLOR="Navy"]To[/COLOR] 20
    sTemp = sTemp & a(i, 2) & ","
[COLOR="Navy"]Next[/COLOR] i
[COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] Left(sTemp, Len(sTemp) - 1)
[COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR]

[COLOR="SeaGreen"]'//Show dictionary keys and positions[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] vKey [COLOR="Navy"]In[/COLOR] dic.Keys [COLOR="SeaGreen"]'//counter variable must be a variant to iterate a dictionary with for-each[/COLOR]
    s = vKey & "{" & dic(vKey) & "}"
    [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] s & "[ Count:" & (UBound(Split(dic(vKey), ",")) + 1) & "]"
[COLOR="Navy"]Next[/COLOR] vKey

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Output:
Code:
identifiers: 
11,8,9,4,4,12,0,12,13,11,0,6,13,12,5,15,13,0,15,5

11{1,10}          [ Count:2]
8{2}              [ Count:1]
9{3}              [ Count:1]
4{4,5}            [ Count:2]
12{6,8,14}        [ Count:3]
0{7,11,18}        [ Count:3]
13{9,13,17}       [ Count:3]
6{12}             [ Count:1]
5{15,20}          [ Count:2]
15{16,19}         [ Count:2]
 

hypnotoad

New Member
Joined
Feb 7, 2012
Messages
47
Xenou, I've only been doing excel vba on and off (mostly off) for about 5 months and have no formal programming training, so this is pretty beyond me. This was also only my second attempt at arrays. I don't even know what a 1-based means (I remember logs are normally base 10 hah!). Perhaps I should read a book :|. If I can figure out what you did up there, I will then try and figure out how to use this data to extract data from the large array. I want the user to be able to select one of the "keys" and then print out all data from the master array that pertains to that "key." That's the overall goal. Thanks a lot, I'll go review this.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

What does the '* 18' do when you 'dim s as string'? Noobish I'm sure.
This is actually a little obscure - it just declares a string type of fixed width. I only used to to make my output Ascii text line up.

If you are just learning arrays I'd tackle them first before dictionaries. 1-based arrays mean that the array starts with the first element at index 1:
array(1,1)
array(1,2)
array(2,1)
array(2,2)
array(3,1)
array(3,2)

Most programming languages are 0-based, and arrays start at index 0:
array(0,0)
array(1,1)
array(1,0)
array(1,1)
array(2,0)
array(2,1)

It can be a cause of errors if you think you are using a 1-based array that is actually 0-based, and vice versa. I don't think there's any easy way to learn array-handling. You just have work it out and after a while it gets easier.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
Another try for kicks. No dictionaries this time - just "brute force" (but farming out the jobs to little functions can keep your main program quite neat and tidy):

Code:
[COLOR="Navy"]Sub[/COLOR] Foo()

[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] Long, j [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] arrTemp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR]
[COLOR="Navy"]Dim[/COLOR] ID [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] a(1 [COLOR="Navy"]To[/COLOR] 20, 1 [COLOR="Navy"]To[/COLOR] 8)
[COLOR="Navy"]Dim[/COLOR] lngCount [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] arrPositions

Randomize

[COLOR="SeaGreen"]'//Load array with test data (repeating identifiers in the second element of the array)[/COLOR]
[COLOR="Navy"]For[/COLOR] i = 1 [COLOR="Navy"]To[/COLOR] 20
    [COLOR="Navy"]For[/COLOR] j = 1 [COLOR="Navy"]To[/COLOR] 8
        a(i, j) = Chr(64 + i) & Chr(64 + j) & Format(Int((999 - 0 + 1) * Rnd + 0), "000")
    [COLOR="Navy"]Next[/COLOR] j
    a(i, 2) = Format(Int((15 - 0 + 1) * Rnd + 0), "0000")
[COLOR="Navy"]Next[/COLOR] i

[COLOR="SeaGreen"]'//Show array contents[/COLOR]
[COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] String(15, "-") & vbNewLine & "Array Contents:"
[COLOR="Navy"]For[/COLOR] i = 1 [COLOR="Navy"]To[/COLOR] 20
    [COLOR="Navy"]ReDim[/COLOR] arrTemp(1 [COLOR="Navy"]To[/COLOR] 8)
    [COLOR="Navy"]For[/COLOR] j = 1 [COLOR="Navy"]To[/COLOR] 8
        arrTemp(j) = a(i, j)
    [COLOR="Navy"]Next[/COLOR] j
    [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] Join(arrTemp, ",")
[COLOR="Navy"]Next[/COLOR] i

[COLOR="SeaGreen"]'//Pick an identifier at random[/COLOR]
ID = a(3, 2)

[COLOR="SeaGreen"]'//Get count of an identifier[/COLOR]
lngCount = GetIDCount(ID, a)
[COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] String(15, "-") & vbNewLine & "identifier " & ID & " count: " & lngCount

[COLOR="SeaGreen"]'//Get positions of elements with an identifier[/COLOR]
arrPositions = GetPositions(ID, a)
[COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] String(15, "-") & vbNewLine & "identifier " & ID & " positions: " & Join(arrPositions, ",")

[COLOR="SeaGreen"]'//Get subarrays of elements with an identifier[/COLOR]
[COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] String(15, "-")
[COLOR="Navy"]Call[/COLOR] ShowSubArrays(arrPositions, a)

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Function[/COLOR] GetIDCount(ByVal ID [COLOR="Navy"]As[/COLOR] String, a [COLOR="Navy"]As[/COLOR] Variant) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] j [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    
    [COLOR="Navy"]For[/COLOR] i = LBound(a, 1) [COLOR="Navy"]To[/COLOR] UBound(a, 1)
        [COLOR="Navy"]If[/COLOR] a(i, 2) = ID [COLOR="Navy"]Then[/COLOR]
            j = j + 1
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]Next[/COLOR] i

    GetIDCount = j
    
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Function[/COLOR] GetPositions(ByVal ID [COLOR="Navy"]As[/COLOR] String, a [COLOR="Navy"]As[/COLOR] Variant) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR]
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] b

    [COLOR="Navy"]For[/COLOR] i = LBound(a, 1) [COLOR="Navy"]To[/COLOR] UBound(a, 1)
        [COLOR="Navy"]If[/COLOR] a(i, 2) = ID [COLOR="Navy"]Then[/COLOR]
            s = s & i & "|"
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]Next[/COLOR] i

    [COLOR="Navy"]If[/COLOR] Len(s) > 0 [COLOR="Navy"]Then[/COLOR]
        GetPositions = Split(Left(s, Len(s) - 1), "|")
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Sub[/COLOR] ShowSubArrays(arrPositions [COLOR="Navy"]As[/COLOR] Variant, [COLOR="Navy"]ByRef[/COLOR] a [COLOR="Navy"]As[/COLOR] Variant)
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] j [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

    [COLOR="Navy"]For[/COLOR] i = LBound(arrPositions) [COLOR="Navy"]To[/COLOR] UBound(arrPositions)
        s = "Values for ID " & a(arrPositions(i), 2) & " at position " & Format(arrPositions(i), "000") & ": "
        [COLOR="Navy"]For[/COLOR] j = 1 [COLOR="Navy"]To[/COLOR] UBound(a, 2)
            s = s & a(arrPositions(i), j) & " "
        [COLOR="Navy"]Next[/COLOR] j
        [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] Trim(s)
    [COLOR="Navy"]Next[/COLOR] i
    
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Output:
Code:
---------------
Array Contents:
AA367,0014,AC289,AD246,AE737,AF861,AG633,AH079
BA691,0000,BC507,BD546,BE158,BF072,BG269,BH297
CA231,0000,CC372,CD231,CE697,CF354,CG656,CH623
DA991,0002,DC377,DD122,DE995,DF492,DG346,DH684
EA718,0005,EC288,ED864,EE282,EF285,EG438,EH675
FA019,0008,FC151,FD956,FE151,FF681,FG932,FH091
GA253,0001,GC397,GD792,GE361,GF578,GG589,GH260
HA322,0010,HC700,HD508,HE030,HF059,HG869,HH690
IA881,0015,IC726,ID971,IE956,IF526,IG176,IH337
JA481,0000,JC846,JD437,JE260,JF945,JG615,JH862
KA691,0000,KC861,KD491,KE237,KF280,KG749,KH233
LA024,0008,LC532,LD618,LE130,LF720,LG257,LH669
MA995,0015,MC482,MD482,ME328,MF944,MG569,MH458
NA737,0007,NC665,ND082,NE271,NF318,NG525,NH299
OA385,0013,OC944,OD853,OE567,OF293,OG948,OH175
PA925,0003,PC446,PD549,PE568,PF965,PG098,PH667
QA103,0001,QC571,QD923,QE132,QF398,QG862,QH869
RA394,0007,RC197,RD433,RE622,RF302,RG714,RH559
SA365,0010,SC558,SD206,SE178,SF703,SG240,SH979
TA182,0006,TC976,TD511,TE274,TF314,TG895,TH986
---------------
identifier 0000 count: 4
---------------
identifier 0000 positions: 2,3,10,11
---------------
Values for ID 0000 at position 002: BA691 0000 BC507 BD546 BE158 BF072 BG269 BH297
Values for ID 0000 at position 003: CA231 0000 CC372 CD231 CE697 CF354 CG656 CH623
Values for ID 0000 at position 010: JA481 0000 JC846 JD437 JE260 JF945 JG615 JH862
Values for ID 0000 at position 011: KA691 0000 KC861 KD491 KE237 KF280 KG749 KH233
 

hypnotoad

New Member
Joined
Feb 7, 2012
Messages
47
I'll give both ways a try and see which one works better for me. Thank you for the help, I'll get back soon!!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,784
Members
414,020
Latest member
Meghdad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top