Forum Discussion
Same Row Identifier, different values need to assign to a value
Hi All
I hope you can help, I have a list of orders with order lines that have different amounts and I need to assign the whole order to a specific value depending on the highest amount. Example below, please let me know if there is a simple formula to achieve this.
Thanks
| Order # | Order Line # | Value | Range 40 / 50 / 50 |
| 123 | 123-1 | 10 | 40 |
| 123 | 123-2 | 20 | 40 |
| 123 | 123-4 | 30 | 40 |
| 456 | 456-1 | 10 | 60 |
| 456 | 456-2 | 20 | 60 |
| 456 | 456-3 | 60 | 60 |
| 789 | 789-1 | 50 | 50 |
| 789 | 789-2 | 40 | 50 |
| 789 | 789-3 | 40 | 50 |
=MAX(IF($A$2:$A$10=A2,$C$2:$C$10))I'd create a helper column which returns the highest value for each order. This formula is in cell E2 and filled down. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.
=IF(E2<=40,40,IF(E2<=50,50,IF(E2<=60,60,"")))Then you can use this formula to return the range. The formula is in cell F2 and filled down. The formula uses the result of the helper column E.
=MAX(IF($A$2:$A$10=A2,$C$2:$C$10))I'd create a helper column which returns the highest value for each order. This formula is in cell E2 and filled down. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.
=IF(E2<=40,40,IF(E2<=50,50,IF(E2<=60,60,"")))Then you can use this formula to return the range. The formula is in cell F2 and filled down. The formula uses the result of the helper column E.
- That worked perfectly, thank you so much
