loop through ID's and get RAG details

[Lumjing]

Hi 

Any help will be much appreaciated.I want to get the RAG details in RAG by ID column. From a group of same ID if for one of the ID "RAG" is RED then it should be "RED" or all. if no red then Yellow. Please see below details. Have thousands for which I have to loop through the ID's and get the RAG details 

ID	RAG	RAG by ID
12345	Grey	Grey	If "RAG" is "Grey" then "RAG by ID is "Grey"
12345	Yellow	Red	Should pick only red for ID -12345 from RAG column
12345	Green	Red
12345	Red	Red
2345	Yellow	Yellow	Should pick only yellow for -2345 from RAG column
2345	Green	Yellow
2345	Green	Yellow
66345	Grey	Grey
66345	Green	Red	Should pick only yellow for - 66345 from RAG column
66345	Green	Red
66345	Red	Red

[jdunkerley79 · Accepted solution]

I'd suggest:

- First map, Red to 1, Yellow to 2, Green to 3

- Find the minimum value by ID ignoring Grey

- Join this back to original data

- Correct the Grey rows

Sample attached.

Hope it helps

[Lumjing]

@jdunkerley79. Thank you  yes it worked. Am now able to pick RED from a group of same ID  with RED, Yellow, Green and Grey.