Compare date in multiple spreadsheet and provide summary of match/mismatch in another sheet

K

kalpesh505ahd

New Member
Joined
Sep 6, 2015
Messages
2
Dear All,
I am new to excel VBA and need to find solution for the issue I am struggling with. In one file, there are 5 input spreadsheet and one output spreadsheet.
(1) RAVE - input spreadsheet
[TABLE="width: 259"]
<tbody>[TR]
[TD]Subject
[/TD]
[TD]Visit
[/TD]
[TD]Date
[/TD]
[TD]Date
[/TD]
[/TR]
[TR]
[TD]30001
[/TD]
[TD]Week 0
[/TD]
[TD]01-Jan-15
[/TD]
[TD]01-Jan-15
[/TD]
[/TR]
[TR]
[TD]30001
[/TD]
[TD]Week 4
[/TD]
[TD]01-Feb-15
[/TD]
[TD]01-Feb-15
[/TD]
[/TR]
[TR]
[TD]30001
[/TD]
[TD]Week 8
[/TD]
[TD]01-Mar-15
[/TD]
[TD]01-Mar-15
[/TD]
[/TR]
[TR]
[TD]30002
[/TD]
[TD]Week 0
[/TD]
[TD]01-Apr-15
[/TD]
[TD]01-Apr-15
[/TD]
[/TR]
[TR]
[TD]30002
[/TD]
[TD]Week 4
[/TD]
[TD]01-May-15
[/TD]
[TD]01-May-15
[/TD]
[/TR]
[TR]
[TD]30002
[/TD]
[TD]Week 8
[/TD]
[TD]01-Jun-16
[/TD]
[TD]01-Jun-16
[/TD]
[/TR]
</tbody>[/TABLE]

(2) LB - input spreadsheet
[TABLE="width: 261"]
<tbody>[TR]
[TD]Subject
[/TD]
[TD]Visit
[/TD]
[TD]Date
[/TD]
[TD]Date
[/TD]
[/TR]
[TR]
[TD]30001
[/TD]
[TD]Week 0
[/TD]
[TD]01-Jan-15
[/TD]
[TD]01-Jan-15
[/TD]
[/TR]
[TR]
[TD]30001
[/TD]
[TD]Week 4
[/TD]
[TD]01-Feb-15
[/TD]
[TD]01-Feb-15
[/TD]
[/TR]
[TR]
[TD]30001
[/TD]
[TD]Week 8
[/TD]
[TD]01-Mar-15
[/TD]
[TD]01-Mar-15
[/TD]
[/TR]
[TR]
[TD]30002
[/TD]
[TD]Week 0
[/TD]
[TD]01-Jan-15
[/TD]
[TD]01-Jan-15
[/TD]
[/TR]
[TR]
[TD]30002
[/TD]
[TD]Week 4
[/TD]
[TD]01-Feb-15
[/TD]
[TD]01-Feb-15
[/TD]
[/TR]
[TR]
[TD]30002
[/TD]
[TD]Week 8
[/TD]
[TD]01-Mar-15
[/TD]
[TD]01-Mar-15
[/TD]
[/TR]
</tbody>[/TABLE]

(3) ST - Input spreadsheet
[TABLE="width: 269"]
<tbody>[TR]
[TD]Subject
[/TD]
[TD]Visit
[/TD]
[TD]Date
[/TD]
[TD]Date
[/TD]
[/TR]
[TR]
[TD]30001
[/TD]
[TD]Week 0
[/TD]
[TD]01-Apr-15
[/TD]
[TD]01-Apr-15
[/TD]
[/TR]
[TR]
[TD]30001
[/TD]
[TD]Week 4
[/TD]
[TD]01-May-15
[/TD]
[TD]01-May-15
[/TD]
[/TR]
[TR]
[TD]30001
[/TD]
[TD]Week 8
[/TD]
[TD]01-Jun-16
[/TD]
[TD]01-Jun-16
[/TD]
[/TR]
[TR]
[TD]30002
[/TD]
[TD]Week 0
[/TD]
[TD]01-Jan-15
[/TD]
[TD]01-Jan-15
[/TD]
[/TR]
[TR]
[TD]30002
[/TD]
[TD]Week 4
[/TD]
[TD]01-Feb-15
[/TD]
[TD]01-Feb-15
[/TD]
[/TR]
[TR]
[TD]30002
[/TD]
[TD]Week 8
[/TD]
[TD]01-Mar-15
[/TD]
[TD]01-Mar-15
[/TD]
[/TR]
</tbody>[/TABLE]

