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.
Below is my Data ..........But in practical project it will be in different workbook hence which is best way to deal.
Thanks
mg
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 | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | Emp | Score | Emp | Score | |||
2 | 1750 | 432 | 1183 | 364 | |||
3 | 1667 | 423 | 1195 | 533 | |||
4 | 1903 | 421 | 1919 | 430 | |||
5 | 1709 | 348 | 1238 | 427 | |||
6 | 1259 | 476 | 1051 | 587 | |||
7 | 1374 | 473 | 1538 | 349 | |||
8 | 1138 | 388 | 1573 | 443 | |||
9 | 1767 | 303 | 1397 | 581 | |||
Sheet1 |
Thanks
mg