+ Reply to Thread
Results 1 to 10 of 10

Populating table with data from another table in the same workbook based on criteria (VBA)

  1. #1
    Registered User
    Join Date
    08-28-2007
    Location
    Khabarovsk, Russia
    MS-Off Ver
    2010
    Posts
    10

    Populating table with data from another table in the same workbook based on criteria (VBA)

    Can some one help me with the VBA coding? I am trying to figure out how to populate a table with the data from another table on the different sheet based on criteria entered in the cell. I want to be able enter company ID into cell B2 (Please see Example attached) on Sheet 1 and have Table 1 (Sheet 1) populated with Employee ID's (cells starting A7 through how many there are employee ID's in the Table 2 on the Sheet 2). I want this command to be executed after I type in company ID and press enter. For example, if I enter ABC into cell B2 and press enter I would like to see EE ID 001736727 in the Cell A7, 690608470 in the cell A8 and 113506877 in the cell A9. I have up to 2000 ID's in the real table. Can some one please help me with this?
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,446

    Re: Populating table with data from another table in the same workbook based on criteria (

    Would this serve your purposes.

    Here is the code for a UDF

    Please Login or Register  to view this content.
    Here is the syntax for that UDF

    MLOOKUP(TableArray,LookupValue,LookupRange,[NthMatch])

    Attached is an example
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    08-28-2007
    Location
    Khabarovsk, Russia
    MS-Off Ver
    2010
    Posts
    10

    Re: Populating table with data from another table in the same workbook based on criteria (

    Alan, thank you for the UDF function. I have to enter MLOOKUP formula into each cell. I have several thousands of cells with formulas. This makes it really slow to update. I currently have a different excel formula in the cell that works great but takes over 2 minutes to update. I was looking for the VBA code that will allow me avoid using formulas in the spreadsheet and save time on updating the data. If anyone knows how to do this with the VBA please help.

  4. #4
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,356

    Re: Populating table with data from another table in the same workbook based on criteria (

    Hi alextol,
    try this
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-28-2007
    Location
    Khabarovsk, Russia
    MS-Off Ver
    2010
    Posts
    10

    Re: Populating table with data from another table in the same workbook based on criteria (

    Nilem, thank you! This does exactly what I was looking for! I was wondering if you could help me modify your code a little bit. I want to be able to pull information not for one column only (Employee ID) but for several different columns with information pertaining to the same employee. Please see attached example Table 2, Sheet 2. When the Company ID is entered on the Sheet 1, ABC. I want to see all EE's ID's (like the code already does) plus Employee City and Premium. I want to skip Employee State on purpose (Table 2 in huge and I only need specific columns from it). The result should look like Table 1 on the Sheet 1. Can you please help me with that? I'll be much obliged.
    Attached Files Attached Files

  6. #6
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,356

    Re: Populating table with data from another table in the same workbook based on criteria (

    Hi alextol,
    try again
    Please Login or Register  to view this content.
    PS в Хабаровске не все знают русский язык?
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-28-2007
    Location
    Khabarovsk, Russia
    MS-Off Ver
    2010
    Posts
    10

    Re: Populating table with data from another table in the same workbook based on criteria (

    Spasibo za code! Seichas poprobuy evo. U menya net russkova shrifta.

  8. #8
    Registered User
    Join Date
    08-28-2007
    Location
    Khabarovsk, Russia
    MS-Off Ver
    2010
    Posts
    10

    Re: Populating table with data from another table in the same workbook based on criteria (

    I don't know much about VBA. Can you please explain to me this line of code: Range("A7:C7", Cells(Rows.Count, 1).End(xlUp)(2, 1)).ClearContents I understand that it clears the content so new content can be loaded. I just need to know what "count, 1" row mean and "(2, 1)" coordinates (I assume, row and column) do.

  9. #9
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,356

    Re: Populating table with data from another table in the same workbook based on criteria (

    Cells(Rows.Count, 1) this is the last cell on the sheet in the first column (A1048576 cell)
    Cells(Rows.Count, 1).End(xlUp) it goes to the first filled cell in column A (analogue Ctrl+UpArrow)
    InitialCell(2, 1) is a cell in the second row and the first column relative to the initial cell. If you do not have the data, but only the title, this statement: Cells(Rows.Count, 1).End(xlUp).ClearContents will clear your headline. To avoid this, we use the offset down one line.
    So that the final version: Range("A7:C7", Cells(Rows.Count, 1).End(xlUp)(2, 1)).ClearContents

  10. #10
    Registered User
    Join Date
    08-28-2007
    Location
    Khabarovsk, Russia
    MS-Off Ver
    2010
    Posts
    10

    Re: Populating table with data from another table in the same workbook based on criteria (

    Sorry I didn't reply. I wasn't around computer last couple of weeks. Thank you so much for your help!

+ 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. Populating data in Sheet1 of the attached workbook from Access table
    By aman1234 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-07-2014, 09:02 AM
  2. Replies: 2
    Last Post: 04-28-2014, 05:17 AM
  3. Populating table with data using multiple criteria
    By skip13 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-06-2013, 11:19 PM
  4. [SOLVED] Populating a table based on the value match from another table
    By wnarx in forum Excel General
    Replies: 6
    Last Post: 08-31-2012, 12:30 AM
  5. [SOLVED] Populating a table based on values in another table
    By Bri in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-25-2006, 09:30 PM

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