How to find if entire row contains specific value in any of its columns?

Help Studio
1

Hi All,

I would like to know if there is any way to find if certain excel row has a specific value in any of its columns (column names and index is not constant). I have implemented using for each row. but since my excel has aroun 70k rows, this approach is very time consuming. Please find the input sheet and output that is required

Input excel sample:

Policy Category limit rate deductibe value
111 Coverages Fail Fail Fail Fail
222 Buidling
333 Coverages Fail
444 Buidling
555 Coverages Fail
666 Buidling Fail
777 Coverages
888 Locations Fail
999 Locations Fail
1110 Buidling Fail Fail Fail

Here policy is unique column. I need to find for each category (Coverages, Building, Locations etc) how many polices are failed. In some cases one policy (eg 111) might have failures for multiple columns (limit, rate, deductible). But this should be counted as one instead of 4.

Output required:

Category Total Count Pass count Fail count
Coverages 4 1 3
Buidling 4 2 2
Locations 2 0 2
2

@Palaniyappan Could you please help

3

Hi @nabeelu

dtOutput = dtInput.defaultview.totable(True,“Category”)

The above statement gives the distinct elements in Category column.

Loop dtOutput and filter the dtInput.

Thanks,
Venkatesh.

4

Hi @nabeelu
can u share the sample excel file?

5

test.xlsx (10.1 KB)
Please find excel attached

6

Hi @veerlapativenkatesh
Thanks for quick reply. But I am looking for an output where it give me count of policies failed for each category as mentioned below. Also if particular column has multiple ‘Fail’ columns still it will be counted
as only one Fail. Also the number of columns that have ‘Fail’ as values is not constant. There can be more such columns or less

Category Total Count Pass count Fail count
Coverages 4 1 3
Buidling 4 2 2
Locations 2 0 2
7

Hi @nabeelu

check this workflow
process1.zip (3.4 MB)

Regards,
Nived N
Happy Automation

8

Thanks Very much Nived.
This really helps :slight_smile:

1 Like
9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.