Compare excel sheet with different columns from different files

Hi All,

I have two excel files, file1 has some 5+ email ids in different columns and file 2 has one email id with few columns included.

I wanted to compare file1 information with file2 if it matches then it has to be written in the separate file.

is it possible to check 5 different values from one row with the another value from another file

i am attaching the sample files here. kindly help me building this logic.

File1.xlsx (39.8 KB)
File2.xlsx (94.4 KB)

Here in the file 1 all the email idā€™s from column2, column4, column6, column8 and column10 has to be compared with preferred name email address of all three sheets from file 2. if any one of the email id from file is matched then it has to be written in the output file as file 2 (that is matched record filtration).

Kindly let me know for any further clarifications .

It is quite urgent, please help me,

Regards,
Selvarani M

Hi All,

Can anyone please help me here.

Regards,
Selvarani M

@Manjuts90, can you please help me hereā€¦ i really got struck in somewhereā€¦ i

@selvarani1390 Can you tell me what going on wrong?

Actually i wanted to compare all 5 columns with that one columnā€¦ I tried it with or condition but it is not comparing am getting empty outputā€¦

Should compare each column of the email id of the row, the minimum criteria is matching any one of the email id and the maximum will the last number, here it is 5.

Kindly help me with the logic, and also i wanted to know whether it is feasible to do or not.

Thanks for response :slight_smile:

Regards,
Selvarani M

Hi,

From File 1 which column you want to check/Compare in File2 to Columns.
Specify the columns to be compare in both files.

Hi sarathi125,

from file 1 i want to take the column names as Column2, Column4, Column6, Column8, Column10
(ever time it is not mandatory to have email idā€™s in allcolumns. it may be empty also. for example i may have mail id in column2 and not in all other)

from file 2 the column name is preferred name email address from all three sheets.

Example :slight_smile:
jeff.loesch1@xyz.com and raj.pillai@xyz.com of the row has to be compared with the
each with and every record of preferred name email address of the second file.

If it matches then write that particular record from file 2 and write in the output file. here i dnt want to have these in three tabs it is to ok have in a single sheet of the output file,

Hope i explained clearly.

@selvarani1390 Please find the below workflow and attached output.

Flow.xaml (28.4 KB)

Result.xlsx (14.4 KB)

1 Like

Thank you so much :slight_smile: it helped lot.

Your help on time is highly appreciable.

Regards,
Selvarani M

1 Like

@selvarani1390 No Problem

  Happy women's Day
1 Like

Hi @Manjuts90,

Thanks for the wishes :slight_smile:

One more doubt, if it crosses more than 10 values how can i handle that.

I tried with the variable Number = 20, i am getting the length of the array is greater error.
Can you please help me handling with n number of counts here

Regards,
Selvarani M

@selvarani1390 Are you talking about number of columns?

yeah @Manjuts90 , in our input we have taken the maximum as 10 ā€¦ if i wanted to change it dynamic or to increase the number (which may happen in future). what should i do.

I tried change the number value as 20 wherein i am getting the array count error.

@selvarani1390 If your talking about number of columns for which i have used value as 10. To make it dynamic you can use

datatableName.Columns.count 

it will take all columns from that excel. Just for your information i considered every even column contains mail idā€™s, if this is not the case you need to change the condition.

Regarding your error, excel may contain columns less than 20. Because of this you might be getting that error.

Yeah that helps, thank you so much @Manjuts90 :slight_smile:

1 Like

Hi @Manjuts90, sorry for bugging you againā€¦ got struck in againā€¦ can you please help

Regards,
Selvarani M

@selvarani1390 Yes sure can you tell where you getting stuck?

Thanks @Manjuts90 :)ā€¦ Give me 15 mins let me create the input and output file.

Since i have my final demo 2mrw, i am in a hurry. Kindly helpā€¦

Regards,
Selvarani M

@selvarani1390 its okay take your time

Hi @Manjuts90,

I have 2 files - File 1 and File 2.

we have already did the logic for comparing but the comparison is not providing the exact result.

in file 1 , we have column H which is owners has got ā€˜nā€™ number of names and email idā€¦ that has been split already and the output is written from column AI to AR. we need to compare AJ(Owner2), AL(Owner4), AN(Owner6), AP(Owner8), AR(Owner10) with the file 2ā€™s ā€œPreferred Name Email Addressā€ column of the all three sheets.

when the comparison is done if any one of the AJ, AL, AN, AP and AR is matchedā€¦ I wanted to write the output as file 1 expect columnā€™s AI to AR and need the Service area column from File 2 to be attached in the column AI of the file 1. sample output of the first row is shown in the output file.

If you see the output file, in the first entry both the email idā€™s are matched so i have written the result as file 1. for example in the first row those two emails are matched so i have written the result like that.
the actual minimal condition is if any one of the email id matches of file 1ā€™s mentioned column is matched with with the file2ā€™s email id of any of the sheets then it has to write it in the same format as mentioned in the output file if none of the email id is not matched then leave that row record and check the next row.

Attaching the comparison workflow also for your perusal.

Hope it is clear, if not kindly get back. as i have my demo early afternoon 2mrw. it has be implemented asap. Looking forward for your help

Query.zip (284.3 KB)

Regards,
Selvarani M