Increment a column according to a value in column A

[Soudios (Sou)]

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.

[kirankumar.mahanthi1 (Kirankumar Mahanthi)]

Hi,

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.

ExcelIncrementDemo.zip (9.1 KB)

[Robinnavinraj_S]

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

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

[Soudios (Sou)]

@kirankumar.mahanthi1 @Robinnavinraj_S

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

[Robinnavinraj_S]

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

Thanks,

[ppr (Peter Preuss)]

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
RHS

(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

[ignasi.peiris]

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!

[Soudios (Sou)]

@Robinnavinraj_S

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

[ppr (Peter Preuss)]

@Soudios

It works but it takes a lot of time i have 23 column and more than 1000 rows

just do following:

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

@ignasi.peiris

but if you want to keep the row positions

what is needed.

@Soudios

but it takes a lot of time i

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

Thanks for support

[Soudios (Sou)]

For me it needs to takes arround 10 or 15s

[ppr (Peter Preuss)]

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

@Soudios

It works but it takes a lot of time

looks like that the timing is reflected for a different implementation

[Soudios (Sou)]

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

[ppr (Peter Preuss)]

@Soudios

i don’t know how to use LINQ

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

[ignasi.peiris]

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.

CountValuesIncolumn.zip (59.3 KB)

[Soudios (Sou)]

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

[ignasi.peiris]

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

[Soudios (Sou)]

I talking about my process (my screenshot)

[ignasi.peiris]

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

[Soudios (Sou)]

@ignasi.peiris

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

[ignasi.peiris]

@ignasi.peiris

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.

CountValuesIncolumn.zip (59.3 KB)

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

@ignasi.peiris

Get a list of unique values:

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

@ignasi.peiris

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

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

done with the following expression:

var_dt.rows(RowIndex)(ColumnName)

on the code:

var_dt.rows(index)("ColumnB")

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