Verify two sets of data to find mistakes

[Maxi]

Hi! Guys, my brother has posted a question on mrexcel.com for which he
is not getting an answer. Can anybody help?

Instead of posting a new thread in this forum, I thought it would be
better if I give a link to the original post.
Here is the link: http://www.mrexcel.com/board2/viewtopic.php?t=294981&highlight=

 

[Guest]

4)In a cursory look, I'm guessing this can be done, but it's fairly
complicated. That's probably why you're not getting an answer as quickly as
you'd like. There are quite a few questions to answer:

1) Are the master and Employee files located in the same folder? (it
would make it easier)
2) Are the employee names always in Column A?
3) Are the dates always in Row 2 of master? (does data always start in
A3?)
4) Are the employee name files identical to the names in the master
(except with .xls at the end)
5) How do you know to extract the flight #'s (I'm assuming) from cell B3?
Does the data always start after the 2nd space? Where does it end?
6) Having merged cells makes it a bit more complicated, but it can be done.7_
7) How many employees do you need to check?

You may have already answered some of this. If I have time later, I'll
post info on how I'd start on this. This will have to be a step wise
process.

 

[Guest]

You may want to put a message that you've also posted here so that someone
doesn't waste their time duplicating effort on this.

 

[Maxi]

4)In a cursory look, I'm guessing this can be done, but it's fairly

complicated. That's probably why you're not getting an answer as quickly as
you'd like.

I don't need an answer quickly, I have been doing this manually for
two months. I have also tried lot of things for my brother but the
kind of macros I know, it is kind of difficult for me.

1) Are the master and Employee files located in the same folder? (it
would make it easier)

Yes. C:\Data

2) Are the employee names always in Column A?
Yes.

3) Are the dates always in Row 2 of master? (does data always start in
A3?)

Yes. (yes)

4) Are the employee name files identical to the names in the master
(except with .xls at the end)
Yes.

5) How do you know to extract the flight #'s (I'm assuming) from cell B3?
Does the data always start after the 2nd space? Where does it end?

I did not understand this question.

6) Having merged cells makes it a bit more complicated, but it can be done.7_

I cannot help on this. The excel file is extracted from a software
which pulls the result in merged cells.

7) How many employees do you need to check?

This is just a sample data, In real, 250+ employees.

You may have already answered some of this. If I have time later, I'll
post info on how I'd start on this. This will have to be a step wise
process.

I will also put a message on mrexcel that I have posted this question
here. Thank you so much

 

[Guest]

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
In that module paste the following:

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

That's a start to getting what you want.

 

[Maxi]

I have gone through the entire code and have understood it properly.
Very well done. Hats off to you.
myEmployee.Value : picks up the employee name
aWS.Cells(2, myCol).Value : picks up the date
aWS.Cells(myRow, myCol).Value : picks up the string which has the
numbers to be checked

I think I would need another piece of code in the [ For myCol = 2 To
lCol AND Next myCol ] loop

Now the logic what i am thinking of is just after the For myCol = 2 To
lCol line, I should open the EMPLOYEE A.XLS file and check the numbers
corresponding to date in the aWS.Cells(2, myCol).Value. After that
find those numbers in the aWS.Cells(myRow, myCol).Value string and
continue this process till the end of all dates. Once done, close the
file and open EMPLOYEE B.XLS file.

I believe my thought process is correct. I am going to try this out
tomorrow evening. Not sure if I can do it but I will definitely give
it a try.

Just a question: the two variables Dim oWB As Workbook and Dim oWS As
Worksheet. Have you declared it to open the other files in the c:\data
folder?

Thanks a ton

 

[Guest]

You'll need to define the path for the file you are opening. I'm assuming
that the master file and the Employee files are in the same folder.


myFolderPath = awb.path & "\"
myFilePath = myFolderPath & myEmployee.value & ".xls"

Try using this Sub to open the file

Sub OpenWorksheet(myFilePath As String, oWB As Workbook)
Dim myFilePath As String
Dim ShortName As String
Dim aWB As Workbook
Dim oWB As Workbook

Set aWB = ActiveWorkbook

'Opens Finance workbook


ShortName = Right(myFilePath, Len(myFilePath) - InStrRev(myFilePath, "\"))

On Error Resume Next
Set oWB = Nothing
Set oWB = Workbooks(ShortName)

If oWB Is Nothing Then
Set oWB = Workbooks.Open(myFilePath)
End If
On Error GoTo 0

End Sub

And No, I didn't create all this from scratch. I had used something like
this before.
--
HTH,
Barb Reinhardt

I have gone through the entire code and have understood it properly.
Very well done. Hats off to you.
myEmployee.Value : picks up the employee name
aWS.Cells(2, myCol).Value : picks up the date
aWS.Cells(myRow, myCol).Value : picks up the string which has the
numbers to be checked

I think I would need another piece of code in the [ For myCol = 2 To
lCol AND Next myCol ] loop

Now the logic what i am thinking of is just after the For myCol = 2 To
lCol line, I should open the EMPLOYEE A.XLS file and check the numbers
corresponding to date in the aWS.Cells(2, myCol).Value. After that
find those numbers in the aWS.Cells(myRow, myCol).Value string and
continue this process till the end of all dates. Once done, close the
file and open EMPLOYEE B.XLS file.

I believe my thought process is correct. I am going to try this out
tomorrow evening. Not sure if I can do it but I will definitely give
it a try.

Just a question: the two variables Dim oWB As Workbook and Dim oWS As
Worksheet. Have you declared it to open the other files in the c:\data
folder?

Thanks a ton

 

[Maxi]

Yes my master and employee files are in the same folder "C:\Data"
I was thinking of opening the file using Set wbo =
Workbooks.Open(filename) and
closing it using wbo.Close

The code you have given seems to be a very efficient one. I may have
to figure out how to call that sub to open/close my files. I am still
trying on a logic as to how do I compare both the numbers in master
and employee files for each and every date and employee. Will keep you
posted.

Thank you

 

[Maxi]

Can I ask for more help... the challenge what I am facing is that, in
the employee files the dates are one below the other. In some cases
there are two dates and in some there are three. Can you shed some
light on it?

 

[Maxi]

More help requried....

 

[Maxi]

Can I ask for more help... the challenge what I am facing is that, in
the employee files the dates are one below the other. In some cases
there are two dates and in some there are three. Can you shed some
light on it?

 

[Maxi]

Can I ask for more help... the challenge what I am facing is that, in
the employee files the dates are one below the other. In some cases
there are two dates and in some there are three. Can you shed some
light on it?