Match identification number and replace missing value with the available data

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

I did it with a helper column.

C2=

Formula:

Please Login or Register  to view this content.

D2=

Formula:

Please Login or Register  to view this content.

I will continue to try to truncate to a single expression.

@daffodil, I came up with a similar approach but figured if there was more than one occurance of the ID number with a valid ID2 it would return the wrong number. Although your formula works on the example workbook I extended it to:

=IF(ISNUMBER(B2),B2,SUMPRODUCT(IF(A$2:A$9=A2,1,0),B$2:B$9)/SUMPRODUCT(IF(A$2:A$9=A2,1,0),IF(ISNUMBER(B$2:B$9),1,0)))
again entered as an array formula (ctrl+shift+enter)

There's always a bigger fish in the Excel sea.

Yudlugar has got it!

Thanks, for what it's worth your "sum(if(" approach is better than my "sumproduct(if" version. Although I don't think the isnumber part of you sum is neccessary, so combining the two (untested):
=IF(ISNUMBER(B2),B2,SUM(IF(A$2:A$9=A2,B$2:B$9,0))/SUM(IF(AND(A$2:A$9=A2,ISNUMBER(B$2:B$9)),1,0)))

That's beyond my abilities. It would be some sort of match/index/lookup combination I guess.. I'd probably right a custom vba function for it.

Yes, the match/index combination may make it work.

Is there anyone who can work this out? That will be really helpful.

In the absence of anyone else, the best I've come up with is:
=INDEX(IF(IF(A$2:A$9=A2,1,0)*IF(B$2:B$9="#N/A N/A",0,1)=1,B$2:B$9,""),SUM(IF(IF(A$2:A$9=A2,1,0)*IF(B$2:B$9="#N/A N/A",0,1)=1,ROW(A$2:A$9)-1,"")))
Again an array formula (ctrl+shift+enter) but it will only work if you have only one ID1 with the correct ID2