Post reply

Use one file to filter another

  • September 17, 2011 at 8:33 am

    #245076

    I have an excel file with 117,000 rows & 51 columns and another file with 66,000 rows and just one column. This column (dname) is common to both files.

    How can I generate a third file that is effectively a trimmed-down version of the first, where only those records where "dname" matches are written?

    Thanks

  • September 17, 2011 at 12:22 pm

    #1382958

    You don't need SQL to do this; you can do it just as easily in Excel:

    First sort both files on the common column, copy the dname column to a new spreadsheet and use the VLOOKUP function to pull down the related columns from File1. (Hint: You will need to write separate VLOOKUP statements for each column and copy them across all rows and columns.)

    Regards,

    goodguy

    Experience is a bad teacher whose exams precede its lessons

  • September 17, 2011 at 12:27 pm

    #1382961

    Yes, I'd forgotten about VLOOKUP , very useful command.

    Thanks

  • September 17, 2011 at 3:28 pm

    #1382987

    You are welcome. Sometimes the solution is simple and staring us in the face!

    Regards,

    goodguy

    Experience is a bad teacher whose exams precede its lessons

  • September 23, 2011 at 9:20 am

    #1385842

    Can you help with the command? Here is what I have, but it is giving an error message:

    =VLOOKUP(J2,[trim2.xlsx]Sheet2!$J$2:$J$80039,10,FALSE)

    (The "drawing number" column is column J - this column is common to both files)

  • September 23, 2011 at 11:01 am

    #1385920

    See the formulas in the attached examples.

    Regards,

    goodguy

    Experience is a bad teacher whose exams precede its lessons

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply