Increment a column according to a value in column A

I need to increment a column according to a value in column A.

If the value of the cell in column A is always the same, then column B must be incremented until the value in column A is different, at which point it will start from 0.



For the above requirement we have to use the below steps.

  1. Use read range activity to read the data and export into data table.
  2. Use for each row loop and use if condition to identify the first row value and second row value and also use different counters to track the count and cell reference.

Attached the work flow for your reference. thanks. (9.1 KB)

1 Like

Hi @Soudios

I am created a workflow as you expected , i will attach the xaml and output excel with you

if it is helpful to you kindly mark it as solution

test9.xaml (11.9 KB)
test9.xlsx (8.4 KB)

1 Like

@kirankumar.mahanthi1 @Robinnavinraj_S

It does not keep the increment in memory if the same value arrives later in column A

If you want to keep the memory we need to create new variable for unique values it increases complexity so

Sort the datatable after read range activity and process the flow

Then it works


1 Like

Can be done with a LINQ and groupBy

Prepare an empty datatable with build datatable activity - dtResult and configure to Columns:
ColA, ColB

Use an assign activity
LHS: dtResult

(From d in YourDataTableVar.AsEnumerable
Group d by k=d(0).toString.Trim into grp=Group
Let ral = grp.Select(Function (x,i) new Object(){k,i}).toList
From ra in ral
Select r = dtresult.Rows.Add(ra)).CopyToDataTable

hi @Soudios !

As @ppr was correctly writing above, those solutions will work by sorting the table and then writing the numbers, but if you want to keep the row positions (not sure about your requirements) you could also use this workaround:

1- Get a list of unique values:

var_Dt.AsEnumerable.Select(Function(a) a("ColumnA").ToString).Distinct.ToList().Where(Function(b) Not String.IsNullOrWhiteSpace(b)).ToList

2- For each unique value, get the indexes that it is located:

var_dt.AsEnumerable.where(function(x) x("ColumnA").ToString.Equals(Value)).Select(function(y) var_dt.Rows.IndexOf(y)).ToArray

you’ll get something like this, and then just init a counter, and type on each index!


1 Like


It works but it takes a lot of time i have 23 column and more than 1000 rows
Any idea to takes less time ?

just do following:

  • share the sample data with us
  • share your implementation / what you have done so far XAML with us
  • give feedback on

what is needed.

we prefer more a feedback on facts e.g. 20 ms / 1,5 min / 2days

Thanks for support

1 Like

For me it needs to takes arround 10 or 15s

where did you used the provided LINQ Statement?
Also not understandable how it results to so much activities

  • build datatable - for the preperation
  • read range
  • assign - Applying the LINQ
  • write range

would do the job

looks like that the timing is reflected for a different implementation

1 Like

i don’t know how to use LINQ, do you have a short sample of your proposition plz ?

we already shared it with you and elaborated on the steps / things that are to do

1 Like

I believe he was referring to the ForEach solution, with different counters,

I did check with mine, takes around 3 secs to process 1030 rows (Run Mode).

I’d suggest @Soudios to go with @ppr 's LINQ in case you dont need positions, or with mine if you need to mantain row positions. both use LINQ which is much faster than ForEach solutions.

You can use this also as an example to use LINQ in future developments
On sheet2 from the Test.xlsx, you have your desired output. (59.3 KB)

1 Like

OK but is it normal to takes more than 15s with my current process ?

This can depend on the machine performance, Debug or Run mode, and so on…


1 Like

I talking about my process (my screenshot)

Yep, its normal, you’re using ForEach activities, which by default are the slowest (and less recommended on efficient automations)

Also “Write Cell” activities are slower than editing the DataTable itself with an Assign Activity, and writing the Range at the end.

Your solution works and its visual, but will be slow on large Excels

1 Like


Can you show me how to do it with build data table, i will be easier for me to use it for other process plz

Here I did attach the solution.

the steps are:

1- Read range (read input file with values in Column A

2- Find unique Values in column A

3- For each unique Value found (AAA,BBB,CCC…) find the indexes:

4- Finally, for each index found inside each unique value, fill the datatable position.

done with the following expression:


on the code:



And the value to type on each DataTable position, is just a simple counter starting from 1.

1 Like