+ Reply to Thread
Results 1 to 6 of 6

How to compare 2 lists (original and sorted) to verify correct sort

  1. #1
    Registered User
    Join Date
    06-28-2013
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2007
    Posts
    3

    How to compare 2 lists (original and sorted) to verify correct sort

    Hello to all,
    I have an original list like this: list1.xlsx

    NAME CODE CASH
    Mike 6001 $15
    Anna 3405 $6
    Jim 4582
    Mary 8482 $7
    John 2567
    Tom 7234 $2

    and after I remove the rows with blank CASH cells and I sort by CODE I get this and save as: list2.xlsx

    NAME CODE CASH
    Anna 3405 $6
    Mike 6001 $15
    Tom 7234 $2
    Mary 8482 $7

    Now, how can I auto-check if NAME has the right CODE NUMER and CASH next to it?
    How can I compare list1.xlsx and list2.xlsx and be sure that Mike has next to him 6001 and 15, then Tom etc etc so that I am sure that I haven't screwed up things by a mistaken edit?
    Last edited by MikeTin; 06-28-2013 at 08:07 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,589

    Re: How to compare 2 lists (original and sorted) to verify correct sort

    Hi and welcome to the forum

    If you tried to upload your files, it didnt work

    But based on the sample you provided, I created 2 worksheets in the same file, and then used this to compare sheet2 (List2) against sheet1 (list1)....

    =AND(VLOOKUP(A2,Sheet1!$A$2:$C$7,2,0)=Sheet2!B2,VLOOKUP(A2,Sheet1!$A$2:$C$7,3,0)=Sheet2!C2)
    copied down
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: How to compare 2 lists (original and sorted) to verify correct sort

    If the original list of data starts on Sheet1 A1 with the headers and actual data starting in A2 to C7 and your finished data is on sheet2 starting in A1 with the headers in row 1 and the data from A2:C5, enter this formula in E2 and copy down to row 5 and across to column F.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In Column G2 of Sheet2 enter this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Registered User
    Join Date
    06-28-2013
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2007
    Posts
    3

    Question Re: How to compare 2 lists (original and sorted) to verify correct sort

    Thank you both. FDibbins you are right. It alignment looked ok in the editor but got messed up when I posted.
    Here as some questions (I am a total beginner)


    Quote Originally Posted by FDibbins View Post
    Hi and welcome to the forum

    If you tried to upload your files, it didnt work

    But based on the sample you provided, I created 2 worksheets in the same file, and then used this to compare sheet2 (List2) against sheet1 (list1)....

    =AND(VLOOKUP(A2,Sheet1!$A$2:$C$7,2,0)=Sheet2!B2,VLOOKUP(A2,Sheet1!$A$2:$C$7,3,0)=Sheet2!C2)
    copied down
    Where specifically should I copy down this. When I am done I just hit Formula -> Calculate Now correct?


    Quote Originally Posted by newdoverman View Post
    If the original list of data starts on Sheet1 A1 with the headers and actual data starting in A2 to C7 and your finished data is on sheet2 starting in A1 with the headers in row 1 and the data from A2:C5, enter this formula in E2 and copy down to row 5 and across to column F.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In Column G2 of Sheet2 enter this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I am bit confused because you say "the headers and actual data starting in A2 to C7". If I select all (headers= NAME, CODE, CASH) included and I start from A2 I go down to C8. Is this a mistake of yours or I am the one that is doing something wrong?
    Also, should I paste the
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    to all cells in the table A2 to C5 of sheet2, then paste
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    in each cell of the column G2 (down to row 5) ? Then hit Calculate now?
    Please take a lookm at these screenshots:
    sheet1.jpg
    sheet2.jpg
    Last edited by MikeTin; 06-29-2013 at 05:21 AM.

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: How to compare 2 lists (original and sorted) to verify correct sort

    Paste the formulae in the row where the data actually starts. EG. If the first row of data is in row two (just under the headers of the columns) row 2 is where the formulae start and copy down from there. In the Screen Shot, D2 is where the formula should be pasted and copied down.

  6. #6
    Registered User
    Join Date
    06-28-2013
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: How to compare 2 lists (original and sorted) to verify correct sort

    Thank you both

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1