Re: $$Excel-Macros$$ Find differences between four columns.
Verma bhayya,, The code is working fine but only one sided.i mean if there are no Receipts in List 2 which are in List 1 then the code finds the difference only between these two.how to change the code so that it shows all the missing receipts with amounts either way...Receipts with amounts not appearing in list 2 compared with list 1 also...please help... Thanks in advance -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
RE: $$Excel-Macros$$ Find differences between four columns.
Hi Keechak Try this: See the attached File Sub ShowDifferenc() Dim ArrTabel1 Dim rngTabel2 As Range Dim ArrResult() Dim lngRecordCOunter As Long Dim lngFillArrayAs Long lngFillArray = 1 ArrTabel1 = Range(rngTable1).CurrentRegion Set rngTabel2 = Range(rngTable2).CurrentRegion ReDim ArrResult(1 To UBound(ArrTabel1) + rngTabel2.Rows.Count, 1 To UBound(ArrTabel1, 2)) For lngRecordCOunter = (LBound(ArrTabel1) + 1) To UBound(ArrTabel1) If WorksheetFunction.SumIf(rngTabel2, ArrTabel1(lngRecordCOunter, 1), rngTabel2.Columns(2)) ArrTabel1(lngRecordCOunter, 2) Then ArrResult(lngFillArray, 1) = ArrTabel1(lngRecordCOunter, 1) ArrResult(lngFillArray, 2) = ArrTabel1(lngRecordCOunter, 2) - WorksheetFunction.SumIf(rngTabel2, ArrTabel1(lngRecordCOunter, 1), rngTabel2.Columns(2)) lngFillArray = lngFillArray + 1 End If Next lngRecordCOunter ArrTabel1 = Range(rngTable2).CurrentRegion Set rngTabel2 = Range(rngTable1).CurrentRegion For lngRecordCOunter = (LBound(ArrTabel1) + 1) To UBound(ArrTabel1) If WorksheetFunction.SumIf(rngTabel2, ArrTabel1(lngRecordCOunter, 1), rngTabel2.Columns(2)) ArrTabel1(lngRecordCOunter, 2) Then ArrResult(lngFillArray, 1) = ArrTabel1(lngRecordCOunter, 1) ArrResult(lngFillArray, 2) = ArrTabel1(lngRecordCOunter, 2) - WorksheetFunction.SumIf(rngTabel2, ArrTabel1(lngRecordCOunter, 1), rngTabel2.Columns(2)) lngFillArray = lngFillArray + 1 End If Next lngRecordCOunter With ThisWorkbook.Worksheets(ResultTable) .Range(A2).CurrentRegion.Offset(1).ClearContents .Range(A2).Resize(UBound(ArrResult), UBound(ArrResult, 2)).Value = ArrResult .Activate MsgBox Done End With 'Free them Erase ArrResult Erase ArrTabel1 Set rngTabel2 = Nothing End Sub Regards Rajan verma +91 7838100659 [IM-Gtalk] From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Mr excel Sent: 09 May 2012 5:24 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Find differences between four columns. Verma bhayya,, The code is working fine but only one sided.i mean if there are no Receipts in List 2 which are in List 1 then the code finds the difference only between these two.how to change the code so that it shows all the missing receipts with amounts either way...Receipts with amounts not appearing in list 2 compared with list 1 also...please help... Thanks in advance -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Copy of ShowDifference.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
RE: $$Excel-Macros$$ Find differences between four columns.
Hi Please find with solution , Code used as below: Sub ShowDifferenc() Dim ArrTabel1 Dim rngTabel2 As Range Dim ArrResult() Dim lngRecordCOunter As Long Dim lngFillArrayAs Long lngFillArray = 1 ArrTabel1 = Range(rngTable1).CurrentRegion Set rngTabel2 = Range(rngTable2).CurrentRegion ReDim ArrResult(1 To UBound(ArrTabel1), 1 To UBound(ArrTabel1, 2)) For lngRecordCOunter = (LBound(ArrTabel1) + 1) To UBound(ArrTabel1) If WorksheetFunction.SumIf(rngTabel2, ArrTabel1(lngRecordCOunter, 1), rngTabel2.Columns(2)) ArrTabel1(lngRecordCOunter, 2) Then ArrResult(lngFillArray, 1) = ArrTabel1(lngRecordCOunter, 1) ArrResult(lngFillArray, 2) = ArrTabel1(lngRecordCOunter, 2) - WorksheetFunction.SumIf(rngTabel2, ArrTabel1(lngRecordCOunter, 1), rngTabel2.Columns(2)) lngFillArray = lngFillArray + 1 End If Next lngRecordCOunter With ThisWorkbook.Worksheets(ResultTable) .Range(A2).CurrentRegion.Offset(1).ClearContents .Range(A2).Resize(UBound(ArrResult), UBound(ArrResult, 2)).Value = ArrResult .Activate MsgBox Done End With 'Free them Erase ArrResult Erase ArrTabel1 Set rngTabel2 = Nothing End Sub Regards Rajan verma +91 7838100659 [IM-Gtalk] From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Mr excel Sent: 03 May 2012 07:48 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Find differences between four columns. Sorry Verma,,i had messed up everything here.Got confused with the requirement. What i want actually is to find out the differences between the FIRST table with Receipt No Amt and SECOND table with Receipt No Amt. Example: The receipt no 1151881 in Master data is 4500 It should match the Receipt No 1151881 with the amount TOTAL in THIS month data. i.e. the amount in the second table may be splitted into two or more lines,but the AMT in Both the tables should be same with the SAME RECEIPT No's in both tables. I want the formula to find out which Receipts with amounts did not matched the RECEIPT NO amt in the second table Master Data This Month Data Receipt No Total Paid Receipt No Total Paid 1151886 490 1151886 490 1151882 2100 1151882 2100 1151883 50 1151883 50 1151881 4500 1151881 1500 1151880 4000 1151881 2000 1151894 710 1151880 800 1151894 490 1151880 1200 1151894 710 1151894 490 -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com ShowDifference.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
RE: $$Excel-Macros$$ Find differences between four columns.
Use this in F2 and Drag where Result is False mean that record does't exist in first table =SUMPRODUCT(--($A$1:$A$8=D2)*--($B$1:$B$8=E2))=1 See the attached File Regards Rajan verma +91 7838100659 [IM-Gtalk] From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Mr excel Sent: 03 May 2012 00:09 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Find differences between four columns. hi all, i m in need of help regarding finding the differences between four columns i.e. RECEIPT No AMT. in sheet1 and RECEIPT No AMT in sheet2.i would like to know the Formula which will highlight the differences between the two columns in both sheets. Receipt No Total Paid Receipt No Total Paid 1151886 490 1151886 490 1151882 2100 1151882 2100 1151883 50 1151883 50 1151881 3500 1151881 1500 1151880 4000 1151881 2000 1151894 710 1151880 800 1151894 490 1151880 1200 1151894 710 1151894 490 For example the RECEIPT no:1151881=3500 is splitted into two in the second worksheet.as 1151881=1500 2000.In short i need to lookup the Receipt No with the amount in worksheet one with the Receipt no with the amount in worksheet two.i need only THOSE receipt no and Amounts which are different.. Please guide me... thanks regards -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Book1.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
RE: $$Excel-Macros$$ Find differences between four columns.
If you want to highlight the unmatched Record you can put this formula in conditional formatting Please see the attached file Regards Rajan verma +91 7838100659 [IM-Gtalk] From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Mr excel Sent: 03 May 2012 00:09 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Find differences between four columns. hi all, i m in need of help regarding finding the differences between four columns i.e. RECEIPT No AMT. in sheet1 and RECEIPT No AMT in sheet2.i would like to know the Formula which will highlight the differences between the two columns in both sheets. Receipt No Total Paid Receipt No Total Paid 1151886 490 1151886 490 1151882 2100 1151882 2100 1151883 50 1151883 50 1151881 3500 1151881 1500 1151880 4000 1151881 2000 1151894 710 1151880 800 1151894 490 1151880 1200 1151894 710 1151894 490 For example the RECEIPT no:1151881=3500 is splitted into two in the second worksheet.as 1151881=1500 2000.In short i need to lookup the Receipt No with the amount in worksheet one with the Receipt no with the amount in worksheet two.i need only THOSE receipt no and Amounts which are different.. Please guide me... thanks regards -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Book1.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Find differences between four columns.
thanks for the speedy reply sir..how about using VBA for finding the differences between the columns and pasting them(only the DIFFERENT ones-Receipt no amount which are not matched to a new sheet one down the other... On Thu, May 3, 2012 at 12:08 AM, Mr excel excelkeec...@gmail.com wrote: hi all, i m in need of help regarding finding the differences between four columns i.e. RECEIPT No AMT. in sheet1 and RECEIPT No AMT in sheet2.i would like to know the Formula which will highlight the differences between the two columns in both sheets. Receipt No Total Paid Receipt No Total Paid 1151886 490 1151886 490 1151882 2100 1151882 2100 1151883 50 1151883 50 1151881 3500 1151881 1500 1151880 4000 1151881 2000 1151894 710 1151880 800 1151894 490 1151880 1200 1151894 710 1151894 490 For example the RECEIPT no:1151881=3500 is splitted into two in the second worksheet.as 1151881=1500 2000.In short i need to lookup the Receipt No with the amount in worksheet one with the Receipt no with the amount in worksheet two.i need only THOSE receipt no and Amounts which are different.. Please guide me... thanks regards -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Find differences between four columns.
A small correction for my requirement please i need to highlight those receipt nos which had different amounts..Verma bhayya...formula ko change karona please taaki,Receipt nos with different amounts highlight hue to color kardena thanks a lot for the help On Thu, May 3, 2012 at 12:21 AM, Mr excel excelkeec...@gmail.com wrote: thanks for the speedy reply sir..how about using VBA for finding the differences between the columns and pasting them(only the DIFFERENT ones-Receipt no amount which are not matched to a new sheet one down the other... On Thu, May 3, 2012 at 12:08 AM, Mr excel excelkeec...@gmail.com wrote: hi all, i m in need of help regarding finding the differences between four columns i.e. RECEIPT No AMT. in sheet1 and RECEIPT No AMT in sheet2.i would like to know the Formula which will highlight the differences between the two columns in both sheets. Receipt No Total Paid Receipt No Total Paid 1151886 490 1151886 490 1151882 2100 1151882 2100 1151883 50 1151883 50 1151881 3500 1151881 1500 1151880 4000 1151881 2000 1151894 710 1151880 800 1151894 490 1151880 1200 1151894 710 1151894 490 For example the RECEIPT no:1151881=3500 is splitted into two in the second worksheet.as 1151881=1500 2000.In short i need to lookup the Receipt No with the amount in worksheet one with the Receipt no with the amount in worksheet two.i need only THOSE receipt no and Amounts which are different.. Please guide me... thanks regards -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
RE: $$Excel-Macros$$ Find differences between four columns.
Hi, I think it is already highlighting Receipt number with different amount.. I mean which amount against same receipt number is not matching in first table, Do you want to highlight the receipt number in first table also?? Regards Rajan verma +91 7838100659 [IM-Gtalk] From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Mr excel Sent: 03 May 2012 00:27 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Find differences between four columns. A small correction for my requirement please i need to highlight those receipt nos which had different amounts..Verma bhayya...formula ko change karona please taaki,Receipt nos with different amounts highlight hue to color kardena thanks a lot for the help On Thu, May 3, 2012 at 12:21 AM, Mr excel excelkeec...@gmail.com wrote: thanks for the speedy reply sir..how about using VBA for finding the differences between the columns and pasting them(only the DIFFERENT ones-Receipt no amount which are not matched to a new sheet one down the other... On Thu, May 3, 2012 at 12:08 AM, Mr excel excelkeec...@gmail.com wrote: hi all, i m in need of help regarding finding the differences between four columns i.e. RECEIPT No AMT. in sheet1 and RECEIPT No AMT in sheet2.i would like to know the Formula which will highlight the differences between the two columns in both sheets. Receipt No Total Paid Receipt No Total Paid 1151886 490 1151886 490 1151882 2100 1151882 2100 1151883 50 1151883 50 1151881 3500 1151881 1500 1151880 4000 1151881 2000 1151894 710 1151880 800 1151894 490 1151880 1200 1151894 710 1151894 490 For example the RECEIPT no:1151881=3500 is splitted into two in the second worksheet.as 1151881=1500 2000.In short i need to lookup the Receipt No with the amount in worksheet one with the Receipt no with the amount in worksheet two.i need only THOSE receipt no and Amounts which are different.. Please guide me... thanks regards -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Find differences between four columns.
Getting error bhayy..please correct. On Thu, May 3, 2012 at 12:59 AM, Rajan_Verma rajanverma1...@gmail.comwrote: ** ** *Wanna Use VBA?? * See the attached Sheet.. You can A*dd record dynamically* in second table Try this ; Sub GetUnMatchedValueonNewSheet() Dim ArrTabl1 Dim ArrTabl2 Dim ArrResult() As String *Dim objDic As Object* *Set objDic = CreateObject(Scripting.Dictionary)* Dim lngCounter As Long ArrTabl1 = Range(rngTable1).CurrentRegion ArrTabl2 = Range(rngTable2).CurrentRegion For lngCounter = LBound(ArrTabl1) To UBound(ArrTabl1) *If Not objDic.Exists(ArrTabl1(lngCounter, 1)) Then* *objDic.Add ArrTabl1(lngCounter, 1) , ArrTabl1(lngCounter, 2), ArrTabl1(lngCounter, 1) , ArrTabl1(lngCounter, 2)* End If Next lngCounter = 0 lngCounter2 = 0 For lngCounter = LBound(ArrTabl2) To UBound(ArrTabl2) *If Not objDic.Exists(ArrTabl2(lngCounter, 1) , ArrTabl2(lngCounter, 2)) Then* ReDim Preserve ArrResult(0 To lngCounter2) ArrResult(lngCounter2) = ArrTabl2(lngCounter, 1) : ArrTabl2(lngCounter, 2) lngCounter2 = lngCounter2 + 1 End If Next With Worksheets(Sheet2).Range(A1) .CurrentRegion.ClearContents * .Resize(UBound(ArrResult) + 1, 1).Value = Application.Transpose(ArrResult)* .CurrentRegion.TextToColumns Destination:=Range(A1), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ :=:, FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True .Parent.Activate End With ** ** *'Free from memory* *Set objDic = Nothing* *Erase ArrResult* *Erase ArrTabl1* *Erase ArrTabl2* ** ** End Sub * * *Regards* *Rajan verma* *+91 7838100659 [IM-Gtalk]* ** ** *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Mr excel *Sent:* 03 May 2012 00:27 *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Find differences between four columns. ** ** A small correction for my requirement please i need to highlight those receipt nos which had different amounts..Verma bhayya...formula ko change karona please taaki,Receipt nos with different amounts highlight hue to color kardena thanks a lot for the help On Thu, May 3, 2012 at 12:21 AM, Mr excel excelkeec...@gmail.com wrote:* *** thanks for the speedy reply sir..how about using VBA for finding the differences between the columns and pasting them(only the DIFFERENT ones-Receipt no amount which are not matched to a new sheet one down the other... ** ** On Thu, May 3, 2012 at 12:08 AM, Mr excel excelkeec...@gmail.com wrote:* *** hi all, i m in need of help regarding finding the differences between four columns i.e. RECEIPT No AMT. in sheet1 and RECEIPT No AMT in sheet2.i would like to know the Formula which will highlight the differences between the two columns in both sheets. Receipt No Total Paid Receipt No Total Paid 1151886 490 1151886 490 1151882 2100 1151882 2100 1151883 50 1151883 50 1151881 3500 1151881 1500 1151880 4000 1151881 2000 1151894 710 1151880 800 1151894 490 1151880 1200 1151894 710 1151894 490 For example the RECEIPT no:1151881=3500 is splitted into two in the second worksheet.as 1151881=1500 2000.In short i need to lookup the Receipt No with the amount in worksheet one with the Receipt no with the amount in worksheet two.i need only THOSE receipt no and Amounts which are different.. Please guide me... thanks regards -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email
Re: $$Excel-Macros$$ Find differences between four columns.
Sorry Verma,,i had messed up everything here.Got confused with the requirement. What i want actually is to find out the differences between the FIRST table with Receipt No Amt and SECOND table with Receipt No Amt. Example: The receipt no 1151881 in Master data is 4500 It should match the Receipt No 1151881 with the amount TOTAL in THIS month data. i.e. the amount in the second table may be splitted into two or more lines,but the AMT in Both the tables should be same with the SAME RECEIPT No's in both tables. I want the formula to find out which Receipts with amounts did not matched the RECEIPT NO amt in the second table Master Data This Month Data Receipt No Total Paid Receipt No Total Paid 1151886 490 1151886 490 1151882 2100 1151882 2100 1151883 50 1151883 50 *1151881* *4500* *1151881* *1500* 1151880 4000 *1151881* *2000* 1151894 710 1151880 800 1151894 490 1151880 1200 1151894 710 1151894 490 -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
