If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Compare workbooks
That works perfectly. Apologise for first mistake the additional data was in
coloum A of the old report and not the new. Just 1 last thing can you get it to give you a count of matching data if so this would be great. "joel" wrote: I'm confused because I'm not sure which workbook you are refereing to. 1) the code will alway create a new sheet at the end of the workbook where the code is located. Make sure you are looking at the last sheet 2) The code starts on Row 2 of the workbook it opens and stops when it finds a blank cell in column A. if you have bnak cells in column A I have to make a minor change to the code 3) You said you put extra data in column A. I hope you meant the workbook that gets opened and not Colun A in the wsorkbook where the macro is located. 4) the code only looks at Sheet1 in the workbook with the macro and only the 1st tab in the workbooks that are opened. 5) I made 1 change to the code below to close the workbook at the end. I'm thinking that maybe you made changes to the workbook and forgot to save the changes. If yo started with the workbook open and didn't save the changes the code will reopen the workbook and you will loose you new changes. Sub GetNewIds() filetoopen = Application _ .GetOpenFilename("Excel Files (*.xls), *.xls", _ Title:="Open New Workbook") If filetoopen = False Then MsgBox ("Cannot open file - Exitig Macro") Exit Sub End If 'sheet where old IDs are located Set OldSht = ThisWorkbook.Sheets("Sheet1") Set NewBk = Workbooks.Open(Filename:=filetoopen) Set NewSht = NewBk.Sheets(1) With ThisWorkbook 'Create new sheet Set NewIDSht = .Worksheets.Add(after:=.Sheets(.Sheets.Count)) With NewIDSht 'copy Header Row NewSht.Rows(1).Copy _ Destination:=.Rows(1) NewIDRow = 2 NewShtRow = 2 Do While NewSht.Range("A" & NewShtRow) "" ID = NewSht.Range("A" & NewShtRow) Set c = OldSht.Columns("A").Find(what:=ID, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then NewSht.Rows(NewShtRow).Copy _ Destination:=.Rows(NewIDRow) NewIDRow = NewIDRow + 1 End If NewShtRow = NewShtRow + 1 Loop End With End With NewBk.close savechanges:=False End Sub "Andy" wrote: Joel I am sorry but not sure what I am doing wrong here. Tried again with your origional code, which opens the new report fine, it then creates a new worksheet at the end of the older report. Then it seams to stop. When I first tried it I just took it that there were no matches, I have now put some extra numbers in coloum A but these dont show up in the new work sheet. Which is why I asked for the quick look up of mathing records, which I am sorry but couldnt get to work. Sorry for miss leading you again and thanks for your help to date. "joel" wrote: the number of matching items is the Last row where data is put minus 1 for the header row. the variable NewIDRow is that value except it is pointing to the row where the next row of data is put. therefore, if you take NewIDRow and subtract 2 you get the total number of matches. Numberof Matches = NewIDRow - 2 "Andy" wrote: Thats great Joel works fine. Could I be a right royal pain and ask for just one thing more. Is it possible for it to give me a count of matching records just for quick reference if not now worries. Appreciate all you have done its gonna save me hours. "Andy" wrote: Sounds great Joel but where is the code ???? "joel" wrote: I didn't test this code. Can you do a quick check and if it works it will save me the time of testing. this code is very simple for mye and I usually get it right the 1st time. I created a new worksheet at the end of the workbook with the old data. The code opens the workbook with the new ID and then closes the book so it won't change this data. I didn't name the new worksheet it will just be called sheetX where X is one higher than the sheet name in the workbook. "Andy" wrote: Yes ID's would be duplicated. As the older report contains updated and more accurate information. So I am looking for duplicate rows where coloum A is the prime key if you like so everything in row 1 relates to the id in A1 but only A1 may be duplicated other coloums may be but my interest lies in coloum A on the 2 workbooks at the moment. Many thanks "joel" wrote: What are you calling duplicates? The ID's may be the same but the rest of the data in the row may be different. Which coilumns have to match to be called a duplicate. "Andy" wrote: I currently get a new report every month in xls format which I have to spend a few hours to manualy find duplicates from the last report before i can process any new tasks required. I have tried a few tips and formulars found online but they dont give great results. In Short what I would like to do is open the new report compare it to the older one which contains more information and delete any duplicate data enteries in where coloum a is the control. Or task id. If I could do this I would then be left with only new tasks to process and not have to waste a few hours making sure I am not duplicating work. Many thanks in advance. |
#12
|
|||
|
|||
Compare workbooks
What I did in my last posting is what we call at work "Fish Boning"a problem
which is basically listing every reason for the problem. It seemed to work. I answered you last question proeviously (see below). I'm not sure where you want the total to be displayed. On the worksheet somewhere or in a message box. the number of matching items is the Last row where data is put minus 1 for the header row. the variable NewIDRow is that value except it is pointing to the row where the next row of data is put. therefore, if you take NewIDRow and subtract 2 you get the total number of matches. Numberof Matches = NewIDRow - 2 "Andy" wrote: That works perfectly. Apologise for first mistake the additional data was in coloum A of the old report and not the new. Just 1 last thing can you get it to give you a count of matching data if so this would be great. "joel" wrote: I'm confused because I'm not sure which workbook you are refereing to. 1) the code will alway create a new sheet at the end of the workbook where the code is located. Make sure you are looking at the last sheet 2) The code starts on Row 2 of the workbook it opens and stops when it finds a blank cell in column A. if you have bnak cells in column A I have to make a minor change to the code 3) You said you put extra data in column A. I hope you meant the workbook that gets opened and not Colun A in the wsorkbook where the macro is located. 4) the code only looks at Sheet1 in the workbook with the macro and only the 1st tab in the workbooks that are opened. 5) I made 1 change to the code below to close the workbook at the end. I'm thinking that maybe you made changes to the workbook and forgot to save the changes. If yo started with the workbook open and didn't save the changes the code will reopen the workbook and you will loose you new changes. Sub GetNewIds() filetoopen = Application _ .GetOpenFilename("Excel Files (*.xls), *.xls", _ Title:="Open New Workbook") If filetoopen = False Then MsgBox ("Cannot open file - Exitig Macro") Exit Sub End If 'sheet where old IDs are located Set OldSht = ThisWorkbook.Sheets("Sheet1") Set NewBk = Workbooks.Open(Filename:=filetoopen) Set NewSht = NewBk.Sheets(1) With ThisWorkbook 'Create new sheet Set NewIDSht = .Worksheets.Add(after:=.Sheets(.Sheets.Count)) With NewIDSht 'copy Header Row NewSht.Rows(1).Copy _ Destination:=.Rows(1) NewIDRow = 2 NewShtRow = 2 Do While NewSht.Range("A" & NewShtRow) "" ID = NewSht.Range("A" & NewShtRow) Set c = OldSht.Columns("A").Find(what:=ID, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then NewSht.Rows(NewShtRow).Copy _ Destination:=.Rows(NewIDRow) NewIDRow = NewIDRow + 1 End If NewShtRow = NewShtRow + 1 Loop End With End With NewBk.close savechanges:=False End Sub "Andy" wrote: Joel I am sorry but not sure what I am doing wrong here. Tried again with your origional code, which opens the new report fine, it then creates a new worksheet at the end of the older report. Then it seams to stop. When I first tried it I just took it that there were no matches, I have now put some extra numbers in coloum A but these dont show up in the new work sheet. Which is why I asked for the quick look up of mathing records, which I am sorry but couldnt get to work. Sorry for miss leading you again and thanks for your help to date. "joel" wrote: the number of matching items is the Last row where data is put minus 1 for the header row. the variable NewIDRow is that value except it is pointing to the row where the next row of data is put. therefore, if you take NewIDRow and subtract 2 you get the total number of matches. Numberof Matches = NewIDRow - 2 "Andy" wrote: Thats great Joel works fine. Could I be a right royal pain and ask for just one thing more. Is it possible for it to give me a count of matching records just for quick reference if not now worries. Appreciate all you have done its gonna save me hours. "Andy" wrote: Sounds great Joel but where is the code ???? "joel" wrote: I didn't test this code. Can you do a quick check and if it works it will save me the time of testing. this code is very simple for mye and I usually get it right the 1st time. I created a new worksheet at the end of the workbook with the old data. The code opens the workbook with the new ID and then closes the book so it won't change this data. I didn't name the new worksheet it will just be called sheetX where X is one higher than the sheet name in the workbook. "Andy" wrote: Yes ID's would be duplicated. As the older report contains updated and more accurate information. So I am looking for duplicate rows where coloum A is the prime key if you like so everything in row 1 relates to the id in A1 but only A1 may be duplicated other coloums may be but my interest lies in coloum A on the 2 workbooks at the moment. Many thanks "joel" wrote: What are you calling duplicates? The ID's may be the same but the rest of the data in the row may be different. Which coilumns have to match to be called a duplicate. "Andy" wrote: I currently get a new report every month in xls format which I have to spend a few hours to manualy find duplicates from the last report before i can process any new tasks required. I have tried a few tips and formulars found online but they dont give great results. In Short what I would like to do is open the new report compare it to the older one which contains more information and delete any duplicate data enteries in where coloum a is the control. Or task id. If I could do this I would then be left with only new tasks to process and not have to waste a few hours making sure I am not duplicating work. Many thanks in advance. |
|
Thread Tools | |
Display Modes | |
|
|