Get stored value from existing item in dictionary

[YasserKhalil]

Hello everyone

I am trying the following code

Sub Test()
    Dim a, dic As Object, s As String, i As Long, j As Long
   
    a = ThisWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion.Value
    a = Application.Index(a, Evaluate("ROW(1:" & UBound(a, 1) & ")"), [{3,7,13,14,15,16,17,18,19,20,21}])
   
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1

    For i = LBound(a) To UBound(a)
        s = a(i, 1) & vbTab & a(i, 2)
        If Not dic.Exists(s) Then
            dic.Add s, s
           
            For j = LBound(a, 2) To UBound(a, 2)
                If j = 1 Or j = 3 Or j = 6 Or j = 9 Then
                    If i > 1 Then a(i, j) = CLng(Application.WorksheetFunction.WorkDay(CDate(a(i, 1)), IIf(j = 1, 0, Val(j / 3))))
                End If
            Next j
        Else
            'Need Here Some Help
            Stop
        End If
    Next i
   
End Sub

What I need to add in this code is to detect if the item existing in the dictionary or not
If new then some calculations will be done ..on columns M to U
And if it is already exists, there is no need to do the calculations again .. just bring the results from the existing

run the above code and have a look at a(7), you will find it empty as it is already existed before
What I need for a(7) for example [This is a duplicate one] >> So I need to take the results in a(7,3) to a(7,11) from existing item
The exisitng item or the first instance of 20/04/2009 and "N" was in a(6)
So I would like to fill a(7,3) to a(7,11) with the same values in a(6,3) to a(6,11)

Posted at this link too

[MickG]

I'm not sure exactly what the results should be, but if you store the row number "i" as the item, instead of the key then you can use that row as the Datum for copying down, based on each "Key", as shown in the "Else" statement.
Try the code below for results on sheet2.

Sub MG20Jul58
Dim a, dic As Object, s As String, i As Long, j As Long, col As Variant, Ray, n As Long
   
    a = ThisWorkbook.Worksheets("Sheet5").Range("A1").CurrentRegion.Value
    a = Application.Index(a, Evaluate("ROW(1:" & UBound(a, 1) & ")"), [{3,7,13,14,15,16,17,18,19,20,21}])
   
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1

    For i = LBound(a) To UBound(a)
        s = a(i, 1) & vbTab & a(i, 2)
        If Not dic.Exists(s) Then
            dic.Add s, i
           
            For j = LBound(a, 2) To UBound(a, 2)
                If j = 1 Or j = 3 Or j = 6 Or j = 9 Then
                    If i > 1 Then a(i, j) = CLng(Application.WorksheetFunction.WorkDay(CDate(a(i, 1)), IIf(j = 1, 0, Val(j / 3))))
                End If
            Next j
        Else
            
            For n = 1 To UBound(a, 2)
                a(i, n) = a(dic(s), n)
            Next n
        End If
    Next i

Sheets("Sheet2").Range("A1").Resize(UBound(a, 1), UBound(a, 2)).Value = a

Regards Mick

[YasserKhalil]

That's great Mr. Mick
I got your point and I learned something new

Thank you very much

** What if I need to populate the data to the same sheet ("Sheet1") which has the raw data but I don't want to touch the columns from A to L ..
I mean I need to populate the result of the array from the third column of the array to the last one and put those results in sheet1 in column M to U

Thanks a lot for great help

[YasserKhalil]

Thanks a lot (it seems I can't think well because I didn't sleep well)
What I have asked from last reply is too simple but I am not in a good mood

    a = Application.Index(a, Evaluate("ROW(1:" & UBound(a, 1) & ")"), [{3,4,5,6,7,8,9,10,11}])
    Sheets("Sheet1").Range("M1").Resize(UBound(a, 1), UBound(a, 2)).Value = a

Thank you very much for awesome help