# Match identification number and replace missing value with the available data

1. ## 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

2. ## Re: Match identification number and replace missing value with the available data

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.

3. ## Re: Match identification number and replace missing value with the available data

There we go:

C2=

Formula:
`Please Login or Register  to view this content.`

Entered as an array using Ctrl+Shift+Enter instead of just hitting enter.

4. ## Re: Match identification number and replace missing value with the available data

@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)

5. ## Re: Match identification number and replace missing value with the available data

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

Yudlugar has got it!

6. ## Re: Match identification number and replace missing value with the available data

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)))

7. ## Re: Match identification number and replace missing value with the available data

Hi yudlugar, it is a great idea to work it out in that way. Treat them as numeric numbers and do sum-up.

Alternatively, if the question changed to the fact that column B is text values. How to adapt the formula to make it accommodate text values? If the formula allows text values, then it is perfect which is so useful.

8. ## Re: Match identification number and replace missing value with the available data

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.

9. ## Re: Match identification number and replace missing value with the available data

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

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

10. ## Re: Match identification number and replace missing value with the available data

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1