Identifying dominant categories via another field.

[Cheekychives]

Has anyone already come up with an answer to this process?
I’m trying to populate a column with a name (category) based on the highest number within the bounds of a specific REGION_ID.

In other words, identifying which ‘CODE’ is dominant for the ‘REGION_ID’ by the largest ‘AREA’ within that ‘REGION_ID’.

I’ve made complete example (see figure attached) after manually populating the ‘DOMINANT_CODE’ column.

MAX_RESULT.JPG

I have tried to use the Pivot table and now believe that’s probably not the way to go. Maybe it needs to be done in more than one stage… any help would be much appreciated.

AREA_ID	REGION_ID	CODE	AREA	DOMINANT_CODE
1	1	A	300107	A
2	1	B	99570	A
3	2	A	11506	A
4	2	B	10213	A
5	3	A	24590	B
6	3	B	338605	B
7	4	A	22122	B
8	4	B	5733488	B
9	5	A	164875	A
10	5	B	6060	A
11	6	A	390865	B
12	6	B	2536929	B
13	7	A	873733	B
14	7	B	1940027	B
15	8	A	672648	B
16	8	B	1128200	B

[Ron Coderre]

Please post a sample workbook. JPG's only make us manually enter what we *think* is in the cells.

[Cheekychives]

o.k. I've added a table above. Hope it is of use. (couldn't find a way of attaching an xlsx file)