(4) ZR - input spreadsheet
[TABLE="width: 269"]
<tbody>[TR]
[TD]Subject
[/TD]
[TD]Visit
[/TD]
[TD]Date
[/TD]
[TD]Date
[/TD]
[/TR]
[TR]
[TD]30001
[/TD]
[TD]Week 8
[/TD]
[TD]01-Apr-15
[/TD]
[TD]01-Apr-15
[/TD]
[/TR]
[TR]
[TD]30001
[/TD]
[TD]Week 4
[/TD]
[TD]01-May-15
[/TD]
[TD]01-May-15
[/TD]
[/TR]
[TR]
[TD]30001
[/TD]
[TD]Week 0
[/TD]
[TD]01-Jun-16
[/TD]
[TD]01-Jun-16
[/TD]
[/TR]
[TR]
[TD]30002
[/TD]
[TD]Week 0
[/TD]
[TD]01-Jan-15
[/TD]
[TD]01-Jan-15
[/TD]
[/TR]
[TR]
[TD]30002
[/TD]
[TD]Week 4
[/TD]
[TD]01-Feb-15
[/TD]
[TD]01-Feb-15
[/TD]
[/TR]
[TR]
[TD]30002
[/TD]
[TD]Week 8
[/TD]
[TD]01-Mar-15
[/TD]
[TD]01-Mar-15
[/TD]
[/TR]
</tbody>[/TABLE]

(5) EG - input spreadsheet

[TABLE="width: 269"]
<tbody>[TR]
[TD]Subject
[/TD]
[TD]Visit
[/TD]
[TD]Date
[/TD]
[TD]Date
[/TD]
[/TR]
[TR]
[TD]30001
[/TD]
[TD]Week 0
[/TD]
[TD]01-Apr-15
[/TD]
[TD]01-Apr-15
[/TD]
[/TR]
[TR]
[TD]30001
[/TD]
[TD]Week 4
[/TD]
[TD]01-May-15
[/TD]
[TD]01-May-15
[/TD]
[/TR]
[TR]
[TD]30001
[/TD]
[TD]Week 8
[/TD]
[TD]01-Jun-16
[/TD]
[TD]01-Jun-16
[/TD]
[/TR]
[TR]
[TD]30002
[/TD]
[TD]Week 8
[/TD]
[TD]01-Jan-15
[/TD]
[TD]01-Jan-15
[/TD]
[/TR]
[TR]
[TD]30002
[/TD]
[TD]Week 4
[/TD]
[TD]01-Feb-15
[/TD]
[TD]01-Feb-15
[/TD]
[/TR]
[TR]
[TD]30002
[/TD]
[TD]Week 0
[/TD]
[TD]01-Mar-15
[/TD]
[TD]01-Mar-15
[/TD]
[/TR]
</tbody>[/TABLE]

(6) ePRO -Input spreadsheet
[TABLE="width: 259"]
<tbody>[TR]
[TD]Subject
[/TD]
[TD]Visit
[/TD]
[TD]Date
[/TD]
[TD]Date
[/TD]
[/TR]
[TR]
[TD]30001
[/TD]
[TD]Week 0
[/TD]
[TD]01-Apr-15
[/TD]
[TD]01-Apr-15
[/TD]
[/TR]
[TR]
[TD]30001
[/TD]
[TD]Week 4
[/TD]
[TD]01-May-15
[/TD]
[TD]01-May-15
[/TD]
[/TR]
[TR]
[TD]30001
[/TD]
[TD]Week 8
[/TD]
[TD]01-Jun-16
[/TD]
[TD]01-Jun-16
[/TD]
[/TR]
[TR]
[TD]30002
[/TD]
[TD]Week 8
[/TD]
[TD]01-Jan-15
[/TD]
[TD]01-Jan-15
[/TD]
[/TR]
[TR]
[TD]30002
[/TD]
[TD]Week 4
[/TD]
[TD]01-Feb-15
[/TD]
[TD]01-Feb-15
[/TD]
[/TR]
[TR]
[TD]30002
[/TD]
[TD]Week 0
[/TD]
[TD]01-Mar-15
[/TD]
[TD]01-Mar-15
[/TD]
[/TR]
</tbody>[/TABLE]

