Comparing sheets excel and copy

Help
1

Hi,

I have Excel sheet with 2 sheets that I want to compare and the result in a third sheet that one to the two sheet with the name in common.

Sheet 1
Name Age Addresses
x 20 St. 10 40-50
y 30 Av. 25 40-55

Sheet 2
Name Sales
x 148000
y 175000

Result Sheet 3
Name Age Addresses Sales
x 20 St. 10 40-50 148000
y 30 Av. 25 40-55 175000

Regards,

Sandra

2

Check these,

Thanks,
Rammohan B.

3

Hi Rammohan,

I made this

(From x In dt1.AsEnumerable() where (From a In dt1.AsEnumerable() Join b In dt2.AsEnumerable() On a(“Name”).ToString() Equals b(“Name”).ToString() select a).Contains(x) select x).CopyToDataTable()

But I don´t know how to copy data from sheet 2.

Thanks,

Sandra

4

Hi @Sandra

The LINQ method you used looks like it is using join to find all rows in dt1 that are also in dt2. (I could be wrong though and you should test the output of the new table using a Write Range)

So, I don’t think that’s what you need.
Instead, you need to update the first table with the column that is in the second table.

To do this, I would recommend using a combination of a For each and lambda/LINQ.
Here is a psuedocode example:

Add Data Column on dt1 //Add "Sales" column
For each row In dt1.AsEnumerable //datarow as TypeArgument - leave off AsEnumerable if you use the For each row activity
    //Assign to Array variable of type DataRow
    matched<DataRow[]> = dt2.AsEnumerable.Where(Function(r2) r2("Name").ToString.Trim = row("Name").ToString.Trim ).ToArray

    //Check if a match was found
    If matched.Count>0
        //Assign Sales value to dt1
        row("Sales") = matched(0)("Sales").ToString.Trim

    <perform other actions on row>

I hope I am clear enough for you and that this helps.
There might also be a way to join the Sales column to the other dt1 using LINQ but on the top of my head, I can’t think of the solution.

Regards.

C

1 Like
5

Hi ClaytonM,

Thank you very much, It worked perfect.

Regards,

Sandra

6

Hi @ClaytonM,

Now, I want to make the comparison of two keys and bring a third result. I don’t know how to do that. It is something similar to the following:

Sheet 1
Name Age Addresses Departament
x 20 St. 10 40-50 International sales
x 20 St. 10 40-50 National sales
y 30 Av. 25 40-55 Great sales
y 30 Av. 25 40-55 International sales

Sheet 2
Name Departament Sales
x International sales 148000
y Great sales 175000
x National sales 20300

Result Sheet 3
Name Age Addresses Departament Sales
x 20 St. 10 40-50 International sales 148000
x 20 St. 10 40-50 National sales 20300
y 30 Av. 25 40-55 Great sales 175000

Could you help me, please.

Regards,

Sandra

7

Hi @Sandra

I’m going to pull in my previous pseudocode and make an adjustment to it. Essentially, you just need to add a condition when you match the Data Row

Add Data Column on dt1 //Add "Sales" column
For each row In dt1.AsEnumerable //datarow as TypeArgument - leave off AsEnumerable if you use the For each row activity
    //Assign to Array variable of type DataRow
    matched<DataRow[]> = dt2.AsEnumerable.Where(Function(r2) r2("Name").ToString.Trim = row("Name").ToString.Trim And r2("Departament").ToString.Trim = row("Departament").ToString.Trim ).ToArray

    //Check if a match was found
    If matched.Count>0
        //Assign Sales value to dt1
        row("Sales") = matched(0)("Sales").ToString.Trim

    <perform other actions on row>

The only change was made here:
dt2.AsEnumerable.Where(Function(r2) r2(“Name”).ToString.Trim = row(“Name”).ToString.Trim And r2(“Departament”).ToString.Trim = row(“Departament”).ToString.Trim ).ToArray

“And” let’s you compare multiple fields.

Regards.

1 Like
8

Hi @ClaytonM,

Thank you very much for your help. I want to ask how do I compare three data tables. Something similar to this:

Sheet 1
Name Age Addresses Department
x 20 St. 10 40-50 International sales
x 20 St. 10 40-50 National sales
y 30 Av. 25 40-55 Great sales
y 30 Av. 25 40-55 International sales

Sheet 2
Name Department Sales
x International sales 148000
y Great sales 175000
x National sales 20300

Sheet 3
Name Department Product Date
x International sales Games 01-15-2018
x National sales Technology 02-20-2018
y Great sales Technology 01-25-2018

Result Sheet 4
Name Age Addresses Department Sales Product Date
x 20 St. 10 40-50 International sales 148000 Games 01-15-2018
x 20 St. 10 40-50 National sales 20300 Technology 02-20-2018
y 30 Av. 25 40-55 Great sales 175000 Technology 01-25-2018

Could you help me, please

Regards,

Sandra

9

Hello.

Since you need to pull a column from another table, you can just add an additional Assigns to match the row in the 3rd table and assign the value to the table.

a continuation from my previous solution:

matched<DataRow[]> = dt2.AsEnumerable.Where(Function(r2) r2("Name").ToString.Trim = row("Name").ToString.Trim And r2("Department").ToString.Trim = row("Department").ToString.Trim ).ToArray
matched2<DataRow[]> = dt3.AsEnumerable.Where(Function(r3) r3("Name").ToString.Trim = row("Name").ToString.Trim And r3("Department").ToString.Trim = row("Department").ToString.Trim ).ToArray

If matched.Count>0
        //Assign Sales value to dt1
        row("Sales") = matched(0)("Sales").ToString.Trim

If matched2.Count>0
        //Assign Date value to dt1
        row("Date") = matched2(0)("Date").ToString.Trim

So I just added another Assign for matched2 to look in dt3 (the 3rd table) using the same conditions. Then, Assign the value in the “Date” column to the “Date” column in dt1 (also make sure you have added that column with Add Data Column.

Hope that answers your question.

Regards.

1 Like