Comparing, deleting and creating a new worksheet

Ed Namzug · Mar 29th, 2002, 11:07 AM

Please help in creating a 3rd worksheet which is the result of comparing 1st and 2nd worksheet.
The Sheet 1 has all the original info and consists of columns A & B. The Sheet 2 has new info and also has columns A & B. I would like the macro or program to first look at Sheet 2, compare/delete the data that matches in Sheet 1, list the data that matches only one column then, create
Sheet 3 with the new data. In another way it's like, Sheet 1 - Sheet 2 = Sheet 3
Thanks in advance...

HERMIT · Mar 29th, 2002, 11:51 AM

You could just do that formula you quoted in sheet 3, by clicking A1 of sheet 1, then put minus, then click A1 of sheet2 and then copy it into columns A and B of sheets 3. Then perhaps filter column A and B, or sort, to find the ones which have a difference not = 0 or whatever you wanna do.

However, when I did this once, excel kept on crashing as it couldn't handle copying so many formulas.

Hermit.

[ This Message was edited by: HERMIT on 2002-03-29 10:53 ]

Dave Patton · Mar 29th, 2002, 11:56 AM

With my commentary that follows,
example sheets are named 1 and 2 and
if there is a match the result is 1.

in C1 enter
=SUMPRODUCT(('1'!$A$1:$A$8='2'!A1)*('1'!$B$1:$B$8='2'!B1))

Copy or FillDn the formula for the length of the data.

Filter or sort on Column C.
Delete if you want the data C >= 1

Copy the balance of the information if necessary (or desired) to another sheet.

Perhaps someone else will give you a compreshensive macro.

How much of the macro have you prepared?

Ed Namzug · Mar 29th, 2002, 03:31 PM

Thanks Dave & Hermit for the prompt reply..
I am sorry if I wasn't clear the first time..
Allow me to express my problem in another way, please..

Sheet 1 is a list of outstanding checks (Col A=check number, Col B=check amount).
Sheet 2 is a list of checks that was cleared by the Bank (Col A=check number, Col B=check amount).
My goal is to automate the process of deleting the checks and amounts that are the same in both sheets (1 & 2) and create a 3rd sheet with just the outstanding checks and amounts.
I do not know how to create a Macro, being a little over average Excel user.
Please help...

Thanks in advance and You All have a Happy Easter..!!!

Dave Patton · Mar 29th, 2002, 04:04 PM

The Sumproduct formula will work for your purpose.

Set it up on one sheet to determine which items are still outstanding and on the other to determine which have not yet been recorded.

Copy the unrecorded items to the main sheet
and extend the sumproduct formulas.

The amounts that have 0 as a result of the Sumproduct formula are still outstanding.

You can do a sumif on the amount column considering cleared 1 or uncleared 0.

Filter the information on the 0 and you will have a list of outstanding cheques.

A while ago I created a very complex array to do the above. This approach is much cleaner.

I will prepare a ss for you if you want a sample.

Yogi Anand · Mar 29th, 2002, 04:22 PM

Quote:

On 2002-03-29 14:31, Ed Namzug wrote:
Thanks Dave & Hermit for the prompt reply..
I am sorry if I wasn't clear the first time..
Allow me to express my problem in another way, please..

Sheet 1 is a list of outstanding checks (Col A=check number, Col B=check amount).
Sheet 2 is a list of checks that was cleared by the Bank (Col A=check number, Col B=check amount).
My goal is to automate the process of deleting the checks and amounts that are the same in both sheets (1 & 2) and create a 3rd sheet with just the outstanding checks and amounts.
I do not know how to create a Macro, being a little over average Excel user.
Please help...

Thanks in advance and You All have a Happy Easter..!!!

Hi Ed Namzug:
I am reproducing sample data for three worksheets per your specification:

ChecksRecd
chkno amount
1 300
2 525
3 30
4 5000
5 30
6 450
7 30
8 900
9 600

ChecksCleared
chkno amount
1 300
2 525
3 30
4 5000
5 30
7 30

ChecksOutstanding
chkno amount
1 0
2 0
3 0
4 0
5 0
6 450
7 0
8 900
9 600
using the formula:
=Sheet1!B3-IF(ISERROR(VLOOKUP(A3,Sheet2!A:B,2,FALSE)),0,VLOOKUP(A3,Sheet2!A:B,2,FALSE))

Then I can filter the sheet ChecksOutstanding with Custom criterion ... AMOUNT>0

The filtered list looks like:
ChecksOutstanding
chkno amount
6 450
8 900
9 600

HTH

Please post back if it works for you ... otherwise explain a little further and let us take it from there!

Dave Patton · Mar 29th, 2002, 04:31 PM

The OP wants to check both cheque number and amount. One could use Vlookup but to work properly the two columns on both sheets would have to be concatenated.

If numbers could be like 11 and 1, a separator character say "|" could be used.
- A2&"|"&B2.

Yogi Anand · Mar 29th, 2002, 05:12 PM

Quote:

On 2002-03-29 15:31, Dave Patton wrote:

The OP wants to check both cheque number and amount. One could use Vlookup but to work properly the two columns on both sheets would have to be concatenated.

If numbers could be like 11 and 1, a separator character say "|" could be used.
- A2&"|"&B2.

Hi DavePatton:
This is how I looked at it --
there is one Worksheet ChkRecd that has checkNumbers and amounts;
then there is another Worksheet ChksCleared that has checkNumbers and amounts
The third Worksheet ChksOutstanding starts off with all the checkNumbers that are in the first Worksheet, and then populates the outstanding amounts by looking up and subtracting the amounts in the first and second worksheets
then the last step is to filter extract only the outstanding checks.