(7) Output - spreadsheet
[TABLE="width: 835"]
<tbody>[TR]
[TD]Subject
[/TD]
[TD]Visit
[/TD]
[TD]RAVE
[/TD]
[TD]LB
[/TD]
[TD]ST
[/TD]
[TD]ZR
[/TD]
[TD]EG
[/TD]
[TD]ePRO
[/TD]
[TD]Mismatch against RAVE date
[/TD]
[/TR]
[TR]
[TD]30001
[/TD]
[TD]Week 0
[/TD]
[TD]01-Jan-2015
[/TD]
[TD]01-Jan-2015
[/TD]
[TD]01-Apr-2015
[/TD]
[TD]01-Jun-2016
[/TD]
[TD]01-Apr-2015
[/TD]
[TD]01-Apr-2015
[/TD]
[TD]LB, ZR, EG, ePRO
[/TD]
[/TR]
[TR]
[TD]30001
[/TD]
[TD]Week 4
[/TD]
[TD]01-Feb-2015
[/TD]
[TD]01-Feb-2015
[/TD]
[TD]01-May-2015
[/TD]
[TD]01-May-2015
[/TD]
[TD]01-May-2015
[/TD]
[TD]01-May-2015
[/TD]
[TD]ST, ZR, EG, ePRO
[/TD]
[/TR]
[TR]
[TD]30001
[/TD]
[TD]Week 8
[/TD]
[TD]01-Mar-2015
[/TD]
[TD]01-Mar-2015
[/TD]
[TD]01-Jun-2016
[/TD]
[TD]01-Apr-2015
[/TD]
[TD]01-Jun-2016
[/TD]
[TD]01-Jun-2016
[/TD]
[TD]ST, ZR, EG, ePRO
[/TD]
[/TR]
[TR]
[TD]30002
[/TD]
[TD]Week 0
[/TD]
[TD]01-Apr-2015
[/TD]
[TD]01-Jan-2015
[/TD]
[TD]01-Jan-2015
[/TD]
[TD]01-Jan-2015
[/TD]
[TD]01-Mar-2015
[/TD]
[TD]01-Mar-2015
[/TD]
[TD]LB, ST, ZR, EG, ePRO
[/TD]
[/TR]
[TR]
[TD]30002
[/TD]
[TD]Week 4
[/TD]
[TD]01-May-2015
[/TD]
[TD]01-Feb-2015
[/TD]
[TD]01-Feb-2015
[/TD]
[TD]01-Feb-2015
[/TD]
[TD]01-Feb-2015
[/TD]
[TD]01-Feb-2015
[/TD]
[TD]LB, ST, ZR, EG, ePRO
[/TD]
[/TR]
[TR]
[TD]30002
[/TD]
[TD]Week 8
[/TD]
[TD]01-Jun-2016
[/TD]
[TD]01-Mar-2015
[/TD]
[TD]01-Mar-2015
[/TD]
[TD]01-Mar-2015
[/TD]
[TD]01-Jan-2015
[/TD]
[TD]01-Jan-2015
[/TD]
[TD]LB, ST, ZR, EG, ePRO
[/TD]
[/TR]
</tbody>[/TABLE]

I am using excel formula concatenate (Subject&Visit) and VLOOKUP to get the date from Input spreadsheet to Output spreadsheet. however data in Output - Mismatch against RAVE data is added manually.

I need help creating macro which can generate the output spreadsheet automatically. Is it possible?

Regards,
Kalpesh
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Try this two part solution:

VBA to scan the input worksheets for the values and transfer them to your output sheet:

Code: 
Sub x10()
    
Dim Subject As Integer
Dim Visit As String
Dim Val As String
Dim Subjectx As Integer
Dim Visitx As String



Application.EnableEvents = False
Application.ScreenUpdating = False

Sheets("Output").Select

