VBA - Filling Master Template

[malleshg24]

Hi Team,


Need your help for filling Master Template, Data is in Data Tab of attached workbook.
Currently Row third of Master Template I have filled with excel formula.


Can you plz suggest any other method for achieving the same task. like array,collection or dictionary.etc.


Thanks in advance for your help.




Regards,
mg

[Paul_Hossler]

This is tied in with the worksheet activate event so that it always has the latest Data. It can also be a stand-alone sub

There's some more polishing that can be added, but I didn't go too far in case this isn't what you wanted



Option Explicit


Sub UpdateMaster()
Dim vData As Variant
Dim rTemplate As Range
Dim i As Long, r As Long, c As Long
Dim sStatus As String, sProduct As String, sSE As String

Set rTemplate = Cells(1, 1).CurrentRegion


vData = Worksheets("Data").Cells(1, 1).CurrentRegion.Value
For r = LBound(vData, 1) To UBound(vData, 1)
For c = LBound(vData, 2) To UBound(vData, 2)
vData(r, c) = UCase(vData(r, c))
Next c

If vData(r, 2) = "PENDING" Then vData(r, 2) = "WORK IN PROGRESS" ' inconsistent
Next r


With rTemplate
For r = 3 To .Rows.Count

.Cells(r, .Columns.Count).Value = 0
.Cells(r, .Columns.Count - 1).Value = 0

For c = 2 To .Columns.Count - 2
sStatus = UCase(.Cells(r, 1).Value)
sProduct = UCase(.Cells(1, c).Value)
If Len(sProduct) = 0 Then sProduct = UCase(.Cells(1, c - 1).Value) ' merged cells
sSE = .Cells(2, c).Value

.Cells(r, c).Value = 0




For i = LBound(vData, 1) + 1 To UBound(vData, 1)
If vData(i, 1) = sProduct And vData(i, 2) = sStatus Then
If sSE = "SOD" Then
If InStr(vData(i, 3), "PEN") > 0 Then
.Cells(r, c).Value = .Cells(r, c).Value + 1
.Cells(r, .Columns.Count - 1).Value = .Cells(r, .Columns.Count - 1).Value + 1
End If
ElseIf sSE = "EOD" Then
If InStr(vData(i, 3), "PEN") = 0 Then
.Cells(r, c).Value = .Cells(r, c).Value + 1
.Cells(r, .Columns.Count).Value = .Cells(r, .Columns.Count).Value + 1
End If
End If
End If
Next i
Next c
Next r
End With






End Sub

[SamT]

Two Formulas for the entire sheet. These go in B3 and C3
=COUNTIFS(Data!$A:$A,B$1,Data!$B:$B,$A3,Data!$C:$C,SOD)
=COUNTIFS(Data!$A:$A,B$1,Data!$B:$B,$A3,Data!$C:$C,EOD)

Copy Cells B3 and C3 and paste as appropriate across the table. Then select Rows 3 thru 6 of the table and press Ctrl+D

What is "SOD" and "EOD" in those formulas? Well, SOD and EOD aren't on the Data sheet, so I created two Named Formulas, SOD = "Penxxx" and EOD="Penxxx".

If you create three Names (Product ,Status, Comments)on the Data sheet, the formulas would read
=COUNTIFS(Product,B$1,Status,$A3,Comments,SOD)
=COUNTIFS(Product,B$1,Status,$A3,Comments,EOD)

What this means:


Named Ranges can be Cut and Inserted without affecting the formulas that use them.
You can edit a Named Formula and it will affect all Formulas that use that Name
If you Insert new columns into the Master Template Table, you can add the Named Formula X="xxxx", copy the old formulas, then just change "SOD" to "X"
If you change a word in the Data sheet, you only have to edit the Named Formula, not all the sheet Formulas

[malleshg24]

Hi Paul and Sam,

Thanks for your help. I have modified little as per your code and its working.

Regards,
mg