Verify two sets of data to find mistakes

S

shekhar_pc

Board Regular
Joined
Jan 29, 2006
Messages
185
I have four files "EMPLOYEE A" "EMPLOYEE B" "EMPLOYEE C" AND "EMPLOYEE D" and a master file "MasterJun07"

I want to compare the numbers of each file with the master file.
For instance:
In the file "EMPLOYEE A.XLS", on 1st Jun, the numbers are 456 and 457, on 2nd june the numbers are 457 376 and 376 and if you see the master file for Employee A (in 3rd row) on 1st june, we have 456/457 and on second we have 457/376 376 that means the data is correct.

I want to re-check each and every record of the master file in each and every file. This is a tedious job that I have to do thrice a month for 250+ employees manually. Any help would be appreciated.

We just have to check the rows where there is an employee. Blank cells and cells with contents "JR" and "SR" should be ignored.

I am posting the files in my next post. In the example given, only 3rd 6th 9th and 14th row in the Master file needs to be checked.

For every employee name in the master file, there will be a different file name with the same employee name in the folder.
All files are kept in the folder "C:\data"
If there is a discrepancy, between the data mentioned in the master file and the one given in the employee file, the background cell color of the master file should change to red and font color to white and the cell address of the incorrect data should be copied into "Error" sheet in the master file to track the mistakes.

The data what I provided is a sample data. In real, I have 250+ files and the data that needs to be checked is for the entire month till 30th or 31st
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
MasterJun07.xls
ABCDEFGHI
1
2DAY1-Jun2-Jun3-Jun4-Jun5-Jun6-Jun7-Jun8-Jun
3EMPLOYEE AREP 16:05 456/457 BOM-DEL-BOM 1605 2115 XXX-456-FLIGHT ATTENDENT XXX-457-FLIGHT ATTENDENTREP 22:10 457/376 376 BOM-MAA-HYD HYD-BOM 2210 0315 XXX-457-FLIGHT ATTENDENT XXX-376-FLIGHT ATTENDENT XXX-376-FLIGHT ATTENDENTL\A - BOM 0000 2359REP 20:20 103/103 153 BOM-AMD-DEL DEL-BOM 2020 0200 XXX-103-FLIGHT ATTENDENT XXX-103-FLIGHT ATTENDENT XXX-153-FLIGHT ATTENDENTL\A - BOM 0000 2359REP 16:00 456/457 BOM-DEL-BOM 1600 2105 XXX-456-FLIGHT ATTENDENT XXX-457-FLIGHT ATTENDENTOFFREP 06:15 371/371 352 BOM-HYD-MAA MAA-BOM 0615 1155 XXX-371-FLIGHT ATTENDENT XXX-371-FLIGHT ATTENDENT XXX-352-FLIGHT ATTENDENT
4
5JR
6EMPLOYEE BL\A - BOM 0000 2359REP 23:05 452 BOM-DEL 2305 0105 XXX-452-FLIGHT ATTENDENTREP 04:50 451 DEL-BOM 0450 0650 XXX-451-FLIGHT ATTENDENTOFFREP 06:30 371/371 352 BOM-HYD-MAA MAA-BOM 0630 1215 XXX-371-FLIGHT ATTENDENT XXX-371-FLIGHT ATTENDENT XXX-352-FLIGHT ATTENDENTREP 08:10 951/956 956 BOM-COK-BLR BLR-BOM 0810 1415 XXX-951-FLIGHT ATTENDENT XXX-956-FLIGHT ATTENDENT XXX-956-FLIGHT ATTENDENTREP 21:30 457/376 376 BOM-MAA-HYD HYD-BOM 2130 0325 XXX-457-FLIGHT ATTENDENT XXX-376-FLIGHT ATTENDENT XXX-376-FLIGHT ATTENDENTL\A - BOM 0000 2359
7
8JR
9EMPLOYEE CREP 20:30 103/103 153 BOM-AMD-DEL DEL-BOM 2030 0240 XXX-103-PURSER XXX-103-INFLIGHT MANAGERAVB EREP 12:00 352/104 104 BOM-DEL-AMD AMD-BOM 1300 1900REP 18:35 106/112 112 BOM-DEL-JAI JAI-BOM 1835 0030AVB AOFFREP 06:50 602/602 602 BOM-SXR-IXJ IXJ-BOM 0650 1355AVB A
10XXX-153-PURSERXXX-352-PURSERXXX-106-PURSERXXX-602-PURSER
11SRXXX-104-PURSERXXX-112-PURSERXXX-602-PURSER
12XXX-104-PURSERXXX-112-PURSERXXX-602-PURSER
13
14EMPLOYEE DREP 09:25 152/151 151 DEL-SXR-IXJ IXJ-DEL 0925 1400 XXX-152-INFLIGHT MANAGER XXX-151-INFLIGHT MANAGER XXX-151-INFLIGHT MANAGERAVB AREP 14:10 162 DEL-BOM 1410 1610 XXX-162-FLIGHT ATTENDENTAVB AREP 08:50 652/151 151 DEL-IXJ-SXR SXR-DEL 0850 1305 XXX-652-FLIGHT ATTENDENT XXX-151-FLIGHT ATTENDENT XXX-151-FLIGHT ATTENDENTREP 13:35 162 DEL-BOM 1335 1540 XXX-162-FLIGHT ATTENDENTOFFREP 06:00 152 BOM-DEL 0600 0755 XXX-152-FLIGHT ATTENDENT
15
16JR
Sample
 