Ed Namzug · Mar 29th, 2002, 05:31 PM

Yogi,
You've hit the jackpot.!!! That's exactly my thoughts.., Man..!

One Q, please... Where do I type the formula.? On Sheet3 Cell B3? How can I automate it?
I am sorry... Just want to clarify..
All of you Fellas are really awesome and great.!!!
THANK YOU VERY MUCH.!!!

Dave Patton · Mar 29th, 2002, 06:00 PM

Use an extra sheet if you want.
The more the merrier and the more complex.

The formula that I presented covered ref#, Dr. and Cr. on the GL Sheet and on the Bank Sheet.

Bank Summary numbers update automatically with just a few total formulas.

Ensure that you match ref# and amount both directions.

Extending the formula, filtering the data, and moving to Summary requires a 10 line sub.

"Once stretched by a new idea, man's mind never returns to its original dimensions.
- Oliver Wendell Holmes "

[ This Message was edited by: Dave Patton on 2002-03-30 08:17 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 29th, 2002, 11:07 AM	#1
Ed Namzug New Member Join Date: Mar 2002 Location: Los Angeles, CA Posts: 9	Please help in creating a 3rd worksheet which is the result of comparing 1st and 2nd worksheet. The Sheet 1 has all the original info and consists of columns A & B. The Sheet 2 has new info and also has columns A & B. I would like the macro or program to first look at Sheet 2, compare/delete the data that matches in Sheet 1, list the data that matches only one column then, create Sheet 3 with the new data. In another way it's like, Sheet 1 - Sheet 2 = Sheet 3 Thanks in advance...

Mar 29th, 2002, 11:51 AM	#2
HERMIT New Member Join Date: Mar 2002 Posts: 2	You could just do that formula you quoted in sheet 3, by clicking A1 of sheet 1, then put minus, then click A1 of sheet2 and then copy it into columns A and B of sheets 3. Then perhaps filter column A and B, or sort, to find the ones which have a difference not = 0 or whatever you wanna do. However, when I did this once, excel kept on crashing as it couldn't handle copying so many formulas. Hermit. [ This Message was edited by: HERMIT on 2002-03-29 10:53 ]

Mar 29th, 2002, 11:56 AM	#3
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	With my commentary that follows, example sheets are named 1 and 2 and if there is a match the result is 1. in C1 enter =SUMPRODUCT(('1'!$A$1:$A$8='2'!A1)*('1'!$B$1:$B$8='2'!B1)) Copy or FillDn the formula for the length of the data. Filter or sort on Column C. Delete if you want the data C >= 1 Copy the balance of the information if necessary (or desired) to another sheet. Perhaps someone else will give you a compreshensive macro. How much of the macro have you prepared?

Mar 29th, 2002, 03:31 PM	#4
Ed Namzug New Member Join Date: Mar 2002 Location: Los Angeles, CA Posts: 9	Thanks Dave & Hermit for the prompt reply.. I am sorry if I wasn't clear the first time.. Allow me to express my problem in another way, please.. Sheet 1 is a list of outstanding checks (Col A=check number, Col B=check amount). Sheet 2 is a list of checks that was cleared by the Bank (Col A=check number, Col B=check amount). My goal is to automate the process of deleting the checks and amounts that are the same in both sheets (1 & 2) and create a 3rd sheet with just the outstanding checks and amounts. I do not know how to create a Macro, being a little over average Excel user. Please help... Thanks in advance and You All have a Happy Easter..!!!

Mar 29th, 2002, 04:04 PM	#5
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	The Sumproduct formula will work for your purpose. Set it up on one sheet to determine which items are still outstanding and on the other to determine which have not yet been recorded. Copy the unrecorded items to the main sheet and extend the sumproduct formulas. The amounts that have 0 as a result of the Sumproduct formula are still outstanding. You can do a sumif on the amount column considering cleared 1 or uncleared 0. Filter the information on the 0 and you will have a list of outstanding cheques. A while ago I created a very complex array to do the above. This approach is much cleaner. I will prepare a ss for you if you want a sample.

Mar 29th, 2002, 04:31 PM	#7
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	The OP wants to check both cheque number and amount. One could use Vlookup but to work properly the two columns on both sheets would have to be concatenated. If numbers could be like 11 and 1, a separator character say "\|" could be used. - A2&"\|"&B2.

Mar 29th, 2002, 05:31 PM	#9
Ed Namzug New Member Join Date: Mar 2002 Location: Los Angeles, CA Posts: 9	Yogi, You've hit the jackpot.!!! That's exactly my thoughts.., Man..! One Q, please... Where do I type the formula.? On Sheet3 Cell B3? How can I automate it? I am sorry... Just want to clarify.. All of you Fellas are really awesome and great.!!! THANK YOU VERY MUCH.!!!

Mar 29th, 2002, 06:00 PM	#10
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	Use an extra sheet if you want. The more the merrier and the more complex. The formula that I presented covered ref#, Dr. and Cr. on the GL Sheet and on the Bank Sheet. Bank Summary numbers update automatically with just a few total formulas. Ensure that you match ref# and amount both directions. Extending the formula, filtering the data, and moving to Summary requires a 10 line sub. "Once stretched by a new idea, man's mind never returns to its original dimensions. - Oliver Wendell Holmes " [ This Message was edited by: Dave Patton on 2002-03-30 08:17 ]