Column difference in excel

excel 1 has some data in mat description column

excel 2 has the same data in mat description column along with few more data .

i want to keep only those rows in excel 2 which are there in excel 1 (column reference mat description )and need to delete all other rows which are extra .

how do i do that ?

1 Like

Hey @poojaskyrathore

Read both tables from Excel to Data Table.

Kindly use Join Data Table with inner join.


@poojaskyrathore Try with the sample workflow below (16.3 KB)

Make sure once you open the project, you install Balareva.excel.Activities

Hi @poojaskyrathore ,

We could also Try with a Linq Approach :

For this we assume, the two Excel files are already Read as Datatables using Workbook Read Range Activity and Stored in Excel1DT and Excel2DT

  1. First, We could get all the Mat Description Column Values from Excel1DT as an Array of String.
descpArray = Excel1DT.AsEnumerable.Select(Function(x)x("Material desc").ToString.trim).ToArray

Here, descpArray is a variable of Type Array of String

  1. Next, we can use the descpArray for comparison with Excel2DT Mat Description Column and get the Matching rows in the Below way :
rowArray = Excel2DT.AsEnumerable.Where(Function(x)descpArray.Any(Function(y)x("Material desc").ToString.Trim.ToLower.Contains(y.Trim.ToLower))).ToArray

where rowArray is a variable of Type Array of DataRow

  1. Next, Use an If Activity and Check if rowArray has any value, and Convert it to Datatable, else Assign a New Datatable to Output Datatable.

Let us know if you are still facing issues.

its not working

Hi @poojaskyrathore ,

Could you let us know What was the Issue ?

Did you get an Error or Was the Output Empty ?

If the Output is Empty, Could you maybe Provide us a Sample Excel Files and the Workflow you have used to Check it from our end?

could you connect with me through zoom tomo so that i can explain you what i want ?

@poojaskyrathore ,

Is it Required Now? We can connect tomorrow if that is ok.

Maybe in the meantime you can double Check the Steps, methods and the Data.

i have checked steps … its not working … if you are free today then we can connect

@poojaskyrathore , Sure. I am available now.

kindly join through this link at 1:30 pm

Pooja Kanwar is inviting you to a scheduled Zoom meeting.

Topic: My Meeting
Time: Apr 19, 2022 01:30 PM Pacific Time (US and Canada)

Join Zoom Meeting

Meeting ID: 919 7509 1805
Passcode: RWc1Hk

can you join plzz

@poojaskyrathore ,

I guess you have set the wrong Timings for the Meeting.