Compare 2 datatable Headers to get matched columns from Datatable 2

I have DT1 and DT2 datatable, DT1 has only headers I need to compare those headers with DT2 headers and get only matching columns from DT2.

I got Solution From Anders Jensen Video (https://www.youtube.com/watch?v=vwBulq38lN4) , But it took more than half an hour to matched columns because excel contains almost 1000 columns. Is there any simplified way to take matched columns from datatable.

@Prathibabala If you just want to filter the DT2 with only the Column Headers that are present in DT1.

In an Assign Activity you can use the below Expression :
DT2 = DT2.DefaultView.ToTable(False,DT1.Columns.Cast(Of DataColumn).Select(Function(x)x.ColumnName).ToArray)

But for this to work the Column Names should be the exact same and all the Columns of DT1 should be present in DT2.

@supermanPunch
I have only 20 Header Column name in DT1 but DT2 have 1000 Column Headers, I need to compare both DT to get those matched header columns from DT2
I have attached excel file for your reference.Test.xlsx (398.8 KB)

@Prathibabala Have you tried this :sweat_smile:
Most Importantly are all the Column Headers in DT1 present in DT2 as well ? If present and if they have the same exact name, It should work.

@Prathibabala Check this workflow :
Read Range.zip (241.7 KB)

@supermanPunch
DT2 = DT2.DefaultView.ToTable(False,DT1.Columns.Cast(Of DataColumn).Select(Function(x)x.ColumnName).ToArray)
This worked perfectly, Thank so much :+1: :grinning:

1 Like

@supermanPunch
One more question, How to do sum values in columns ?. If 20 columns in DT I have to find each column sum value and paste value that in last cell of each column. Could you please give any solution for this.

@Prathibabala Yes. It can be done. I’ll try to modify the workflow and provide the solution as soon as I get it working.

@Prathibabala But I think there should be a differentiator between the data and the Sum Value. How would you like to Show the Sum Value?

Columns values will be 0 or 1 need to count those values like CountIf(Range,Criteria) formula in excel.We have to Count no of ones in column and paste the value in last cell of column.

@Prathibabala Oh. So the data will only be 0’s and 1’s and you would need the total count of 1’s in each Column and paste it in the last Cell ?

@Prathibabala Since taking the Sum and Counting the Number of 1’s are amounting to the same value, and there’s no other data present in it. We could take the Sum Approach as well.
Check this Updated workflow :
Read Range.zip (243.5 KB)

@supermanPunch Its working :+1: :grinning: Thank you So much

1 Like