Comparing, deleting and creating a new worksheet
Comparing, deleting and creating a new worksheet
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Comparing, deleting and creating a new worksheet

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Los Angeles, CA
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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...

  2. #2
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,327
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    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?

  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    Los Angeles, CA
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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..!!!

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,327
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    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.

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!



    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,327
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    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.

  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  9. #9
    New Member
    Join Date
    Mar 2002
    Location
    Los Angeles, CA
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.!!!

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,327
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com