Code:
Option Explicit
Sub Dik1Dik2_() '
Dim Ar As Long, Em As Long
Let Em = Range("A" & Rows.Count).End(xlUp).Row
Dim A() As Variant: Let A() = Range("A1:A" & Em & "").Value2 ' The main data from column 1
Dim Dik1 As Object, Dik2 As Object: Set Dik1 = CreateObject("Scripting.Dictionary"): Set Dik2 = CreateObject("Scripting.Dictionary")
For Ar = 1 To Em ' The main data rows range
Let Dik1(Ar) = Split(A(Ar, 1), " ", 2, vbBinaryCompare) ' I am splitting each data into 2 bits, the first is the data ID the second is all the rest
Let Dik2(Ar) = Split(StrReverse(Dik1(Ar)(1)), " ", 6, vbBinaryCompare) ' We are splitting the reversed string, because my second data CITY might have a few words, I split the backward string in just enough bits so that the last element is the data CITY regardles of how many words are in it
Let Dik2(Ar) = Array(StrReverse(Dik2(Ar)(0)), StrReverse(Dik2(Ar)(1)), StrReverse(Dik2(Ar)(2)), StrReverse(Dik2(Ar)(3)), StrReverse(Dik2(Ar)(4)), StrReverse(Dik2(Ar)(5))) ' The problem with the last line is that all my data words and data numbers are in reverse , so I need to put each data back the correct way around
Next Ar
Dim v: v = Dik1.items()
' The end result of the above is that we have two 1 D arrays in the items of the dictionaries, one in each. Each element is itself a 1 D array. We find that strangely that INDEX seems to treat such arrays as like 2 D arrays as long as all the 1 D array elements have the same number of elements. This allows us to use the Index(arr(), Rws(), Clms()) way to get out our final range in any order we like.
Let Range("B2:B" & Em + 1 & "").Value = Application.Index(Dik1.items(), Evaluate("=Row(1:" & Em & ")"), Array(1)) ' We only want the first column from V1()
Let Range("C2:H" & Em + 1 & "").Value = Application.Index(Dik2.items, Evaluate("=Row(1:" & Em & ")"), Array(6, 5, 4, 3, 2, 1)) ' We want all the data columns from V2() but need them in the reverse order because we split the reversed string
Range("A1:H1").EntireColumn.AutoFit
End Sub
Sub Dik1Dik2__() '
Dim Ar As Long, Em As Long
Let Em = Range("A" & Rows.Count).End(xlUp).Row
Dim A() As Variant: Let A() = Range("A1:A" & Em & "").Value2 ' The main data from column 1
Dim Dik1 As Object, Dik2 As Object: Set Dik1 = CreateObject("Scripting.Dictionary"): Set Dik2 = CreateObject("Scripting.Dictionary")
For Ar = 1 To Em ' The main data rows range
Let Dik1(Ar) = Split(A(Ar, 1), " ", 2, vbBinaryCompare) ' I am splitting each data into 2 bits, the first is the data ID the second is all the rest
Let Dik2(Ar) = Split(StrReverse(Dik1(Ar)(1)), " ", 6, vbBinaryCompare) ' We are splitting the reversed string, because my second data CITY might have a few words, I split the backward string in just enough bits so that the last element is the data CITY regardles of how many words are in it
Let Dik2(Ar) = Array(StrReverse(Dik2(Ar)(5)), StrReverse(Dik2(Ar)(4)), StrReverse(Dik2(Ar)(3)), StrReverse(Dik2(Ar)(2)), StrReverse(Dik2(Ar)(1)), StrReverse(Dik2(Ar)(0))) ' The problem with the last line is that all my data words and data numbers are in reverse , so I need to put each data back the correct way around
Next Ar
Dim v: v = Dik1.items()
' The end result of the above is that we have two 1 D arrays in the items of the dictionaries, one in each. Each element is itself a 1 D array. We find that strangely that INDEX seems to treat such arrays as like 2 D arrays as long as all the 1 D array elements have the same number of elements. This allows us to use the Index(arr(), Rws(), Clms()) way to get out our final range in any order we like.
Let Range("B2:B" & Em + 1 & "").Value = Application.Index(Dik1.items(), Evaluate("=Row(1:" & Em & ")"), Array(1)) ' We only want the first column from V1()
Let Range("C2:H" & Em + 1 & "").Value = Application.Index(Dik2.items, Evaluate("=Row(1:" & Em & ")"), Array(1, 2, 3, 4, 5, 6)) ' We want all the data columns from V2() but need them in the reverse order because we split the reversed string
Range("A1:H1").EntireColumn.AutoFit
End Sub
Reply With Quote
Bookmarks