vba help - Can we add two set of Array into Single

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
976
Office Version
  1. 2010
Platform
  1. Windows
Hi Team,

I am using two Loops here , Can we shorten code, By storing two Array into Single Array and Running Single loop.
Any alternate way of doing same things just for learning purpose.



VBA Code:
Sub Add_TwoSet_Data_Into_Dictionary()

    Dim dict As New Scripting.Dictionary
    Dim i As Long
    Dim arr1 As Variant
    Dim arr2 As Variant

    arr1 = Range("A1").CurrentRegion.Value
    arr2 = Range("d1").CurrentRegion.Value

   
    With dict
        'First Set of Data
        For i = LBound(arr1, 1) To UBound(arr1, 1)
            If Not .Exists(arr1(i, 1)) Then
                .Add arr1(i, 1), arr1(i, 2)
            End If
        Next i
   
   
    'Second Set of Data
        For i = LBound(arr2, 1) To UBound(arr2, 1)
            If Not .Exists(arr2(i, 1)) Then
                .Add arr2(i, 1), arr2(i, 2)
            End If
        Next i
   
'Print
Range("H1").Resize(.Count, 2).Value = Application.WorksheetFunction.Transpose(Array(.Keys, .Items))
   
   
End With

End sub


Below is my Data ..........But in practical project it will be in different workbook hence which is best way to deal.

Book2
ABCDE
1EmpScoreEmpScore
217504321183364
316674231195533
419034211919430
517093481238427
612594761051587
713744731538349
811383881573443
917673031397581
Sheet1



Thanks
mg
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Putting the two arrays into the Dictionary is essentially combining them into one array. If you make them into one array before adding them to the Dictionary, then the Dictionary would be redundant.

 
Upvote 0
Tis is probably a better reference for understanding the Dictionary:

 
Upvote 0
You could do something like this if the sizes of the ranges (arrays) are the same:

VBA Code:
Option Explicit

Sub Add_TwoSet_Data_Into_Dictionary()

    Dim dict As New Scripting.Dictionary
    Dim i As Long
    Dim arr1 As Variant
    Dim arr2 As Variant

    arr1 = Range("A1").CurrentRegion.Value
    arr2 = Range("D1").CurrentRegion.Value

    ' Only if both sets are of the same size.
    With dict
        For i = LBound(arr1, 1) To UBound(arr1, 1)
            'First Set of Data
            .Item(arr1(i, 1)) = .Item(arr1(i, 1)) + arr1(i, 2)
            'Second Set of Data
            .Item(arr2(i, 1)) = .Item(arr2(i, 1)) + arr2(i, 2)
        Next i
        'Print
        Range("H1").Resize(.Count, 2).Value = Application.WorksheetFunction _
          .Transpose(Array(.Keys, .Items))
    End With

End Sub

By adding items in this way, the values of each item are summed up. You could also count the occurrences of each item: .Item(arr2(i, 1)) = .Item(arr2(i, 1)) + 1
 
Upvote 0
VBA Code:
Sub Add_TwoSet_Data_Into_Dictionary()

    Dim dict As New Scripting.Dictionary
    Dim i As Long
    Dim area As Range
   
    With dict
        For Each area In Union([A1].CurrentRegion, [d1].CurrentRegion).Areas
            arr = area.Value
            For i = LBound(arr, 1) To UBound(arr, 1)
                '.Item(arr(i, 1)) = arr(i, 2)
                .Item(arr(i, 1)) = .Item(arr(i, 1)) + arr(i, 2)
            Next i
        Next area
        'Print
        Range("H1").Resize(.Count, 2).Value = Application.WorksheetFunction.Transpose(Array(.Keys, .Items))
    End With

End Sub
 
Upvote 0
Hi veySeleMre,

Really nice code, this code is summing values, I don't want to sum the values here.
if key exists .... next time if I come across key again , I don't to add/sum that key.





Thanks
mg
 
Upvote 0
Hi veySeleMre,

Really nice code, this code is summing values, I don't want to sum the values here.
if key exists .... next time if I come across key again , I don't to add/sum that key.





Thanks
mg
It was just for the purpose of learning something. Of course you will use If Not Exists... if you only need the first occurrence. Sorry.
But anyway, I would stick to your given solution. It's maybe a line or two more, but it's more readable then shortening the code and there is nothing to gain in efficiency.
 
Upvote 0
Hi,
VBA Code:
If Not .Exists(arr(i, 1)) Then .Add arr(i, 1), arr(i, 2)
Or
VBA Code:
.Item(arr(i, 1)) = arr(i, 2)
 
Upvote 0
Hi JLGWhiz,veySeleMre and VBBasics

Thank you all for your help learned something new this example. (y) ?


Thanks
mg
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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
Back
Top