Selecting a row by highest value in a column per unique value in another column

Hello everyone! I really hope someone can help me out with this...

I have a set of data with several thousand rows and 50 columns(each column is a named field).
My first field is named 'PatientUI' for which each value is an 8-digit number. Duplicate values are allowed in this column.

I have another field named 'Stage' for which the possible values are: 0, 1, 2, 3, 4, 5, 6, 7. Duplicate values are allowed in this column.

I want to create a new column with field name 'HighestStage' for which the possible values are 0 and 1. And I want to populate this column by entering a 1 when the row in question has the highest Stage for the PatientUI for that row. And I want to put a 0 for every other row for that PatientUI.

For example, if I have

PatientUI | Stage |
00238574 | 3 |
00238574 | 2 |
00238574 | 4 |
00238574 | 1 |
00238574 | 3 |


I want to create a new column so that I have

PatientUI | Stage | HighestStage |
00238574 | 3 | 0 |
00238574 | 2 | 0 |
00238574 | 4 | 1 |
00238574 | 1 | 0 |
00238574 | 3 | 0 |

And if the highest stage for a patient occurs more than once for that patient, I want to put a 1 in the new column for each occurrence.



Does anyone know how I'd be able to do this? I'd really appreciate any help with this.

Try this array* formula in C2:

=IF(B2=MAX(IF(A$2:A$10000=A2,B$2:B$10000)),1,0)

and then copy down.

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual Enter. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar, but you should not type these yourself. If you need to amend the formula subsequently, then you will need to use CSE again. The formula can be copied to other cells in the normal way(s).

Hope this helps.

Pete

Assuming patientUI in column A and stage in column B

=--(COUNTIFS(PatientUI,A2,Stage,">"&B2)=0)

Should be more efficient than an array formula over a few thousand rows.

Thanks a lot. As soon as I get a chance to try these methods out, I'll let you know how it goes.

Hey Pete,

Thanks for your reply. I tried your suggestion, but it doesn't seem to work. When I use Ctrl-Shift-Enter, '#N/A' is returned for every cell.
I was thinking that it might have something to do with the embedded IF function?
Any thoughts?

Perhaps you could post a sample workbook - remove anything of a sensitive or confidential nature first.

Pete

Nevermind, silly mistake on my end. I adjusted the size and format of my table and it works now.

Thanks a lot!

-Niraj

Thanks Marcol, that way also works very well!