Upvote 0
EMPLOYEE A
EMPLOYEE A.xls
ABCDEFGHIJKL
5Name :EMPLOYEE A
6
7Date
8
901-Jun-2007XXX-456BOM-DEL
1001-Jun-2007XXX-457DEL-BOM
1102-Jun-2007XXX-457BOM-MAA
1202-Jun-2007XXX-376MAA-HYD
1302-Jun-2007XXX-376HYD-BOM
1404-Jun-2007XXX-103BOM-AMD
1504-Jun-2007XXX-103AMD-DEL
1604-Jun-2007XXX-153DEL-BOM
1706-Jun-2007XXX-456BOM-DEL
1806-Jun-2007XXX-457DEL-BOM
1908-Jun-2007XXX-371BOM-HYD
2008-Jun-2007XXX-371HYD-MAA
2108-Jun-2007XXX-352MAA-BOM
Sheet1
 
Upvote 0
EMPLOYEE B
EMPLOYEE B.xls
ABCDEFGHIJKL
5Name :EMPLOYEE B
6
7Date
8
902-Jun-2007XXX-452BOM-DEL
1003-Jun-2007XXX-451DEL-BOM
1105-Jun-2007XXX-371BOM-HYD
1205-Jun-2007XXX-371HYD-MAA
1305-Jun-2007XXX-352MAA-BOM
1406-Jun-2007XXX-451BOM-COK
1506-Jun-2007XXX-456COK-BLR
1606-Jun-2007XXX-456BLR-BOM
1707-Jun-2007XXX-457BOM-MAA
1807-Jun-2007XXX-376MAA-HYD
1907-Jun-2007XXX-376HYD-BOM
Sheet1
 
Upvote 0
EMPLOYEE C
EMPLOYEE C.xls
ABCDEFGHIJKL
5Name :EMPLOYEE C
6Date
7
801-Jun-2007XXX-103BOM-AMD
901-Jun-2007XXX-103AMD-DEL
1001-Jun-2007XXX-153DEL-BOM
1103-Jun-2007XXX-352BOM-DEL
1203-Jun-2007XXX-104DEL-AMD
1303-Jun-2007XXX-104AMD-BOM
1404-Jun-2007XXX-106BOM-DEL
1504-Jun-2007XXX-112DEL-JAI
1604-Jun-2007XXX-112JAI-BOM
1707-Jun-2007XXX-602BOM-SXR
1807-Jun-2007XXX-602SXR-IXJ
1907-Jun-2007XXX-602IXJ-BOM
Sheet1

