Most efficient way to get row count in excel based on value

[RobertoEwaldo]

hi, i made a project to count the number of row based on value. in short, the method is to read the current cell and the next cell, if it’s the same then count + 1, if it’s different then the current and next cell will move down. something like this

it’s been working good so far. But recently the data is getting much bigger, it even take days to finish the process with my current method. Is there a more efficient way to do this ?

[shanmukh_pothamsetty]

@RobertoEwaldo ,

Once you read the Excel haveing columns “Hostname” and “count” , say the output is stored in DT1

Get distinct value from DT1
DT2 = DT1.DefaultView.ToTable(true, “hostname”)

Now loop through DT2 using for each row
array of data row (XYZ) = new System.data.datarow
Inside the loop use linq query :
Array of datarow (XYZ) = (From r in DT1.AsEnumerable.where(function(x) convert.tostring(x(“hostname”)) = Convert.tostring(row(“hostname”)) ) select r).toarray
then
in assign give Row(“Count”)=XYX.count.tostring

Regards
Shanmukh

[RobertoEwaldo]

hi @shanmukh_pothamsetty,

not quite understand what should i put inside the foreach row

what variable type is the array of data row ? after foreach row i tried to assign count (integer type) = rowArray.count.tostring like you said but no count autocomplete

[shanmukh_pothamsetty]

@RobertoEwaldo

rowArra

@RobertoEwaldo rowArray is of type Array of datarow right , if count auto complete does not come also , you can give it … no issues

for after foreach row i tried to assign count (integer type) = rowArray.count.tostring like you said but no count autocomplete , Give this Count (integer type) = rowArray.count tostring not required

[RobertoEwaldo]

it worked like a charm, thanks a lot