Collection library

[theta]

Hi, if I had a range from A1 : A100 contained many duplicated items, I was told there was a method in VBA to add all of these to an array, but it would only store the unique values? A collection or library

Any ideas?

 

[GTO]

You can use a Dictionary or Collection Object.

 

[theta]

Those were the ones!

How would I go about using them?

My ideal situation would be that it goes through a range e.g. A1:A100 only storing unique items. But then I also need a second array containing the adjacent value in column B.

So the two arrays will be the same size, but the first will do all of the logical work and the second will be based on the index of the first?

If not can just form a unique array and get the column B details using a LOOKUP (but not efficient)

Any help apprecaited!

 

[GTO]

...My ideal situation would be that it goes through a range e.g. A1:A100 only storing unique items. But then I also need a second array containing the adjacent value in column B...

Hi Theta,

I am not clear on that part. Presuming there are corresponding vals in Col B for ea val in Col A, and Col A has duplicates, once we rid the duplicates, how are we picking a certain val in B to go along with a particular val (unique now) from A?

Anyways, here is a short example to run in a blank/new workbook. Both in a Standard Module, run Setup to get some junk vals and Exa to retunr the uniques in Col B.

Option Explicit
    
Sub Setup()
    With Sheet1.Range("A1:A100")
        .Formula = "=ROUND(RAND()*25,0)"
        .Value = .Value
    End With
End Sub
    
Sub exa()
Dim DIC As Object '<--- Dictionary
Dim rngMyRange As Range
Dim aryMyArray
Dim i As Long
    
    Set rngMyRange = Sheet1.Range("A1:A100")
    Set DIC = CreateObject("Scripting.Dictionary")
    
    aryMyArray = rngMyRange.Value
    
    For i = 1 To UBound(aryMyArray, 1)
        DIC.Item(Key:=aryMyArray(i, 1)) = Empty
    Next
    
    aryMyArray = DIC.Keys
    
    Sheet1.Range("B1").Resize(UBound(aryMyArray, 1) + 1).Value _
        = Application.Transpose(aryMyArray)
    
End Sub

Hope that helps,

Mark

 

[theta]

Hi, yes you are correct. For each item in A, I would also like to store the adjacent item in column B in it's own array

The two should be related. So if A is duplicated, it will always have the same column B value (imagine fruit being A, and colour being B...so Banana will always have Yellow in the adjacent column)

I will give this a go, it is going to save me alot of time...

 

[theta]

Hi Mark,

So looking at your code...if I wanted to store the adjacent value I could just form a second array and load values to it using 'i' as the index? (as both original data arrays are the same size)

I could then call items from both arrays using the same index and they should tie up

See any problem with this logic?

(I am also giving this a read : http://www.cpearson.com/excel/CollectionsAndDictionaries.htm)

 

[GTO]

Hi, yes you are correct. For each item in A, I would also like to store the adjacent item in column B in it's own array...

Hi Mark,

So looking at your code...if I wanted to store the adjacent value I could just form a second array and load values to it using 'i' as the index? (as both original data arrays are the same size)

I could then call items from both arrays using the same index and they should tie up

See any problem with this logic?

(I am also giving this a read : http://www.cpearson.com/excel/CollectionsAndDictionaries.htm)

Okay, I am probably just being thick-headed, but just to confirm, does your data look something like:

Excel Workbook
	A	B
1	Banana	Yellow
2	Pear	Green
3	Orange	Orange
4	Apple	Red
5	Banana	Yellow
6	Cherry	Maroon
7	Pear	Green
8	Potato	Brown
9	Cantaloupe	Tan
10	Pear	Green
Sheet1

Excel 2003

That is, we are not going to find different vals is Col B for the same val in Col A, like:

Excel Workbook
	A	B
1	Banana	5
2	Pear	10
3	Banana	3
Sheet1

Excel 2003

 

[theta]

Correct! There is always a one-to-one relationship (just duplicate records)

 

[GTO]

Okay, then we could also assign/overwrite the val of .Item for ea Key.

For:

Excel Workbook
	D	E
1	Banana	Yellow
2	Pear	Green
3	Orange	Orange
4	Apple	Red
5	Cherry	Maroon
6	Potato	Brown
7	Cantaloupe	Tan
Sheet1

Excel 2003

Maybe:

Sub exa()
Dim DIC As Object '<--- Dictionary
Dim rngMyRange As Range
Dim aryMyArray, TmpColA, TmpColB
Dim i As Long
    With Sheet1
        Set rngMyRange = Range(.Range("A1"), .Cells(.Rows.Count, "B").End(xlUp))
        
        Set DIC = CreateObject("Scripting.Dictionary")
        
        aryMyArray = rngMyRange.Value
        
        For i = 1 To UBound(aryMyArray, 1)
            DIC.Item(Key:=aryMyArray(i, 1)) = aryMyArray(i, 2)
        Next
        
        '// These are both zero-based 1-dimension arrays    //
        TmpColA = DIC.Keys
        TmpColB = DIC.Items
        
        ReDim aryMyArray(1 To DIC.Count, 1 To 2)
        
        '// Or, we could store both arrays from above in one 2-dimensional array//
        For i = 1 To DIC.Count
            aryMyArray(i, 1) = TmpColA(i - 1)
            aryMyArray(i, 2) = TmpColB(i - 1)
        Next
        
        .Range("D1").Resize(UBound(aryMyArray, 1), 2).Value _
            = aryMyArray
    End With
End Sub

 

[theta]

Fantastic! I will get to work using this method.

I will only have to adapt it to page ranges (I have allocated 40 lines per page) so that I can select elements 1-40, 41-80, 81-100 from each array

?