+ Reply to Thread
Results 1 to 10 of 10

Fill missing data using vlookup.

  1. #1
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    23

    Fill missing data using vlookup.

    I have two sheets with same data but the 2nd sheet has some data missing. How do I fill those missing data using the data in sheet1, in the appropriate position on sheet2 using vlookup?

    Sheet1

    database.png

    Sheet2

    checking.png

    Sheet1 and Sheet2 should look identical.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    17,939

    Re: Fill missing data using vlookup.

    If you want two identical sheets, why don't you delete Sheet2 then Ctrl-drag the tab for Sheet1 to create a copy, and then rename the copy as Sheet2?

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    23

    Re: Fill missing data using vlookup.

    No, that is not what I meant exactly, I want to search the missing Row ID's in sheet2 and fetch those data from sheet1 to sheet2 and sheet1 may contain more data than sheet2 but I don't want to add those extra data in sheet2, e.x. sheet1 may contain data from Row ID's 1 to 50 but say in sheet2 there are only 30 rows from Row ID's 1 to 30, I don't want to add rows 31 to 50 in sheet2, I just want the missing rows from row 1 to 30.

    Hope this makes a bit more sense

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    17,939

    Re: Fill missing data using vlookup.

    It would help if you attached a sample Excel workbook.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it doesn't work on this forum.

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    23

    Re: Fill missing data using vlookup.

    Hi Pete,

    I've attached a sample workbook. Hope it helps
    Attached Files Attached Files

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    17,939

    Re: Fill missing data using vlookup.

    You can use this formula in A4 of Sheet2:

    =ROW()-1

    and this one in B4:

    =VLOOKUP($A4,Sheet1!$A:$N,COLUMN(),0)

    Copy the formula from B4 across into C4:N4. You will also need to format cells C4 and D4 as dates. Then you can just copy that row of formulae into the other balnk rows.

    Hope this helps.

    Pete

  7. #7
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    23

    Re: Fill missing data using vlookup.

    Thanks Pete.

    But can this be done in one go...like using the go to special option to select all the blank cells and then typing a formula and that will fill all the missing data..something like that.

    I did something like this:

    Added a column after Product ID in sheet1 and used the formula =VLOOKUP([Row ID],Sheet2!$A$1:$A$31,1,0) to find the missing data (the missing values came as #N/A) then added the filter to show only #N/A values and in sheet2 selected the blank cells using goto special and used the formula =VLOOKUP(Sheet1!$A$4:$A$23,Sheet1!$A$4:$N$23,COLUMNS(Sheet1!$A$4:A4),0), which works but feels a bit messy and error prone, can something similar to this be done in a better way like more efficient and less complicated?

    Thanks
    Last edited by 0Cool; 01-12-2019 at 08:29 AM.

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    17,939

    Re: Fill missing data using vlookup.

    Okay, remove the formula from cell A4, then apply autofilter to column A and select blanks from the drop-down. Then put the formula in A4 (which will be the first visible row) and in B4. Copy the formula from B4 across into C4:N4, as previously advised. Then you can copy that row down over the visible rows in one operation, and then Select All in the filter drop-down.

    Hope this helps.

    Pete

  9. #9
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    23

    Re: Fill missing data using vlookup.

    Hi Pete,

    Your formula won't work if column A had alphanumeric values like say C001, C002, C003 ... etc or A-101, A-102, A-103 and so on. The sample file was just a simple example to illustrate what I was trying to do.

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    17,939

    Re: Fill missing data using vlookup.

    Well, I'm not a mind-reader - only you know what is really in column A.

    If it is a simple a code as C001, C002 etc., then you could use this:

    ="C"&TEXT(ROW()-1,"000")

    assuming it starts with C001 in cell A2.

    If it is of the form A-101, A-102 etc., then you can use this:

    ="A-"&TEXT(ROW()+99,"000")

    assuming A-101 is in A2.

    Hope this helps.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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