Match identification number and replace missing value with the available data

[billj]

Hello Sir,

I have a file with two columns (A, B). Column B has some missing values which need to be identified using column A. In theory, if two cases have the same number in column A, then they should have the same number as well in column B.

I made a simple example and attached here.

For example, in column A the number 2770 appears 3 times, and their corresponding values in column B are #N/A N/A, #N/A N/A and 551010 respectively. Based on the matching principle, I can use 551010 to replace the other two values of missing data.

Is there a way to use formula to implement it? Thanks in advance for your help