+ Reply to Thread
Results 1 to 10 of 10

Match identification number and replace missing value with the available data

  1. #1
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010, 2019
    Posts
    422

    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
    Attached Files Attached Files

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

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

    I did it with a helper column.

    C2=

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    D2=

    Formula: copy to clipboard
    Please Login or Register  to view this content.


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

  3. #3
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

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

    There we go:

    C2=


    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Entered as an array using Ctrl+Shift+Enter instead of just hitting enter.
    Last edited by daffodil11; 08-07-2013 at 11:15 AM. Reason: absolute references

  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    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. #5
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    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. #6
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    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. #7
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010, 2019
    Posts
    422

    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.
    Last edited by billj; 08-07-2013 at 11:59 AM.

  8. #8
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    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. #9
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010, 2019
    Posts
    422

    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. #10
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Counting The number of Identification References in a cell
    By newtoexcel24 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-15-2013, 02:57 AM
  2. [SOLVED] Finding the identification number of a check box
    By Steverizer in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-11-2013, 08:51 PM
  3. Number identification
    By HelpExc in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-14-2012, 02:15 PM
  4. Excel 2007 : Need to match data and find missing
    By topgun3406 in forum Excel General
    Replies: 5
    Last Post: 04-02-2011, 07:31 PM
  5. Generating an Identification number
    By Awheeler in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-14-2006, 02:51 AM

Bookmarks

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