Output rows based on value in another column

[Rosaile]

Hello,

I have a data set reflecting properties and their values during the year, and I'm wanting to create an output that looks at the "Status" Column and outputs only the unique ID row of the Latest Status.  (Latest uses this order, Final > Appeal > Initial > Projected).  What are some different ways I can achieve the output I want using Alteryx.  I have been trying to think of ways to use the multi-row formula tool or even the cross tab tool to turn the data, but can't seem to figure things out.  Thanks!

Here is my input data example.                                                                     

Unique ID	Name	Status	Value
1	Location X	Projected	$100
1	Location X	Initial	$200
1	Location X	Appeal	$200
1	Location X	Final	$150
2	Location Y	Projected	$500
2	Location Y	Initial	$1,000
2	Location Y	Appeal	$1,000
3	Location Z	Projected	$200
3	Location Z	Initial	$400

Here is how the final output should look

Unique ID	Name	Status	Value
1	Location X	Final	$150
2	Location Y	Appeal	$1,000
3	Location Z	Initial	$400

[jdunkerley79 · Accepted solution]

I suggest the following.

Use a second table to define the ranking of the statuses. Then use a find and replace to append a rank to the data

You can then sort by ID and Rank

Finally use a Sample tool to pick highest rank row by ID

Sample attached

[Rosaile]

Thank you very much!  This works and will be useful in other areas as well.