Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Finding a value in column, another in row, and then placing

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Boston
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a worksheet with a list of parts. the first column contains
    the Part Number. The second column contains one of several components.
    The third column contains the quantity of that component that goes into that
    part.



    Part Number---Component---QtyofComponent

    HL-45S--------M67O-----------3

    HL-45S--------SSS------------2

    HL-45S--------LABRXX---------10

    CVA42---------M67O-----------4

    CVA42---------SSS------------4

    CVA42---------LABRXX---------10

    CVA42---------SOAP-----------1






    Then I have a worksheet which lists the part number in column A, and has
    each possible component as a column heading in columns J, K, L, and M.
    There are four possible components. Not all parts will contain all
    components. The components for each part sometimes are listed in different
    order.



    Here's the second workbook and what it should end up looking like:



























    Part Number

    M67O

    SSS

    LABRXX

    SOAP

    CVA42

    4

    4

    10

    1

    HL-45

    3

    2

    10








    How can I make a macro that

    1. Grabs the Part# and component from WrkBk1, then looks for the row in WrkBk2
    that contains the same part number

    2. Then looks for the column in WrkBk2 that contains the component
    name as the heading and fills in teh quantity in the intersection of the
    component column and the partnumber row.



    Help!!!!!!!!!!!!!!!!!



    I have many ideas...such as creating variables of each of the two values
    i'm searching for, then when I find them, recording the row location of the
    first one, then the column location of the second one as another two variables,
    then telling it to go to cell(var3,var4) blah blah. I also thought
    I could line all of the Partnumbers up by component, and simply paste all
    the values for that component at once, then doing a separate run for the
    next component, etc, thereby reducing the variables and the general confusion
    of it all.



    Thanks so much everyone!




    [ This Message was edited by: Claudette on 2002-03-08 07:04 ]

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Claudette, would a pivot table work meet your requirements?
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Boston
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I don't know, I've never used a pivot table? what is it?

  4. #4
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-08 07:37, Claudette wrote:
    I don't know, I've never used a pivot table? what is it?
    For an introduction to pivot tables, have a look at:

    http://ca.geocities.com/b_davidso/We...vottable1.html

    Let me know if you need any further information.

    Regards,
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-08 07:37, Claudette wrote:
    I don't know, I've never used a pivot table? what is it?
    Also, see the Excel Help topic for "PivotTables: Analyzing data interactively". The table component labels are linked to a descriptive popup text box.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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