For Each x In Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
    Subject = Cells(x.Row, 1)
    Visit = Cells(x.Row, 2)
    If Subject = Empty Then
    Else
        Sheets("RAVE").Select
        For Each y In Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
            Subjectx = Cells(y.Row, 1)
            Visitx = Cells(y.Row, 2)
            If Subjectx <> Subject Then
            Else
                If Visitx <> Visit Then
                Else
                    Val = Cells(y.Row, 3)
                End If
            End If
        Next
    Sheets("Output").Select
    Cells(x.Row, 3).Value = Val
        Sheets("LB").Select
        For Each y In Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
            Subjectx = Cells(y.Row, 1)
            Visitx = Cells(y.Row, 2)
            If Subjectx <> Subject Then
            Else
                If Visitx <> Visit Then
                Else
                    Val = Cells(y.Row, 3)
                End If
            End If
        Next
    Sheets("Output").Select
    Cells(x.Row, 4).Value = Val
    
        Sheets("ST").Select
        For Each y In Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
            Subjectx = Cells(y.Row, 1)
            Visitx = Cells(y.Row, 2)
            If Subjectx <> Subject Then
            Else
                If Visitx <> Visit Then
                Else
                    Val = Cells(y.Row, 3)
                End If
            End If
        Next
    Sheets("Output").Select
    Cells(x.Row, 5).Value = Val
    
        Sheets("ZR").Select
        For Each y In Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
            Subjectx = Cells(y.Row, 1)
            Visitx = Cells(y.Row, 2)
            If Subjectx <> Subject Then
            Else
                If Visitx <> Visit Then
                Else
                    Val = Cells(y.Row, 3)
                End If
            End If
        Next
    Sheets("Output").Select
    Cells(x.Row, 6).Value = Val
    
        Sheets("EG").Select
        For Each y In Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
            Subjectx = Cells(y.Row, 1)
            Visitx = Cells(y.Row, 2)
            If Subjectx <> Subject Then
            Else
                If Visitx <> Visit Then
                Else
                    Val = Cells(y.Row, 3)
                End If
            End If
        Next
    Sheets("Output").Select
    Cells(x.Row, 7).Value = Val
    
        Sheets("ePRO").Select
        For Each y In Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
            Subjectx = Cells(y.Row, 1)
            Visitx = Cells(y.Row, 2)
            If Subjectx <> Subject Then
            Else
                If Visitx <> Visit Then
                Else
                    Val = Cells(y.Row, 3)
                End If
            End If
        Next
    Sheets("Output").Select
    Cells(x.Row, 8).Value = Val
    
    End If
Next



Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub


Then enter this formula into your mismatch column, and drag the formula down as needed:

=IF(ISNA(MATCH(C2,D2,0)),"LB, ","") & IF(ISNA(MATCH(C2,E2,0)),"ST, ","") & IF(ISNA(MATCH(C2,F2,0)),"ZR, ","") & IF(ISNA(MATCH(C2,G2,0)),"EG, ","") & IF(ISNA(MATCH(C2,H2,0)),"ePRO","")
 
Upvote 0
Dear Kuljack,

Thank you for your prompt response. The excel formula is working fine. However VBA code is not generating any output nor throwing any error message.

Regards,
Kalpesh Patel
 
Upvote 0
Interesting...

I am guessing that your output sheet is blank?

This was written under the impression that the output sheet would have some sort of template of the Subject/Week outcomes built out. Something like the following:

Subject Week Rave LB ...
3001 0001 (blank) (blank) ...
3001 0002 (blank) (blank) ...
3001 0003 (blank) (blank) ...
3001 0004 (blank) (blank) ...
3002 0001 (blank) (blank) ...
3002 0002 (blank) (blank) ...
3002 0003 (blank) (blank) ...
3002 0004 (blank) (blank) ...

And the macro could reference these premade sets for values in the other sheets, populating the (blank) values accordingly. If the output sheet is completely blank than technically it's doing what it's supposed to.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
2K
phiero21
P
S
  • Question Question
Merge mail on multiple section
Replies
0
Views
233
Salehin31
S
skyh3ck
  • Question Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
546
skyh3ck
skyh3ck
ricafonyat
  • Solved
Valid Prices
Replies
1
Views
86
DanteAmor
DanteAmor
staticfluids
  • Solved
Dynamic Array Formula (FILTER) But Show the Other Criteria with Blank
Replies
4
Views
497
staticfluids
staticfluids

Forum statistics

Threads
1,223,901
Messages
6,175,277
Members
452,629
Latest member
SahilPolekar

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