excel comparison

[SabithaNirmal]

Hello to all 

I have a scenario.....
1, Compare 2 excel files, Main file contains 10 columns and comparison excel contain  20 column

2.both excel column not same order

3.Both excel rows also different count  

4. Main excel file 10 column is available in comparison excel.

5, I need to compare these 2 files and find out the difference  only in 10 column of the excel then take that difference as an output copy into excel.

Please anyone did before and share your thoughts

Regards.

SabithaNirmal

[Mohamad_747]

Hi @SabithaNirmal 

I need just more informations.

1- When you say, you only need the difference only on the 10 columns you mean for example that all columns in the 2 excel files  contains the same value ?

I mean if we take an example on one column : 

If col1 in main excel = "Abc" the same column in comparison is equal to "Abc" ? And that the same for all 9 others columns ? 

2- You said : "I need to compare these 2 files and find out the difference only in 10 column of the excel then take that difference as an output copy into excel."

When you talk about the difference of the excel, you mean the main excel file ?

[SabithaNirmal]

Hi @Mohamad_747 

First Excel have 20 column of data and second excel have 10 column of data and so both excel row will be different and the second excel 10 column data is available in first excel. 

Regards

SabithaNirmal

[SabithaNirmal]

Hi @Mohamad_747 ,

For example first excel column "ABCD" and second excel column "AD" so i want to compare AD Column only.

Regards

SabithaNirmal

[Mohamad_747]

Hi @SabithaNirmal 

You said :

the second excel 10 column data is available in first excel

My question is :

Do they have the same name ? Even if they are in an incorrect order 

Example 

Excel 1 columns = A B C D

Excel 2 columns = Z G A V B C

the A, the B the C are present in the 2 excels files and have the same name is it your case ?

[SabithaNirmal]

Yes you are correct. They are in an incorrect order 

[Mohamad_747]

Hi @SabithaNirmal 

Before i code i will be sure that i understand correctly

Main Excel File :

Comparision Excel File (contain all row and column of Main Excel File) : 

Your wanted Output : 

Correct ?

[michaeloneil]

Hi @SabithaNirmal 

Is there any reason you cant use an excel formula for this? if i understand the issue correctly you want to compare values in one column from one workbook to values in another column in another workbook? If thats what you need then i would just create a formula to add to a column and drag if down then you can filter the columns to get the number of rows with matching values and then take those rows into a collection. Example formula and screen below

=IFERROR(IF(MATCH($A2,$B$2:$B$5,0),"TRUE",""),"")

[Mohamad_747]

Hi @michaeloneil 

I think we need to create this unique Key 

[jhoney12]

To compare the two Excel files with different column orders and row counts, you can use Excel's VLOOKUP or INDEX-MATCH functions to align the columns. For more advanced needs, consider using a script in Python with the Pandas library to handle the comparison and output the differences efficiently.