Hi I have a scenario where I have two excel files
No. Name Grade Status
1 aaa A p
2 bbb B F
3 ccc C F
4 ddd C F
No Name Enrolled
1 aaa NA
2 bbb NA
3 ggg NA
I need to get rows matching with only “name”
output should be
There are some ways to achieve it. How about the following using JoinDataTable with InnerJoin and FilterDataTable?
Sample20230421-4L.zip (7.9 KB)
Thanks, whats the filter criteria?
For the column “Name”, row values are not in a same position
Filter columns as the following (order).
The column names are not same in both the data tables
Sorry, but what is your concern? Did you try the above sample? (Sample20230421-4l.zip)
Doesn’t it work for you?
Please follow the steps below
- Use a join datatable activity and join dt1 and dt2 on name column …make sure to do a inner join
- Use filter datatable activity and open filter wizard,switch to columns tab and mention the column names which you want to keep (Name and No)
Hope this helps
But am not getting all the matching values
Did you use join only?
Can you show your workflow please
No its not showing all the matching records , am getting only a few
Please try this in assign also make sure to use build datatable activity to build a datatable outdt with two columns no and name
Outdt = (From d1 In dt1.AsEnumerable
Join d2 In dt2.AsEnumerable
On d1("Name").ToString.Trim.ToLower Equals d2("Name").ToString.Trim.ToLower
Select outdt.Rows.Add (
why am getting this error?
Are there any matching rows in two tables?
Also did you happento have data in dt1 and dt2
The column names name are correct or have been changed to required name?
Can you show how you are using it
Yes there are matching rows in two tables.
dt1 and dt2 contain data.
the column names are different in both the tables
Then in the formula did you change th column names to sync with what you have in two tables?
Can you show a screenshot please
for this am getting this error
This is because the dtfinal should contain only one column now…you are taking only name…so please use build dataTable activity and assign to dtfinal have have one column in it
If the names in one datatable have uppercase and another datatable have lowercase, it wont show those rows??
Because am not getting all the matching data
Fo the same reason I included this function of tolower while comparing…
I see in your code you removed it…if you add back it would match those as well…
Normal join activity would not do that so added the same in thai formula
it will match irrespective of case or only lowercase… because dt2 has names in both uppercase and lowercase but dt1 has names with only uppercase.