Need help on this.....

[Sakthikumar Chinnasamy]

Hi,

In the attached excel sheet, there are two sheets are there "Consolidated" & "Capacity approved" sheet.

output require:

In the capacity approved sheet - If the Status is"Approved" and date is "Today date", then i need to get the value of either B2 or C2 and do the vlookup of F value with Consolidated sheet of C2 , and provide the output of B2 or C2 value in J2 of "consolidated" Sheet.

 

[jindon]

If this is not how you wanted, you need to show your desired result.

Sub test()
    Dim a, b, i As Long, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    a = Sheets("capacity approved").Cells(1).CurrentRegion.Value
    For i = 2 To UBound(a, 1)
        If (a(i, 8) = "Approved") * (a(i, 9) = Date) Then dic(a(i, 6)) = _
        dic(a(i, 6)) & IIf(dic(a(i, 6)) <> "", ", ", "") & a(i, 2) & a(i, 3)
    Next
    With Sheets("consolidated").Cells(1).CurrentRegion
        a = .Columns("c").Value
        b = .Columns("j").Value
        For i = 2 To UBound(a, 1)
            b(i, 1) = dic(a(i, 1))
        Next
        .Columns("j").Value = b
    End With
End Sub

 

[Sakthikumar Chinnasamy]

Thank you Jindon. This is exactly i want. But instead of output value by separating comma, i want to add those value.:

for eg:

for location DC - Philadelphia (FS) - Output of approved capacity is 2350, 135. i want to add this value and show the approved capacity as "2485

 

[jindon]

Then change

   For i = 2 ToUBound(a, 1)
       If (a(i, 8) = "Approved") * (a(i, 9) = Date) Then dic(a(i, 6)) = _
        dic(a(i, 6)) & IIf(dic(a(i, 6)) <> "", ", ", "") & a(i, 2) & a(i, 3)
   Next

to

    For i = 2 To UBound(a, 1)
        If (a(i, 8) = "Approved") * (a(i, 9) = Date) Then _
        dic(a(i, 6)) = dic(a(i, 6)) + a(i, 2) + a(i, 3)
    Next