PDA

View Full Version : Solved: Compare values in same sheet


Shane
03-30-2007, 03:17 AM
I have a worksheet that contains the Debt and Credits entries for a particular business type. Please see the attachment for better understanding. As of Now we have 4 columns each in Debts and Credits segments. Which may increase or decrease in future depends upon the business requirements.

Now the issue is we need to compare all the Debts columns with Credits Columns. We have to compare both the segments on the basis of Ref_Num. Depending on that I need to generate a report. Now say if R001 under Debts has $1000 so under credits we need to compare all the values under RefNum , Amount, Date , Remark. If it does not found R001 in Credit side then check for more occurrence of Ref_Num (in credit side only) and if found then needs to sum up the entire occurrence on that particular Ref_Num. Now the sum of Ref_Num will compare with the Amount value of Ref_Num in Debts side. If the sum matches then leave the records move on next Ref_Num. in Debts side. In case if there is any mismatch in sum of Ref_Num of credits side and debts side then all the entries of that Ref Num will get copy into Sheet2.

Similarly in case if any Ref _Num of Debts side does not match with Credits side then the record also get copied into Sheet2. For instance in sample.xls R008 is found under Debts but not under Credits side.

Pls help.

Shane
Shane
03-30-2007, 06:22 AM
Pls help
parttime_guy
03-30-2007, 07:25 PM
I have tried to solve ur query manually, plz check attached file and let me have ur views.

:hi:
Shane
04-01-2007, 11:03 PM
Thank you gentleman it seems that it will help me in resolving my concern. However I need to do all this using VBA code.

If possible pls help me to do all this using VBA code for the same. I appreciate your time and support.

Shane
geekgirlau
04-02-2007, 09:48 PM
Try the attached - most of the code (sorting, subtotals) can just be recorded.
geekgirlau
04-09-2007, 05:04 PM
Hi Shane,

Sorry about the delay in this. I've attached a new sample that only shows values where they differ - let me know if this is what you're after.
Shane
04-10-2007, 05:33 AM
I appreciate your ur effort and time. However I need the results in the same format as they are in sheet 1. If is there is any mismatch then the whole set of records get pated in sheet2.


Regards,
Shane
geekgirlau
04-10-2007, 06:27 PM
Hi Shane,

Try this. The macro copies the whole sheet, then creates a formula to calculate the difference between all debits and credits. Where the difference is zero, those records are deleted.
Shane
04-10-2007, 10:14 PM
Hi,

Thanks that did the trick for me.

Shane.