How to write cell in for each row except one in Excel

Help
, , ,
1

Hi everybody,

I have an Excel with data (attached file). row(0) - position code, row(2) - status.
The condition is that If the position code in the template occurs 1 time, then in the “status” field should be “Yes”.
If the position code in the template occurs 2 or more times, then in the “status” field the value “Yes” can be specified only once.
If this condition is not met, then opposite the code in which the condition is not met, add a comment in the field “status” with value “comment”

I’ve tried a lot of ways of solving this task such as for each row in DT1 (attached Excel) filter by “position code” and status “Yes” getting DT2, but then I don’t know how to add comment for each row in DT2 except one.

Please, help me to solve this task.
Thanks in advance

Example.xlsx (8.2 KB)

1 Like
2

Hi @Kantemir
Based on the string variable Stroutput within the condition set as Stroutput= “Yes” with assign activity

and then use write cell activity and pass the range as like this"D:+DDt.Rows.Count.ToString and pass the string variable

Thanks
Ashwin.S

3

Hi @AshwinS2

I’ve tried this method but unfotrunately it’s not working for my situaton. For example, in attached file 2 rows with position code 12345 and status “Yes” and 2 rows with position code 1234 and status “Yes”. And accordig to the condition comment should be added in every row except one for this position codes.

Thanks
Kantemir

4

@AshwinS2

May be I didn’t understand your idea fully. If it’s possible could you please show your solution, I will be very grateful.

Thanks,
Kantemir

5

Hi @Kantemir

Check this

image
image772×443 11.1 KB

image

and do write range

Thanks
ashwin S

6

Hi @AshwinS2

Thanks for reply. This solution works only for position codes Contains «12345». But it’s just example. In fact, it could be absolutely any position code in this table.
The algorithm I see:

  1. For each row in dt1 filter dt1.columns(0) with value of every position code to determine how many rows with the same position code and filter dt.columns(6).tostring with value “Yes”. Get DT2.
  2. Then for filtered rows (rows with the same position code and with status “yes”) I should add a comment for each row except one (any of them). But I don’t know how to do this step. Each row in DT2 has its index, but I couldn’t pass this index to Dt1 (I’d try to solve this task using indexes) because for each row in dt indexes of rows of DT2 are -1.

That’s why I’m asking for help and for realization of this task

Thanks,
Kantemir

7

Hi @Kantemir,

If I understood well, a valid result would look like this
image

If so, maybe this can help you

initialDT.Select.Where(Function(x) x("Position code").Equals(row("Position code")) And x("Status").Equals("Yes") And String.IsNullOrEmpty(x("Coment").ToString)).ToArray

image