+ Reply to Thread
Results 1 to 5 of 5

retrieve rows from table on sheet1 to insert in subtable on sheet2

  1. #1
    Registered User
    Join Date
    06-20-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    retrieve rows from table on sheet1 to insert in subtable on sheet2

    Hi,

    I would like to use a tabulation of hundreds of chemical names and corresponding properties as a 'database' on Sheet1. The 'database' would begin in row 10 of Sheet1 (named "DATABASE"). Column A of Sheet1 would contain an substance ID code (integer), column B would contain the alphanumeric name of the substance, Column C would have its molecular weight (single or variant), etc.

    I want to be able to type in a shorter list (perhaps 10 or 20) of chemical names and proportions (adding up to 100 percent) in column B and column C, on Sheet2, beginning at row 15.

    Then, I want use the names of the items in column B on Sheet2 in a loop search for the properties of those chemicals in the 'database' on Sheet1 and to copy the properties from the appropriate rows in the 'database' and 'pasted' into "columns F to whatever" in the rows on Sheet2.

    In other words, I want to extract the properties for a subset of the database components and concentrate that information on Sheet2, so that I can do additional VBA things with the subset of information.

    I prefer to use VBA and Excel to handle this, rather than to use C++ or Fortran, etc.

    Help!
    Last edited by ycubed; 06-20-2010 at 11:21 PM. Reason: remove smilie

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: retrieve rows from table on sheet1 to insert in subtable on sheet2

    Please attach a sample workbook which illustrates how your data are laid out, and the results you want.

  3. #3
    Registered User
    Join Date
    06-20-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: retrieve rows from table on sheet1 to insert in subtable on sheet2

    Thanks for your response. I am absolutely new to the forum, so please be patient. I really want to learn how to do things in VBA and Excel that I am able to do in FORTRAN or C++.

    I sent you a sample workbook (.xls) with the general notion of what I would like to achieve. This will continue improving as I learn more VBA and Excel tricks to make it easier to use and to modify later on.

    I really appreciate your help.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: retrieve rows from table on sheet1 to insert in subtable on sheet2

    I'm not sure that you need VBA. You could just use a lookup formula. E.g. enter this in F15 and copy down. The 1 at the end will vary according to the column you are looking up (e.g. for MOLE WT it will be 3 as this is third column in table). If you put each of your headings in a single cell, you could match by that and avoid having to vary the final number each time.

    =INDEX(DATABASE!$A$10:$AE$209,MATCH($B15,DATABASE!$B$10:$B$209,0),1)

    Does that sound like it will do the job?

    EDIT: another alternative might be Advanced Filter.

  5. #5
    Registered User
    Join Date
    06-20-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: retrieve rows from table on sheet1 to insert in subtable on sheet2

    Stephen,

    Thanks for the assist. I've been reading up on the VLOOKUP, INDEX and MATCH features. Your suggested method may be what this problem needs. Let me ponder it some more. I may (and probably will) have additional questions about it, later on. For now, thanks for the help.

    You did good! You deserve a pint!

    Best,

    ycubed
    Last edited by ycubed; 06-22-2010 at 01:04 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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