topic Re: excel comparison in Digital Exchange

excel comparison

SabithaNirmal — Wed, 14 Aug 2024 00:55:07 GMT

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

Re: excel comparison

Mohamad_Dakkouri — Wed, 14 Aug 2024 07:02:40 GMT

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 ?

Re: excel comparison

SabithaNirmal — Wed, 14 Aug 2024 08:57:09 GMT

Hi @Mohamad_Dakkouri

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

Re: excel comparison

SabithaNirmal — Wed, 14 Aug 2024 08:59:11 GMT

Hi @Mohamad_Dakkouri ,

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

Regards

SabithaNirmal

Re: excel comparison

Mohamad_Dakkouri — Wed, 14 Aug 2024 11:31:43 GMT

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 ?

Re: excel comparison

SabithaNirmal — Thu, 15 Aug 2024 02:16:26 GMT

Yes you are correct. They are in an incorrect order

Re: excel comparison

Mohamad_Dakkouri — Thu, 15 Aug 2024 05:53:36 GMT

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 ?

Re: excel comparison

michaeloneil — Thu, 15 Aug 2024 15:49:46 GMT

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",""),"")

Re: excel comparison

Mohamad_Dakkouri — Fri, 16 Aug 2024 12:14:31 GMT

Hi @michaeloneil

I think we need to create this unique Key

Re: excel comparison

jhoney12 — Sat, 17 Aug 2024 08:05:50 GMT

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.

Re: excel comparison

SabithaNirmal — Mon, 19 Aug 2024 00:32:08 GMT

Hi @Mohamad_Dakkouri

Yes, your understanding is correct.

Re: excel comparison

SabithaNirmal — Mon, 19 Aug 2024 00:33:57 GMT

Hi @michaeloneil ,

Thanks for your suggestion but this one i want to perform with blue prism only

Re: excel comparison

SabithaNirmal — Mon, 19 Aug 2024 00:35:58 GMT

Hi @Mohamad_Dakkouri ,

I want to preform without using any code and with using multiple VBO's

Re: excel comparison

Mohamad_Dakkouri — Mon, 19 Aug 2024 20:13:24 GMT

Hi @SabithaNirmal

Without a code stage it will be difficult

We need to :

1- Remove some columns from the comparison excel file (native bp function)

2-put the comparison excel file columns with the same orders of main excel file columns (code stage)

3- Create a unique key column for the main excel file and the comparision excel file (code stage)

4- Filtering (native bp function)

So if you are ok with this i can give you the process to do your task step by step with screen shot

Regards

Re: excel comparison

SabithaNirmal — Tue, 20 Aug 2024 00:46:44 GMT

Hi @Mohamad_Dakkouri ,

I can compare two different excel by following way. please see below

Re: excel comparison

michaeloneil — Tue, 20 Aug 2024 11:48:39 GMT

hi @SabithaNirmal

i think i might not have been clear, the actions i was mentioning would be done in blueprism. You would use actions in the vbo to write the formula to the desired cell and you can use the vbo to drag the formula down to the desired number of rows as well.

Re: excel comparison

Mohamad_Dakkouri — Tue, 20 Aug 2024 15:27:35 GMT

@SabithaNirmal

Try my colleague @michaeloneil solution

If it's still not working we can make a call on teams m.dakkouri@infinityco.fr

Regards