• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Compare a 1 worksheet doc to a 2 worksheet doc. What's the best way?

K

Katybug1964

New Member
I have two separate workbooks. One workbook contains one worksheet, which is a gigantic table of data (Name, Client Number, Client/Matter Number, Office, Engagement Letters (no), etc. The second workbook has two worksheets. One lists the similar (not but exact) info as the 1st workbook, but does have Engagement Letters (yes); the second worksheet lists similar but doesn't have the Engagement Letters (no). I need to compare the 1st workbook to the 2nd workbook and in the 2nd workbook determine
Basically, I need to compare the first two tabs of the 2nd spreadsheet, to the 1st spreadsheet (New Matters Germany) and determine if the matters on the 1st attachment appear on the 2nd two anywhere and if so, which worksheet (e.g., do we have a letter on file for the matter or don’t we). I'm not sure the best way to proceed. I'm thinking some kind of VLOOKUp but I'm not positive because there are two worksheets to compare to the first workbook/worksheet. Thank you!
 
Hi, Katybug1964!
Thanks for answering. However if you can upload a sample of the three involved files it'd be easier for people to help you.
Regards!
 
Last edited:
I understand. I would have, but the documents are confidential. I found out that I can merge the two worksheets into one, which will make this much easier to work out. I will run a search to see what's already been posted. Thank you.
 
SirJB7 said:
Hi, Katybug1964!
Thanks for answering. However if you can upload a sample of the three involved files it'd be easier for people to help you.
Regards!
Click to expand...
Hi. I haven't found an answer yet. I have combined the two documents into one document, and used fake text. I'm attaching the document.

I don't know VBA so I'm hoping I can do what I need to through Excel. I need to compare the Matter List worksheet with the Engagement worksheet. I need to identify duplicates and unique entries. On the Engagement worksheet, there are two types of entries Y and N (column on right). I need to know which entries from the Matter List are on the Engagement sheet, and are they a Y or N. I hope this makes sense.

In my real document I have over 480 entries on the Matter List worksheet, and 207 on the Engagement worksheet.
 

Attachments

  • FAKE Working Copy 7-21-17 New Matters Combined.xlsx
    10.7 KB · Views: 1
Hi, Katybug1964!

Give a look at the uploaded file. It has the following changes compared to the posted by you.

WS Matter List:
- added a named range for the data area (i.e., excluding row 1)
- added a last column for retrieving data from Engagement: retrieves col E (Y/N), or dashes if not exists
- applied conditional format to column A for identifying duplicates

WS Engagement:
- added a named range for the data area (i.e., excluding row 1)
- added a last column for retrieving data from Matter List: retrieves col D (Yes/No), or dashes if not exists
- applied conditional format to column A for identifying duplicates

How to introduce this changes in your not fake workbook:
1) The easy and lazy way: for both worksheets, copy with Paste Special Formulas (includes values) from the not fake to the fake workbook, then rename both properly
2) The hard and learning way: repeat the 3 actions I did on fake workbook in your non fake one

Just advise if any issue.

Regards!
 

Attachments

  • FAKE Working Copy 7-21-17 New Matters Combined.xlsx
    12.5 KB · Views: 10
Last edited:
Thank you! This looks great. I had used VLOOKUP, but wasn't using the IFERROR so I didn't get what I needed. I think this perfect and I'm going to use your 3 actions to learn! I really appreciate your time.

Katy (ps - there's no "h" in my name).

Thanks again!!
 
Hi, Katybug1964!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards
PS: In Spanish aitch (h) is mute :p
PS2: Where do you see it in your name? :rolleyes:
 
Hi, Kathhybug1964!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
PS: Remember that in Spanish "h" is mute :DD
 
SirJB7 said:
Hi, Kathhybug1964!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
PS: Remember that in Spanish "h" is mute :DD
Click to expand...
Thanks. There's no "h" in my name, even if it's silent in Spanish. Please respect my name. Katy

Thanks again for all your help. You made a great difference.
 
You must log in or register to reply here.
Back
Top