Formula to find, change and highlight Cell based on Specific criteria

[westc4]

I have three columns of data, Assignment, Category, and Amount. What I need is to find when the Category is equal to USOCOO, within each Assignment, then find the Category with the highest Amount, then change the "USOCOO" to the highest Category and change cell color to 0.799981688894314.

Here is a sample of data: (there are thousands of lines and Assignments). The Amount may or may not be sorted by largest to smallest.
[TABLE="class: grid, width: 231"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Assignment
[/TD]
[TD]Category
[/TD]
[TD]Amount
[/TD]
[TD]NEW CATEGORY
[/TD]
[/TR]
[TR]
[TD]2532315992[/TD]
[TD]USOSTM[/TD]
[TD] 13,060.00
[/TD]
[TD]unchanged
[/TD]
[/TR]
[TR]
[TD]2532315992[/TD]
[TD]USOCOO[/TD]
[TD] 151.44
[/TD]
[TD]USOSTM
[/TD]
[/TR]
[TR]
[TD]2532315992[/TD]
[TD]USOSTM[/TD]
[TD] 119.06[/TD]
[TD]unchanged
[/TD]
[/TR]
[TR]
[TD]2532315992[/TD]
[TD]USOCOO[/TD]
[TD] 1.38[/TD]
[TD]USOSTM
[/TD]
[/TR]
[TR]
[TD]2533369197[/TD]
[TD]USODIG[/TD]
[TD] 49,934.62[/TD]
[TD]unchanged[/TD]
[/TR]
[TR]
[TD]2533369197[/TD]
[TD]USOCOO[/TD]
[TD] 3,496.50[/TD]
[TD]USODIG
[/TD]
[/TR]
[TR]
[TD]2533369197[/TD]
[TD]USOPHF[/TD]
[TD] 15.38[/TD]
[TD]unchanged[/TD]
[/TR]
[TR]
[TD]2534243856[/TD]
[TD]USOVAL[/TD]
[TD] 840.00[/TD]
[TD]unchanged
[/TD]
[/TR]
[TR]
[TD]2534243856[/TD]
[TD]USOMIV[/TD]
[TD] 45.00[/TD]
[TD]unchanged
[/TD]
[/TR]
[TR]
[TD]2534243856[/TD]
[TD]USOCOO[/TD]
[TD] 3.87[/TD]
[TD]USOVAL
[/TD]
[/TR]
[TR]
[TD]2534259251[/TD]
[TD]USODIG[/TD]
[TD] 365.04[/TD]
[TD]unchanged
[/TD]
[/TR]
[TR]
[TD]2534259251[/TD]
[TD]USOMBR[/TD]
[TD] 33.73[/TD]
[TD]unchanged
[/TD]
[/TR]
[TR]
[TD]2534259251[/TD]
[TD]USOCOO[/TD]
[TD] 29.12[/TD]
[TD]USODIG
[/TD]
[/TR]
[TR]
[TD]2534259251[/TD]
[TD]USOPHF[/TD]
[TD] 0.11[/TD]
[TD]unchanged[/TD]
[/TR]
[TR]
[TD]2534259360[/TD]
[TD]USOMBR[/TD]
[TD] 8,750.00[/TD]
[TD]unchanged
[/TD]
[/TR]
[TR]
[TD]2534259360[/TD]
[TD]USOMBR[/TD]
[TD] 175.00[/TD]
[TD]unchanged[/TD]
[/TR]
[TR]
[TD]2534259360[/TD]
[TD]USOCOO[/TD]
[TD] 23.74[/TD]
[TD]USOMBR
[/TD]
[/TR]
[TR]
[TD]2534259360[/TD]
[TD]USOCOO[/TD]
[TD] 0.47[/TD]
[TD]USOMBR
[/TD]
[/TR]
[TR]
[TD]2534344017[/TD]
[TD]USOMIV[/TD]
[TD] 105.00[/TD]
[TD]unchanged
[/TD]
[/TR]
[TR]
[TD]2534344017[/TD]
[TD]USOCOO[/TD]
[TD] 9.03[/TD]
[TD]MSOMIV
[/TD]
[/TR]
[TR]
[TD]2534384470[/TD]
[TD]USOSUN[/TD]
[TD] 1,675.00[/TD]
[TD]unchanged[/TD]
[/TR]
[TR]
[TD]2534384470[/TD]
[TD]USOCOO[/TD]
[TD] 12.69[/TD]
[TD]USOCOO
[/TD]
[/TR]
[TR]
[TD]2534402150
[/TD]
[TD]USOADV
[/TD]
[TD] 2,093.84[/TD]
[TD]unchanged[/TD]
[/TR]
[TR]
[TD]2534402150[/TD]
[TD]USOCOO[/TD]
[TD] 166.98[/TD]
[TD]USOADV[/TD]
[/TR]
[TR]
[TD]2534482508[/TD]
[TD]USOADV
[/TD]
[TD] 360.00
[/TD]
[TD]unchanged
[/TD]
[/TR]
[TR]
[TD]2534482508[/TD]
[TD]USOMIV[/TD]
[TD] 105.00[/TD]
[TD]unchanged
[/TD]
[/TR]
[TR]
[TD]2534482508[/TD]
[TD]USOCOO[/TD]
[TD] 19.65[/TD]
[TD]USOADV[/TD]
[/TR]
[TR]
[TD]2534571551[/TD]
[TD]USOCNV
[/TD]
[TD] 21,889.00[/TD]
[TD]unchanged
[/TD]
[/TR]
[TR]
[TD]2534571551[/TD]
[TD]USOCOO[/TD]
[TD] 234.18[/TD]
[TD]USOCNV
[/TD]
[/TR]
[TR]
[TD]2534678375[/TD]
[TD]USOCNV
[/TD]
[TD] 779.00[/TD]
[TD]unchanged[/TD]
[/TR]
[TR]
[TD]2534678375[/TD]
[TD]USOCOO
[/TD]
[TD] 56.48[/TD]
[TD]USOCNV
[/TD]
[/TR]
[TR]
[TD]2534848168[/TD]
[TD]USODIG
[/TD]
[TD] 9,687.02[/TD]
[TD]unchanged
[/TD]
[/TR]
[TR]
[TD]2534848168[/TD]
[TD]USOMIV[/TD]
[TD] 4,134.00[/TD]
[TD]unchanged[/TD]
[/TR]
[TR]
[TD]2534848168[/TD]
[TD]USOPST[/TD]
[TD] 3,430.00[/TD]
[TD]unchanged[/TD]
[/TR]
[TR]
[TD]2534848168[/TD]
[TD]USOCOO[/TD]
[TD] 793.83[/TD]
[TD]USODIG[/TD]
[/TR]
[TR]
[TD]2534848168[/TD]
[TD]USOPHF[/TD]
[TD] 2.98[/TD]
[TD]unchanged[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for your help!!

 

[Tyler Hilton]

Hi westc4,

If you have Excel 2016, I would suggest using the MAXIFS formula in combination with some sort of lookup formula.

If you don't have Excel 2016 but an earlier version, you will have to use a combination of array formulas.

Assuming your data starts in cell A2, I would use the following formula, making sure to hit Ctrl+Shift+Enter when entering the formula to make it an array formula.

{=IF(B2="USOCOO",INDEX($B$2:$B$37,MATCH(MAX(IF($A$2:$A$37=A2,$C$2:$C$37))&A2,$C$2:$C$37&$A$2:$A$37,0)),"unchanged")}

Three primary parts to this formula:

1. IF(B2="USOCOO", - checks for your category condition
2. MAX(IF($A$2:$A$37=A2,$C$2:$C$37)) - finds the max amount based on the assignment
3. INDEX($B$2:$B$37,MATCH(MAX(IF($A$2:$A$37=A2,$C$2:$C$37))&A2,$C$2:$C$37&$A$2:$A$37,0)) - combines this max with the assignment and does a lookup for the proper category

Add in the "unchanged" category for the false condition and you should be set.

Hope this helps! If you found it useful, please shoot me a like.

Cheers!

Tyler

 

[Tyler Hilton]

One additional note is that if you are going to have to use this formula a lot, I would suggest doing the calculation and then copying/pasting as values. Performance of the file can regress with thousands upon thousands of array formulas.

 

[westc4]

Works great!! Thank you so much!!