Filling in missing information based on duplicate records within a column

[eleee3]

Hi there,

I would like to fill in missing information based on duplicate records with complete information. An example of how my database looks is:

Wine Type	Colour
Cabernet Sauvignon	Red
Merlot	Red
Cabernet Sauvignon	Red
Rose	Pink
Cabernet Sauvignon
Riesling	White
Rose
Riesling
Merlot
Pinot Gris

I would like Alteryx to identify the empty cells in the Colour column, check the Wine Type column for duplicates, and if a duplicate exists, to infill the Colour column based on the duplicate with complete information. I would then want Wine Types with no duplicates and/or duplicates with no Colour information to be output separately. The outputs of this function using the above example would therefore be:

Wine Type	Colour
Cabernet Sauvignon	Red
Merlot	Red
Cabernet Sauvignon	Red
Rose	Pink
Cabernet Sauvignon	Red
Riesling	White
Rose	Pink
Riesling	White
Merlot	Red
Pinot Gris

And:

Wine Type	Colour
Pinot Gris

Note that I don't really mind whether Pinot Gris remains in the first output, as I will join the databases via Union at a later stage.

Thanks in advance!

[Qiu · Accepted solution]

@eleee3 
We can do it with a Multi-row formula tool with Sorting.

[eleee3]

Thanks, @Qiu! Is there any way to get the data in the order it was originally in?

[Qiu]

@eleee3 
You should notice that I have put a RecorD ID tool in the beginning of the flow and the Sort tool at the end. 

These two tools will bring the data to their original order.

[eleee3]

Oh that's great, thanks @Qiu!