If Null Then Match from Another Column in a Different Row

[taran42]

I am looking to fill in the country field when it is null based on the matching state from another row. So if "Nashville" has a Country that is null, then look in the State field for another "TN" and fill in the Country that's associated with it, which would be "United States."

Current

ID	City	State	Country
1	Vancouver	BC
2	Vancouver	BC	Canada
3	Nashville	TN	United States
4	Nashville	TN
5	Chicago	IL	United States

Expected Result

ID	City	State	Country
1	Vancouver	BC	Canada
2	Vancouver	BC	Canada
3	Nashville	TN	United States
4	Nashville	TN	United States
5	Chicago	IL	United States

I have tried various formulas and the Find and Replace tool, but no luck yet.

[ctrl_kudorje · Accepted solution]

For This, i would recommend:

1. Creating a new table where only City and State is selected.
2. Remove Duplicates by City and State.
3. Remove all rows where State is null/blank
4. Then utilizing Find and Replace join your main data with the new table. 
   • For more info on Find and Replace: Check out: https://www.thedataschool.co.uk/naledi-hollbruegge/find-replace-alteryx
5. Then remove any columns that are not needed.

[PhilipMannering · Accepted solution]

Very similar to how @ctrl_kudorje describes. Think I would prefer the Join over the Find Replace Tool here though.

[binuacs]

@taran42 One way of dong this with the multirow tool