[/b]
 
Upvote 0
EMPLOYEE D
EMPLOYEE D.xls
ABCDEFGHIJKL
5Name :EMPLOYEE D
6
7Date
8
901-Jun-2007XXX-152DEL-SXR
1001-Jun-2007XXX-151SXR-IXJ
1101-Jun-2007XXX-151IXJ-DEL
1203-Jun-2007XXX-162DEL-BOM
1305-Jun-2007XXX-152DEL-IXJ
1405-Jun-2007XXX-151IXJ-SXR
1505-Jun-2007XXX-151SXR-DEL
1606-Jun-2007XXX-162DEL-BOM
1708-Jun-2007XXX-152BOM-DEL
Sheet1
 
Upvote 0
I have also posted this link in google groups
http://groups.google.com/group/micr...f0/b30460be95ee2544?lnk=raot#b30460be95ee2544

Help what I have received is:
In the "master" file, select the master sheet and right click on it to VIEW
CODE.
Press F4 to View the Properties Window
You should see Sheet1(Sheet1), etc.
The value in the Paren's is the sheet name that you've given it. The value
before the parens if the Worksheet code name.
In the Properties window, change (Name) to Master.

Now Insert a Module
Code: 
Sub FindDuplicates() 
Dim aWB As Workbook 
Dim aWS As Worksheet 
Dim WS As Worksheet 
Dim oWB As Workbook 
Dim oWS As Worksheet 
Dim lRow As Long 
Dim lCol As Long 
Dim myCol As Long 
Dim myRow As Long 
Dim myEmployee As Range 


Set aWB = ActiveWorkbook 
For Each WS In aWB.Worksheets 
    If WS.CodeName = "Master" Then 
        Set aWS = WS 
        Exit For 
    End If 
Next WS 
Set WS = Nothing 


If aWS Is Nothing Then 
    MsgBox ("The worksheet with code name Master does not exist in the " & 
vbNewLine & _ 
          "active workbook") 
End If 


'Determine last row of data in master workbook (in column 1) 
lRow = aWS.Cells(aWS.Rows.Count, 1).End(xlUp).Row 


'Determine last column of data in master workbook Row 2 
lCol = aWS.Cells(2, aWS.Columns.Count).End(xlToLeft).Column 


For myRow = 3 To lRow 
    Set myEmployee = aWS.Cells(myRow, 1) 
    If Not IsEmpty(myEmployee) Then 
        If LCase(myEmployee.Value) <> "jr" And _ 
            LCase(myEmployee.Value) <> "sr" Then 
            For myCol = 2 To lCol 
                Debug.Print myEmployee.Value, aWS.Cells(2, myCol).Value, 
aWS.Cells(myRow, myCol).Value 
            Next myCol 
        End If 
    End If 
Next myRow 


End Sub

Half the work is done... I need help on the second part as to how to open my Employee files and verify both the data.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
vba code to give next Sunday date for pasted data
Replies
2
Views
271
bh24524
B
S
  • Question
Pop Up Message Box - Out of Date items in a Column
Replies
1
Views
780
GlennUK
G
M
  • Question
VBA Macro to Extract Outlook Attachment Excel Data and Paste into Specific Excel Worksheet Range
Replies
0
Views
635
mclawler
M
I
  • Solved
Advice to edit code so deleting by mistke doesnt happen
Replies
7
Views
472
ipbr21054
I
I
  • Question
Find duplicate column values across multiple workbooks, and get data row and set row data to new sheet
Replies
2
Views
240
itomedina25
I

Forum statistics

Threads
1,216,248
Messages
6,129,702
Members
449,528
Latest member
Paula03

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