isso jay
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.
Tom Farrar
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.