How to check the column values and get the corresponding values from a fixed column

Help Automation Ops
,
1

There are 2 files with 2 columns like date and name ,the first file date should match with second file date and first file name should match with second file name and get the result from first file to second

2

Hi @anjani_priya

Can you please provide sample input excel and output excel

Regards,

3

Hi @anjani_priya

Can you check the below thread.

If your query is different, Please provide the input and expected output

4

input1.xlsx (9.0 KB)
input2.xlsx (9.2 KB)
the date and name should compare in input2 with input 1 files and get the result from input1 to input2
note. the input1 date column values are range from date value to present date
each date and name should compare with input2 file

5

Hiii @anjani_priya

You can use the LINQ Expression to write the required data in Input2.xlsx file.

Check the below steps for better understanding,
→ Use the Read Range workbook activity to read the Input1.xlsx and store in a datatable called DT1.
→ Then use another Read range workbook activity to read the Input2.xlsx and store in a datatable called DT2.
→ Then use the assign activity to write the LINQ Expression,

- Assign -> DT2 = (From row In DT2 
                   Let Result = If(DT1.AsEnumerable.Any(Function(X) (CDate(X("date")) > CDate(row("date")) AndAlso CDate(row("date")) < DateTime.Now) AndAlso X("Name").ToString.ToLower.Equals(row("Name").ToString.ToLower)), DT1.AsEnumerable.Where(Function(X) (CDate(X("date")) > CDate(row("date")) AndAlso CDate(row("date")) < DateTime.Now) AndAlso X("Name").ToString.ToLower.Equals(row("Name").ToString.ToLower)).Select(Function(Y) (Y("Result").ToString)).First().ToString,"") 
                   Select DT2.Clone().Rows.Add({row("date"),row("Name"), Result.ToString}) 
                        	).CopyToDataTable()

→ Then use the Write range workbook activity to write the DT2 to the Input2.xlsx file.

Check the below workflow for better understanding,
Sequence3.xaml (21.6 KB)

Hope it helps!!

6

Can you try the below flow by using the Excel Activities… @anjani_priya
Sequence3.xaml (22.1 KB)

Note: Change the Column names in the If condition and write cell as in your excel file If not given column names are not correct. And Don’t consider the Comment out activities.

Hope you understand!!

7

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.