compare across columns, determine result based on rules

[Goyal]

Requirement: first identify text starting with numbers, then compare the text such as below:
- any columns which is non-zero, then in-progress
- any failed, then failed
- all passed, then passed

here are the list of values ... it's ok to re-number or remove the leading numbers:
0-N/A
0-De-scoped
0-Duplicate
1-Not Started
2-Ready for Test
3-In Progress
4-On-hold
5-Passed
6-Failed

here is the sample and expected output:

Many thx for your support.

 

[EvonS]

Not clear on how you have things setup. Can you explain using column and row references?

 

[Snakehips]

@Goyal IF I am understanding correctly then maybe something like the below.

MRXLMAY21.xlsm
	A	B	C	D	E	F	G
1							Status
2	0-N/A	abc	0-De-scoped	Prime	0-Duplicate
3	1-Not Started	cm	0-De-scoped	Netflix	2-Ready for Test		3-In Progress
4	3-In Progress	cm	0-De-scoped	Netflix	5-Passed		3-In Progress
5	5-Passed	abc	5-Passed	Prime	0-N/A		5-Passed
6	5-Passed	cm	5-Passed	Netflix	1-Not Started		3-In Progress
7	5-Passed	abc	6-Failed	Prime	5-Passed		6-Failed
check list

Cell Formulas
Range	Formula
A1	A1	=E1
G2:G7	G2	=IF(COUNTIF(A2:E2,"6-Failed")>0,"6-Failed", IF(SUMPRODUCT(--(LEFT(A2:E2,1)="0"))=SUMPRODUCT((ISNUMBER(--LEFT(A2:E2,1))=TRUE)*1),"", IF(COUNTIF(A2:E2,"5-Passed")=SUMPRODUCT((ISNUMBER(--LEFT(A2:E2,1))=TRUE)*1)-SUMPRODUCT(--(LEFT(A2:E2,1)="0")),"5-Passed", "3-In Progress")))

If you have a large data set and processing is slow then you could maybe shorten the formula by use of helper columnss.
Hope that helps.

 

[Goyal]

@Snakehips

this is great, almost does the trick. I have one more addition (sorry).... if everything is "Not Started", it needs to return "Not Started"

 

[Snakehips]

@Snakehips

this is great, almost does the trick. I have one more addition (sorry).... if everything is "Not Started", it needs to return "Not Started"

Pleased it helped.
Try this for that added result.

MRXLMAY21.xlsm
	A	B	C	D	E	F	G
1							Status
2	0-N/A	abc	0-De-scoped	Prime	0-Duplicate
3	1-Not Started	cm	0-De-scoped	Netflix	2-Ready for Test		3-In Progress
4	3-In Progress	cm	0-De-scoped	Netflix	5-Passed		3-In Progress
5	5-Passed	abc	5-Passed	Prime	0-N/A		5-Passed
6	5-Passed	cm	5-Passed	Netflix	1-Not Started		3-In Progress
7	5-Passed	abc	6-Failed	Prime	5-Passed		6-Failed
8	1-Not Started	cm	1-Not Started	Netflix	1-Not Started		Not Started
check list

Cell Formulas
Range	Formula
A1	A1	=E1
G2:G8	G2	=IF(COUNTIF(A2:E2,"6-Failed")>0,"6-Failed", IF(SUMPRODUCT(--(LEFT(A2:E2,1)="0"))=SUMPRODUCT((ISNUMBER(--LEFT(A2:E2,1))=TRUE)*1),"", IF(COUNTIF(A2:E2,"5-Passed")=SUMPRODUCT((ISNUMBER(--LEFT(A2:E2,1))=TRUE)*1)-SUMPRODUCT(--(LEFT(A2:E2,1)="0")),"5-Passed", IF(COUNTIF(A2:E2,"1-Not Started")=SUMPRODUCT((ISNUMBER(--LEFT(A2:E2,1))=TRUE)*1)-SUMPRODUCT(--(LEFT(A2:E2,1)="0")),"Not Started", "3-In Progress"))))

I see that you are using Excel 365. You should perhaps be able to use the LET function to avoid the repetition of a couple of the repeat expressions in my formula, if you wish?

Edit **. Ignore the silly formula in A1, it's just a stray.

 

[Goyal]

thx, it worked fine. I am not aware on the usage of LET function.