Hi,
If there are 5 consecutive value of PL or H i want to high light those cells.
how to do it?
Hi @shirin.sebatina ,
What would be the case if there were 6 H or PL consecutively repeated? Do we only Highlight the First Five or All Six ?
Hi,
If its more than or equal to 5 then we need to highlight
Hi @shirin.sebatina ,
Attached below is an Example Workflow which Performs the Highlighting of Cells according to the 5 or More than 5 Consecutive Same Value rule.
Highlight _Cells_Excel.zip (11.2 KB)
At first, we could collect the Indices of the Rows and Columns of the Value "PL" or "H" . Store these values in a Datatable, and then loop through to Highlight the Cells.
Datatable can be Created in the Below Way :
Linq used to Populate the Datatable :
(From r In DT
Let x = Enumerable.Range(0,CInt(DT.Columns.Count-5)).SelectMany(Function(a)If(r.ItemArray.Skip(0+a).Take(5).All(Function(y)y.Equals("PL")),{0+a},{}))
Let y = Enumerable.Range(0,CInt(DT.Columns.Count-5)).SelectMany(Function(a)If(r.ItemArray.Skip(0+a).Take(5).All(Function(y)y.Equals("H")),{0+a},{}))
Select RowColumnIndicesDT.Rows.Add({DT.Rows.IndexOf(r),String.Join(",",x),String.Join(",",y)})).CopyToDatatable
Once we have the Populated Datatable consisting of row Indices and Column Start and End Indices, we can Highlight the cell using the Set Range Color Activity.
This Solution could be Optimised in a Better way, Although let’s Check if it meets your requirements.
Let us know if you are looking for an Alternate solution.
@shirin.sebatina
as an alternate find a genric stack logic approach. From the created report we can calculate other information e.g. Excel ranges.
input / output
RIX=RowIndex
CIX=Column Index
Variables:
SeqLen our minum subsequent Sequence length
Flow:
we are creating a helper tuple with rix, cix value info
we do group it on value per row
then we process the groups
- put group on stack
- find subsequent sequence - tmpSeq
- add details to the report when min length was reached
- remove tmpSeq from the stack
- repeat as long as stack count is greater then min length
Inspection Groups:
Find starter help here:
GetRCIndexReport_NConsecutiveValues.xaml (16.3 KB)
Thankyou @supermanPunch
1 Like
i want to add black spaces also to this condition,
if there are blank space (5 or more than 5) even those need to be highlighted
1 Like
If the excel has blank spaces (5 or more consecutive blanks ) then it has to be highlighted.
Hi @shirin.sebatina ,
If you do have many other values along with the PL or H cells to be highlighted, take a look at the above implementation workflow provided by @ppr
This should be able to identify range of values present in the Datatable dynamically.
Hi @ppr
How to write the range in the set color range activity
Can you please help!!
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.