Excel Column distinct items

I have 2 columns in a excel sheet.
City Name
Kolkata Deba
Delhi Raj
Agra Deba

I have to write in the below result column :

Result
Deba (Kolkata, Agra)
Raj (Delhi)

in a second excel

Please suggest

hi,
read first excel as datatable dt1.
find distinct name for this yuu will use:
DataTable dt=dt1.DefaultView.ToTable(true, “Name”)
in foreachrow(in dt) use assign and write
{
datarow=dt1.select([Name]=‘“+row(1).ToString+”’)
use while loop:
condition:counter<=datarow.length-1
{
string=string+datarow(counter).item(0).ToString
}
after while loop
add datarow in result table:
item array will be:{row(1).ToString(string)}
}foreachrow closed
Note you have to build new datatable for result excel.

Thanks a ton Akshay. It helps a lot… Thanks again mate… :slight_smile:

Hi @debashishforyou,

excelDistinct.xaml (11.9 KB)

Try this.!

Thanks!

Hi All,

PFA the input dummy excel and the output excel which is required for your reference. Please note that in the output excel, the column OP should have the count of all the corresponding ID`s along with the Names from input in braces.
Hope this will allow you to suggest the best logic and code…Input.xlsx (8.2 KB) Output.xlsx (8.2 KB)

Hi @debashishforyou,

excelDistinct.xaml (14.8 KB)

Output.xlsx (7.2 KB)

Thanks!

1 Like

I am using the below query to convert one column of my datatable to string array :

String array = (dt2.AsEnumerable().Select(Function(a) a(5).ToString)).ToArray

I need to convert mulitple columns (3 columns from the datatable into string array) using the above query, Please help

Hi,

Could you please tell me about the below line :

outDt.Select(“”,“ID ASC”).CopyToDataTable

after adding to data row…The above line is giving me error saying column not found