Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Verify two sets of data to find mistakes
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/viewto...981&highlight= |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Verify two sets of data to find mistakes
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. -- HTH, Barb Reinhardt "Maxi" wrote: 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/viewto...981&highlight= |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Verify two sets of data to find mistakes
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. -- HTH, Barb Reinhardt "Maxi" wrote: 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/viewto...981&highlight= |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Verify two sets of data to find mistakes
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. -- HTH, Barb Reinhardt I will also put a message on mrexcel that I have posted this question here. Thank you so much |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Verify two sets of data to find mistakes
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. -- HTH, Barb Reinhardt "Maxi" wrote: 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/viewto...981&highlight= |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Verify two sets of data to find mistakes
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 On Oct 3, 7:25 pm, Barb Reinhardt wrote: 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. -- HTH, Barb Reinhardt |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Verify two sets of data to find mistakes
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 "Maxi" wrote: 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 On Oct 3, 7:25 pm, Barb Reinhardt wrote: 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. -- HTH, Barb Reinhardt |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Verify two sets of data to find mistakes
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 On Oct 3, 11:36 pm, Barb Reinhardt wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Verify two sets of data to find mistakes
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? On Oct 4, 10:29 am, Maxi wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Verify two sets of data to find mistakes
More help requried....
On Oct 5, 11:25 am, Maxi wrote: 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? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Verify two sets of data to find mistakes
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? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Verify two sets of data to find mistakes
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel graph 3 sets of data. 2 sets as lines 1 as column? | Charts and Charting in Excel | |||
How do i find a correlation between 2 data point or sets? | Excel Worksheet Functions | |||
help comparing two sets od data to find the odd data | Excel Worksheet Functions | |||
To find equation for two sets of non contiguous data | Charts and Charting in Excel | |||
27 sets of data. Need to find zero in each set | Excel Worksheet Functions |