asked on
Comparing two excel files
I need to compare two excel 2016 files and it seems to be a lengthly process and I know there's a faster better process. I'm currently using the following process...
Step 1: I need to match the name from spreadsheet A to spreadsheet B.
Step 2: Once I find the matching names, I need to confirm if Spreadsheet A's column "P" matches to Spreadsheet B's column "X" for example. I need to do this for almost 500 users and it took me almost a day of nonstop typing and searching last month.
I think there's more to this and I know there are some Exel gurus out there. Help please.
Step 1: I need to match the name from spreadsheet A to spreadsheet B.
Step 2: Once I find the matching names, I need to confirm if Spreadsheet A's column "P" matches to Spreadsheet B's column "X" for example. I need to do this for almost 500 users and it took me almost a day of nonstop typing and searching last month.
I think there's more to this and I know there are some Exel gurus out there. Help please.
Microsoft OfficeOutlookMicrosoft AccessMicrosoft Excel
Last Comment
byundt
You need a VLookUp formula to bring Spreadsheet B's column X back, and then an Exact formula to compare what you brought back to what was in Spreadsheet column P. Provide an example to show you how that works.
Let's assume the names are in Sheet1 column A on both workbooks, and that both workbooks are open. If so, you could use a formula in spreadsheet B like:
=IFERROR(IF(INDEX('[Spreadsheet A]Sheet1'!P:P,MATCH(A2,'[Spreadsheet A]Sheet1'!A:A,0))=X2,"Matches","Doesn't match"),"Not found")
ASKER
The problem is that usernames do not appear on the same number as the other spreadsheet, this is why its a pain.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Just link the two spreadsheets are Linked tables to Access to appear as tables and use the Querying engine of Access to perform these kind of compares.
SOLUTION
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
The problem is that usernames do not appear on the same number as the other spreadsheet, this is why its a pain.It doesn't matter whether you use an Excel formula, Power Query, Access, or Python--there needs to be some way of linking at least a portion of the records in one table with those in the other. You may then use that linkage method to separate your data into records that Match, those that Don't Match, and those that aren't found in the other table.
If you aren't able to implement the suggested approaches, your next step ought to be to dummy up some data in two workbooks that illustrate the problem. Four records in each workbook are sufficient for this purpose, as there are only three possible return values. The dummy data needs to be in the exact same columns, worksheet name and workbook names as your real data. The dummy data doesn't need to include values in any columns that aren't used in matching up the names and values in columns P and X.
The Experts participating in this thread are some of the best people in the world in their fields. They are volunteering their help to you. Please accept that offer and provide the necessary information.