If High Medium Low then result

[dhrumilpatel02]

Hello everyone!



I have two columns, Software_ID and Risk_Level. I want to see how can I fetch the maximum risk level per software_id in a new column. The data sample is as follow:



Software_ID, Risk_Level, Max_Risk_Level

A3B0, High, High

A3B0, Medium, High

A3B0, Low, High

B9S1, Medium, Medium

B9S1, Low, Medium

C2J7, Low, Low

D5K8, High, High

D5K8, Low, High



I have tried Maxifs, simple max with nested if, but it doesn't work. Can you please help? Let me know if you need more information. Thanks!

 

[Phuoc]

What are your expected results?

 

[dhrumilpatel02]

What are your expected results?

Actually the above data sample with three columns is the expected output, my bad. The original data we have just has two columns (software_id and Risk_Level). My expected output is three columns (Software_ID, Risk_Level, Max_Risk_Level). So just to reiterate:

Original data we have:
Software_ID, Risk_Level
A3B0, High
A3B0, Medium
A3B0, Low
B9S1, Medium
B9S1, Low
C2J7, Low
D5K8, High
D5K8, Low


Expected Output:

Software_ID, Risk_Level, Max_Risk_Level
A3B0, High, High
A3B0, Medium, High
A3B0, Low, High
B9S1, Medium, Medium
B9S1, Low, Medium
C2J7, Low, Low
D5K8, High, High
D5K8, Low, High

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this would do.

22 04 21.xlsm
	A	B	C
1	Software_ID	Risk_Level	Max_Risk_Level
2	A3B0	High	High
3	A3B0	Medium	High
4	A3B0	Low	High
5	B9S1	Medium	Medium
6	B9S1	Low	Medium
7	C2J7	Low	Low
8	D5K8	High	High
9	D5K8	Low	High
HML

Cell Formulas
Range	Formula
C2:C9	C2	=IF(COUNTIFS(A$2:A$9,A2,B$2:B$9,"H*"),"High",IF(COUNTIFS(A$2:A$9,A2,B$2:B$9,"M*"),"Medium","Low"))

 

[dhrumilpatel02]

Welcome to the MrExcel board!

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this would do.

22 04 21.xlsm
	A	B	C
1	Software_ID	Risk_Level	Max_Risk_Level
2	A3B0	High	High
3	A3B0	Medium	High
4	A3B0	Low	High
5	B9S1	Medium	Medium
6	B9S1	Low	Medium
7	C2J7	Low	Low
8	D5K8	High	High
9	D5K8	Low	High
HML

Cell Formulas
Range	Formula
C2:C9	C2	=IF(COUNTIFS(A$2:A$9,A2,B$2:B$9,"H*"),"High",IF(COUNTIFS(A$2:A$9,A2,B$2:B$9,"M*"),"Medium","Low"))

[/RANGE

Cell Formulas
Range	Formula

Welcome to the MrExcel board!

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this would do.

22 04 21.xlsm
	A	B	C
1	Software_ID	Risk_Level	Max_Risk_Level
2	A3B0	High	High
3	A3B0	Medium	High
4	A3B0	Low	High
5	B9S1	Medium	Medium
6	B9S1	Low	Medium
7	C2J7	Low	Low
8	D5K8	High	High
9	D5K8	Low	High
HML

Cell Formulas
Range	Formula
C2:C9	C2	=IF(COUNTIFS(A$2:A$9,A2,B$2:B$9,"H*"),"High",IF(COUNTIFS(A$2:A$9,A2,B$2:B$9,"M*"),"Medium","Low"))

Sure yes! I have updates my Account Details now. I tried the formula you sent and it is working for most Software IDs (B9S1, C2J7 and D5K8) but it is not working for (A2B0). C2 gives me the correct expected value (i.e. "High") but C3 and C4 should have "High" too, but rather it returns "Medium". I am attaching a screenshot of Formula in C3 for you to take a look at. Let me know if you need any more information, thanks a lot!!

 

[Peter_SSs]

C2 gives me the correct expected value (i.e. "High") but C3 and C4 should have "High" too,

Why should C3 and C4 be the same as C2? In your picture, the software id in row 2 is A2B0 while in rows 3 and 4 it is A3B0