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.

Assignment	Category	Amount	NEW CATEGORY
2532315992	USOSTM	13,060.00	unchanged
2532315992	USOCOO	151.44	USOSTM
2532315992	USOSTM	119.06	unchanged
2532315992	USOCOO	1.38	USOSTM
2533369197	USODIG	49,934.62	unchanged
2533369197	USOCOO	3,496.50	USODIG
2533369197	USOPHF	15.38	unchanged
2534243856	USOVAL	840.00	unchanged
2534243856	USOMIV	45.00	unchanged
2534243856	USOCOO	3.87	USOVAL
2534259251	USODIG	365.04	unchanged
2534259251	USOMBR	33.73	unchanged
2534259251	USOCOO	29.12	USODIG
2534259251	USOPHF	0.11	unchanged
2534259360	USOMBR	8,750.00	unchanged
2534259360	USOMBR	175.00	unchanged
2534259360	USOCOO	23.74	USOMBR
2534259360	USOCOO	0.47	USOMBR
2534344017	USOMIV	105.00	unchanged
2534344017	USOCOO	9.03	MSOMIV
2534384470	USOSUN	1,675.00	unchanged
2534384470	USOCOO	12.69	USOCOO
2534402150	USOADV	2,093.84	unchanged
2534402150	USOCOO	166.98	USOADV
2534482508	USOADV	360.00	unchanged
2534482508	USOMIV	105.00	unchanged
2534482508	USOCOO	19.65	USOADV
2534571551	USOCNV	21,889.00	unchanged
2534571551	USOCOO	234.18	USOCNV
2534678375	USOCNV	779.00	unchanged
2534678375	USOCOO	56.48	USOCNV
2534848168	USODIG	9,687.02	unchanged
2534848168	USOMIV	4,134.00	unchanged
2534848168	USOPST	3,430.00	unchanged
2534848168	USOCOO	793.83	USODIG
2534848168	USOPHF	2.98	unchanged

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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!!