Excel VBA Compare Data And Create Report

K

kashif.special2005

Active Member
Joined
Oct 26, 2009
Messages
443
Hi,

I have two sheets with data.

Note:- Data in both the sheet is around 90000, I am here showing only sample data for the task.

Sheet Name:- "Associate Tracker"

Sample Data:-

[TABLE="width: 480"]
<tbody>[TR]
[TD="class: xl65, width: 120"]Column - A[/TD]
[TD="class: xl65, width: 120"]Column - J[/TD]
[TD="class: xl65, width: 120"]Column - S[/TD]
[TD="class: xl65, width: 120"]Column - Z[/TD]
[/TR]
[TR]
[TD="class: xl66"]Associate Name[/TD]
[TD="class: xl66"]Calling Date[/TD]
[TD="class: xl66"]Confirmation[/TD]
[TD="class: xl66"]Location[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl67, align: right"]1/4/2019[/TD]
[TD="class: xl65"]Y[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl67, align: right"]1/4/2019[/TD]
[TD="class: xl65"]Y[/TD]
[TD="class: xl65"]Mars[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl67, align: right"]1/4/2019[/TD]
[TD="class: xl65"]Y[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl67, align: right"]1/4/2019[/TD]
[TD="class: xl65"]Y[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl67, align: right"]1/4/2019[/TD]
[TD="class: xl65"]N[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl67, align: right"]1/4/2019[/TD]
[TD="class: xl65"]N[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl67, align: right"]1/4/2019[/TD]
[TD="class: xl65"]N[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl67, align: right"]1/5/2019[/TD]
[TD="class: xl65"]Y[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl67, align: right"]1/5/2019[/TD]
[TD="class: xl65"]Y[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl67, align: right"]1/5/2019[/TD]
[TD="class: xl65"]Y[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl67, align: right"]1/5/2019[/TD]
[TD="class: xl65"]Y[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl67, align: right"]1/5/2019[/TD]
[TD="class: xl65"]N[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl67, align: right"]1/5/2019[/TD]
[TD="class: xl65"]N[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]C[/TD]
[TD="class: xl67, align: right"]1/4/2019[/TD]
[TD="class: xl65"]Y[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]C[/TD]
[TD="class: xl67, align: right"]1/4/2019[/TD]
[TD="class: xl65"]N[/TD]
[TD="class: xl65"]Mars[/TD]
[/TR]
[TR]
[TD="class: xl65"]C[/TD]
[TD="class: xl67, align: right"]1/4/2019[/TD]
[TD="class: xl65"]N[/TD]
[TD="class: xl65"]Mars[/TD]
[/TR]
[TR]
[TD="class: xl65"]C[/TD]
[TD="class: xl67, align: right"]1/4/2019[/TD]
[TD="class: xl65"]N[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl67, align: right"]1/4/2019[/TD]
[TD="class: xl65"]Y[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl67, align: right"]1/6/2019[/TD]
[TD="class: xl65"]Y[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]C[/TD]
[TD="class: xl67, align: right"]1/5/2019[/TD]
[TD="class: xl65"]Y[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]C[/TD]
[TD="class: xl67, align: right"]1/5/2019[/TD]
[TD="class: xl65"]Y[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
</tbody>[/TABLE]

Sheet Name:- "System Data"

Sample Data:-

[TABLE="width: 399"]
<tbody>[TR]
[TD="class: xl65, width: 133"]Column - B[/TD]
[TD="class: xl65, width: 133"]Column - P[/TD]
[TD="class: xl65, width: 133"]Column - AB[/TD]
[/TR]
[TR]
[TD="class: xl66"]Caller Name[/TD]
[TD="class: xl66"]Date[/TD]
[TD="class: xl66"]Area[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl67, align: right"]1/4/2019[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl67, align: right"]1/4/2019[/TD]
[TD="class: xl65"]Mars[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl67, align: right"]1/4/2019[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl67, align: right"]1/4/2019[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl67, align: right"]1/4/2019[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl67, align: right"]1/5/2019[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl67, align: right"]1/5/2019[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl67, align: right"]1/5/2019[/TD]
[TD="class: xl65"]Mars[/TD]
[/TR]
[TR]
[TD="class: xl65"]C[/TD]
[TD="class: xl67, align: right"]1/4/2019[/TD]
[TD="class: xl65"]Mars[/TD]
[/TR]
[TR]
[TD="class: xl65"]C[/TD]
[TD="class: xl67, align: right"]1/4/2019[/TD]
[TD="class: xl65"]Mars[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl67, align: right"]1/4/2019[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl67, align: right"]1/6/2019[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]C[/TD]
[TD="class: xl67, align: right"]1/5/2019[/TD]
[TD="class: xl65"]Mars[/TD]
[/TR]
[TR]
[TD="class: xl65"]Z[/TD]
[TD="class: xl67, align: right"]1/4/2019[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]Z[/TD]
[TD="class: xl67, align: right"]1/6/2019[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
[TR]
[TD="class: xl65"]Z[/TD]
[TD="class: xl67, align: right"]1/6/2019[/TD]
[TD="class: xl65"]Brooklyn[/TD]
[/TR]
</tbody>[/TABLE]

Now I want to create 1st report from the sheet "Associate Tracker", and the report will look like below.

Report from the sheet:- "Associate Tracker"

[TABLE="width: 279"]
<tbody>[TR]
[TD]Location[/TD]
[TD]Brooklyn[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Confirmation[/TD]
[TD]Y[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Associate Name[/TD]
[TD]Calling Date[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]1/4/2019[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]1/5/2019[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]1/4/2019[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]1/5/2019[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]1/6/2019[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]1/4/2019[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]1/5/2019[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[/TR]
</tbody>[/TABLE]


And I want to create another report from the sheet "System Data", and the report will look like below.

Report from the sheet:- "System Data"


[TABLE="width: 213"]
<tbody>[TR]
[TD]Area[/TD]
[TD]Brooklyn[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Caller Name[/TD]
[TD]Date[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]1/4/2019[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]1/5/2019[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]1/4/2019[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]1/6/2019[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD="align: right"]1/4/2019[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD="align: right"]1/6/2019[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[/TR]
</tbody>[/TABLE]

Now from these two report I want to create a final "Comparison Report", and the comparison report will look like below.

Camparison Report:-

[TABLE="width: 975"]
<tbody>[TR]
[TD]Associate Name[/TD]
[TD]Calling Date[/TD]
[TD]Associate Value[/TD]
[TD]System Value[/TD]
[TD]TRUE/FALSE (Associate Value = System Value)[/TD]
[TD]Differences (Associate Value - System Value)[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]1/4/2019[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]1/5/2019[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]1/4/2019[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]-3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]1/5/2019[/TD]
[TD="align: right"]1[/TD]
[TD]Not Exists[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Not Applicable[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]1/6/2019[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]1/4/2019[/TD]
[TD="align: right"]1[/TD]
[TD]Not Exists[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Not Applicable[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]1/5/2019[/TD]
[TD="align: right"]2[/TD]
[TD]Not Exists[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Not Applicable[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]8[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

Please help me to create these three report, I have not idea how to create these reports.

I saw some post from the user "MickG", he used Dictionary object to create reports, Can we create these types of reports from Dictionary or any other method, please help me to achieve this task.

Please help me to achieve this task through VBA, because data is huge.

Thanks in advance.

Thanks
Kashif
 
Last edited:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
Let's go, follow the following steps:
1. Create a sheet called "Reports"
2. Put the following code in the "Reports" sheet
3. To put the code in the "Reports" sheet, right click on the tab and in the menu select the option "View code"
4. Paste the following code

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'
'   Create Reports
'
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("B2:B3")) Is Nothing Then
        If Range("B2").Value = "" Or Range("B3").Value = "" Then Exit Sub
        '
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        Set h1 = Sheets("Associate Tracker")
        Set h2 = Sheets("System Data")
        Set h3 = Sheets("Reports")
        h3.Rows("7:" & Rows.Count).ClearContents
        '
        'Report 1
        '
        If h1.AutoFilterMode Then h1.AutoFilterMode = False
        u1 = h1.Range("Z" & Rows.Count).End(xlUp).Row
        h1.Range("A1:Z" & u1).AutoFilter Field:=26, Criteria1:=h3.Range("B2")
        h1.Range("A1:Z" & u1).AutoFilter Field:=19, Criteria1:=h3.Range("B3")
        h1.Range("A2:A" & u1).Copy h3.Range("A7")   'name
        h1.Range("J2:J" & u1).Copy h3.Range("B7")   'date
        '
        u3 = h3.Range("A" & Rows.Count).End(xlUp).Row
        With Range("C7:C" & u3)
            .FormulaR1C1 = "=COUNTIFS(R7C1:R" & u3 & "C1,RC[-2],R7C2:R" & u3 & "C2,RC[-1])"
            .Value = .Value
        End With
        h3.Range("A6:C" & u3).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
        u3 = h3.Range("A" & Rows.Count).End(xlUp).Row
        With h3.Sort
            .SortFields.Clear
            .SortFields.Add Key:=h3.Range("A7:A" & u3)
            .SortFields.Add Key:=h3.Range("B7:B" & u3)
            .SetRange h3.Range("A6:C" & u3): .Header = xlYes: .MatchCase = False
            .Orientation = xlTopToBottom: .SortMethod = xlPinYin: .Apply
        End With
        '
        'Report 2
        '
        If h2.AutoFilterMode Then h2.AutoFilterMode = False
        u2 = h2.Range("AB" & Rows.Count).End(xlUp).Row
        h2.Range("A1:AB" & u2).AutoFilter Field:=28, Criteria1:=h3.Range("B2")
        h2.Range("B2:B" & u2).Copy h3.Range("E7")   'name
        h2.Range("P2:P" & u1).Copy h3.Range("F7")   'date
        '
        u3 = h3.Range("E" & Rows.Count).End(xlUp).Row
        With Range("G7:G" & u3)
            .FormulaR1C1 = "=COUNTIFS(R7C5:R" & u3 & "C5,RC[-2],R7C6:R" & u3 & "C6,RC[-1])"
            .Value = .Value
        End With
        h3.Range("E6:G" & u3).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
        u3 = h3.Range("E" & Rows.Count).End(xlUp).Row
        With h3.Sort
            .SortFields.Clear
            .SortFields.Add Key:=h3.Range("E7:E" & u3)
            .SortFields.Add Key:=h3.Range("F7:F" & u3)
            .SetRange h3.Range("E6:G" & u3): .Header = xlYes: .MatchCase = False
            .Orientation = xlTopToBottom: .SortMethod = xlPinYin: .Apply
        End With
        '
        'Report 3
        '
        u3 = h3.Range("A" & Rows.Count).End(xlUp).Row
        h3.Range("A7:C" & u3).Copy h3.Range("I7")
        u3 = h3.Range("E" & Rows.Count).End(xlUp).Row
        For i = 7 To u3
            Set r = h3.Columns("I")
            Set b = r.Find(h3.Cells(i, "E"), LookAt:=xlWhole)
            existe = False
            If Not b Is Nothing Then
                celda = b.Address
                Do
                    'detalle
                    If h3.Cells(b.Row, "J").Value = h3.Cells(i, "F").Value Then
                        h3.Cells(b.Row, "L").Value = h3.Cells(i, "G").Value
                        existe = True
                        Exit Do
                    End If
                    Set b = r.FindNext(b)
                Loop While Not b Is Nothing And b.Address <> celda
            End If
            If existe = False Then
                u4 = h3.Range("I" & Rows.Count).End(xlUp).Row + 1
                h3.Range("E" & i & ":F" & i).Copy h3.Range("I" & u4)
                h3.Range("G" & i & ":G" & i).Copy h3.Range("L" & u4)
            End If
        Next
        '
        u3 = h3.Range("I" & Rows.Count).End(xlUp).Row
        With h3.Sort
            .SortFields.Clear
            .SortFields.Add Key:=h3.Range("I7:I" & u3)
            .SortFields.Add Key:=h3.Range("J7:J" & u3)
            .SetRange h3.Range("I6:N" & u3): .Header = xlYes: .MatchCase = False
            .Orientation = xlTopToBottom: .SortMethod = xlPinYin: .Apply
        End With
        On Error Resume Next
        h3.Range("K7:L" & u3).SpecialCells(xlCellTypeBlanks) = "Not Exists"
        On Error GoTo 0
        h3.Range("I" & u3 + 1).Value = "Grand Total"
        h3.Range("K" & u3 + 1).Value = WorksheetFunction.Sum(h3.Range("K7:K" & u3))
        h3.Range("L" & u3 + 1).Value = WorksheetFunction.Sum(h3.Range("L7:L" & u3))
        With h3.Range("M7:M" & u3 + 1)
            .FormulaR1C1 = "=IF(RC[-2]=RC[-1],""TRUE"",""FALSE"")"
            .Value = .Value
        End With
        With h3.Range("N7:N" & u3 + 1)
            .FormulaR1C1 = "=IF(OR(RC[-3]=""Not Exists"",RC[-2]=""Not Exists""),""Not Applicable"",RC[-3]-RC[-2])"
            .Value = .Value
        End With
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End If
End Sub
'
Private Sub Worksheet_Activate()
'
'   Create Data Validation on cell B2 and B3
'
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Set h1 = Sheets("Associate Tracker")
    Set h2 = Sheets("System Data")
    Set h3 = Sheets("Reports")
    'h3.Rows("7:" & Rows.Count).ClearContents
    If h1.AutoFilterMode Then h1.AutoFilterMode = False
    If h2.AutoFilterMode Then h2.AutoFilterMode = False
    u1 = h1.Range("Z" & Rows.Count).End(xlUp).Row
    h1.Range("Z1:Z" & u1).Copy
    h3.Range("Z1").PasteSpecial Paste:=xlPasteValues
    u3 = h3.Range("Z" & Rows.Count).End(xlUp).Row
    h3.Range("Z1:Z" & u3).RemoveDuplicates Columns:=1, Header:=xlYes
    u3 = h3.Range("Z" & Rows.Count).End(xlUp).Row
    With h3.Range("B2").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:="=Z2:Z" & u3
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    h3.Range("AA2").Value = "Y"
    h3.Range("AA3").Value = "N"
    With h3.Range("B3").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:="=AA2:AA3"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Range("B2").Select
    Application.EnableEvents = True
End Sub


On the "Reports" sheet, put the following structure:

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Location[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Confirmation[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Report 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Report 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Report 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Value[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Value[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]A Value[/TD]
[TD="align: center"]S Value[/TD]
[TD="align: center"]True / False[/TD]
[TD="align: center"]Diff[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




It works as follows, select one of your sheets, now select the "Reports" sheet, the locations in cell B2 are loaded automatically. In cell B3 "Y" and "N" are loaded.
Now, select a location and confirmation. In automatic, the 3 reports are generated.

See image:
https://www.dropbox.com/s/jr16jj5bobi259a/reports.jpg?dl=0

Regards Dante Amor
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Calculate if staff are still enrolled in tasks
Replies
2
Views
76
tigerzen
T
S
  • Solved
Web Query in VBA Loop through tables on page to choose get tags from table instead of text
2
Replies
13
Views
608
Domenic
D
O
  • Solved
Unable to download html data from specific site
Replies
7
Views
486
Edgar_
Edgar_
E
  • Solved
VlookUp multiple same value ?
Replies
5
Views
399
Sufiyan97
Sufiyan97
A
  • Solved
Could not set the RowSource property. Invalid property value. Error 380
2
Replies
16
Views
210
RoryA
RoryA

Forum statistics

Threads
1,223,909
Messages
6,175,310
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top