How do I compare 2 excel files in a 3rd excel

[rafas10]

Hi everyone, how is everyone doing?

I have a problem... I don't usually work with excel, but I need to make a function of comparation. 

I have 2 excel files with a lot of information and I want to compare it in a 3rd excel. The thing is,the information is "all over the place" but all the information is in a row. 

For example the 1st excel has the columns: Global ID, Username, age and position

The name of the excel is Excel1 and the sheet name is sheet1
And the 2nd excel has the columns: name, age, ID, position

The name of excel is Excel2 and sheet name is sheet2

How do I make the 3rd excel (Excel3 and sheet3) read, for example, a unique identifier (in this case the ID) and know that, if it finds a match, it compares the rest of the row? 

I know I might be asking for a lot, but I would appreciate some light about this topic.

Thank you all!

Kind regards, 

Sérgio

[#2]

@rafas10 

To compare data from two Excel files in a third Excel file based on a unique identifier (such as ID), you can use Excel formulas or VLOOKUP function.

Here is a suggested solution as to how you might be able to accomplish this:

1. Open Excel3 and Sheet3: Open the Excel file where you want to compare the data and navigate to the sheet where you want the comparison results.
2. Set Up Headers: In Sheet3, set up headers similar to the data you have in Excel1 and Excel2. For example, if you have columns like ID, Username, Age, and Position in Excel1, and columns like Name, Age, ID, and Position in Excel2, set up the headers accordingly in Sheet3.
3. Use VLOOKUP: In the first row of Sheet3, under each header, use the VLOOKUP function to retrieve data from Excel1 and Excel2 based on the ID.

For example, under the "Username" header in Sheet3, you can use the following formula:

=VLOOKUP(A2, '[Excel1.xlsx]Sheet1'!$A$2:$D$100, 2, FALSE)

This formula looks for the ID in cell A2 of Sheet3 in the range A2:D100 of Sheet1 in Excel1 and returns the corresponding Username.

Similarly, you can use VLOOKUP for other columns like Age and Position using appropriate column indices.

4. Repeat for Excel2: Repeat the process for retrieving data from Excel2 using VLOOKUP formulas. Adjust the formula to reference the range and columns in Excel2.

5. Autofill Formulas: Once you have set up the formulas for the first row, you can autofill the formulas down to cover all rows where you want to compare the data.

6. Compare Results: After autofilling the formulas, Excel3 (Sheet3) will compare the data from Excel1 and Excel2 based on the unique identifier (ID) and display the results in the corresponding columns.

This method allows you to compare data from two Excel files based on a unique identifier and display the comparison results in a third Excel file. Make sure to adjust the formulas and ranges as needed based on the actual data and file names. The text was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.