Re: $$Excel-Macros$$ Re: find duplicate rows of data between sheets macro
: tstop = Timer TElapsed = tstop - tstart TMin = 0 TMin = TElapsed \ 60 TSec = TElapsed Mod 60 msg = msg Chr(13) Chr(13) If (TMin 0) Then msg = msg TMin mins msg = msg TSec sec ' MsgBox msg End Sub *Paul* - *“Do all the good you can,By all the means you can,In all the ways you can,In all the places you can,At all the times you can,To all the people you can,As long as ever you can.” - John Wesley* - *From:* Missy786 misbah.ali...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Friday, April 25, 2014 9:08 AM *Subject:* $$Excel-Macros$$ Re: find duplicate rows of data between sheets macro Hi Paul, Thank you so much for your time and effort into problem. The code is very well structured, but I am sorry to inform, that code highlighted every row in sheet1, as the correct output would be highlighting every row in sheet1 besides row 6, as its not the same as sheet2 row6. I am sorry to cause trouble. Is this problem easy to fix? I really appreciate your time and help. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Re: find duplicate rows of data between sheets macro
Any place you have: Sheets( add the workbook name: Workbooks(Firstfile).Sheets( For the workbook that contains the code, you can use: Thisworkbook.Sheets( Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: Eugene Bernard eugene.bern...@gmail.com To: excel-macros@googlegroups.com Sent: Monday, April 28, 2014 11:49 AM Subject: Re: $$Excel-Macros$$ Re: find duplicate rows of data between sheets macro Hi Paul, The code given above works well for the sheets in same workbook. send me the code for data in two different work books. Regards Eugene On Fri, Apr 25, 2014 at 9:15 PM, Paul Schreiner schreiner_p...@att.net wrote: It works perfectly in my workbook. Row 6 is unhighlighted in sheet1 and 6 and 13 are unhighlighted in sheet2. better check to make sure the macro was copied correctly, here it is from my workbook: Option Explicit Public Dict_Data1, Dict_Data2 Sub Compare() Dim Sht1, Sht2 Dim nRows, r, sData, rArray, inx Dim tstart, tstop, TElapsed, TMin, TSec, msg '- Application.ScreenUpdating = False '- tstart = Timer Set Dict_Data1 = CreateObject(Scripting.Dictionary) Set Dict_Data2 = CreateObject(Scripting.Dictionary) Dict_Data1.RemoveAll Dict_Data2.RemoveAll '- 'Identify sheets to compare '- Sht1 = Sheet1 Sht2 = Sheet2 '- ' Clear Highlighting '- Sheets(Sht1).Select With Cells.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Sheets(Sht2).Select With Cells.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With '- ' Load data from Sht1 into Dictionary Object '- nRows = Application.WorksheetFunction.CountA(Sheets(Sht1).Range(A1:A Sheets(Sht1).Rows.Count)) For r = 2 To nRows If (r Mod 1000 = 0) Then Application.StatusBar = Loading Sht1 row r of nRows sData = Trim(Sheets(Sht1).Cells(r, A).Value) sData = sData | Trim(Sheets(Sht1).Cells(r, B).Value) sData = sData | Trim(Sheets(Sht1).Cells(r, C).Value) sData = sData | Trim(Sheets(Sht1).Cells(r, D).Value) sData = sData | Trim(Sheets(Sht1).Cells(r, F).Value) sData = sData | Trim(Sheets(Sht1).Cells(r, H).Value) sData = sData | Trim(Sheets(Sht1).Cells(r, I).Value) If (Not Dict_Data1.exists(sData)) Then Dict_Data1.Add sData, r '-- Else ' In case of duplicate rows, collect all row numbers '-- Dict_Data1.Item(sData) = Dict_Data1.Item(sData) , r End If Next '- ' Process Sht2 data, compare to sht1 '- nRows = Application.WorksheetFunction.CountA(Sheets(Sht2).Range(A1:A Sheets(Sht2).Rows.Count)) For r = 2 To nRows If (r Mod 1000 = 0) Then Application.StatusBar = Processing Sht2 row r of nRows sData = Trim(Sheets(Sht2).Cells(r, A).Value) sData = sData | Trim(Sheets(Sht2).Cells(r, B).Value) sData = sData | Trim(Sheets(Sht2).Cells(r, C).Value) sData = sData | Trim(Sheets(Sht2).Cells(r, D).Value) sData = sData | Trim(Sheets(Sht2).Cells(r, F).Value) sData = sData | Trim(Sheets(Sht2).Cells(r, H).Value) sData = sData | Trim(Sheets(Sht2).Cells(r, I).Value) If (Dict_Data1.exists(sData)) Then '--- 'highlight row in sht2 '--- Sheets(Sht2).Select With Sheets(Sht2).Range(Cells(r, A), Cells(r, I)).Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With '--- 'highlight rows in sht1 '--- Sheets(Sht1).Select rArray
$$Excel-Macros$$ Re: find duplicate rows of data between sheets macro
Hi Paul, Thank you so much for your time and effort into problem. The code is very well structured, but I am sorry to inform, that code highlighted every row in sheet1, as the correct output would be highlighting every row in sheet1 besides row 6, as its not the same as sheet2 row6. I am sorry to cause trouble. Is this problem easy to fix? I really appreciate your time and help. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Re: find duplicate rows of data between sheets macro
Must be something I changed when I went to test my large sample. I'll take a look at it. Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: Missy786 misbah.ali...@gmail.com To: excel-macros@googlegroups.com Sent: Friday, April 25, 2014 9:08 AM Subject: $$Excel-Macros$$ Re: find duplicate rows of data between sheets macro Hi Paul, Thank you so much for your time and effort into problem. The code is very well structured, but I am sorry to inform, that code highlighted every row in sheet1, as the correct output would be highlighting every row in sheet1 besides row 6, as its not the same as sheet2 row6. I am sorry to cause trouble. Is this problem easy to fix? I really appreciate your time and help. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Re: find duplicate rows of data between sheets macro
End Sub Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: Missy786 misbah.ali...@gmail.com To: excel-macros@googlegroups.com Sent: Friday, April 25, 2014 9:08 AM Subject: $$Excel-Macros$$ Re: find duplicate rows of data between sheets macro Hi Paul, Thank you so much for your time and effort into problem. The code is very well structured, but I am sorry to inform, that code highlighted every row in sheet1, as the correct output would be highlighting every row in sheet1 besides row 6, as its not the same as sheet2 row6. I am sorry to cause trouble. Is this problem easy to fix? I really appreciate your time and help. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
