Results 1 to 10 of 10

Thread: Table Or Array That Can Auto Fill In Column Information Based On Two Cells Text Value

  1. #1
    Member
    Join Date
    Jun 2013
    Posts
    52
    Rep Power
    11

    Table Or Array That Can Auto Fill In Column Information Based On Two Cells Text Value

    Ok I have managed to get a VBA macro that takes the text value of two cells and combines them together and displays the text result of that in a separate column however this does not work for my new problem because the end result will not be the two combined words. The idea is to make pasted data easier to automate and complete so what I'm trying to do is this. In the example below is a small version of the way it is set up. I'm only worried about column (B) "Type" and (D) "Manuf" the results will be displayed when the subroutine is run in (E) "Part #" I need to be able to change this information in a table if possible or array because there are countless variations that can and will occur

    A(Address) b(Type) c(Location) d(Manuf) e(Part#) ..........
    03213 photo facp room EST_Add SIGA-PS
    03432 photo hallway EST_Conv 5521FB
    3432 heat boiler Simplex 4098-3232


    Here is the example, the information is pasted in into the worksheet, and the information will stop at column (D) manuf. The idea is to make a subroutine that when run will take the information like this,
    If column B = photo and column D = EST_Add then insert SIGA-PS in column "e" Both column B and D can change and this will affect what numbers will be displayed. It would probably be the easiest to create a table on another sheet and use it for the comparison but im not sure how. Right now I'm doing this all manually and it is getting extremely hard after the 1000+ device to edit. I have no clue how else to do this so I figure I would ask.

    Thanks again.

  2. #2
    Member
    Join Date
    Jun 2013
    Posts
    52
    Rep Power
    11
    I have done some digging and found out from others that I should be using a concatenation and a vlookup however I am completely lost, plus all the examples I have seen show the results being produced automatically. I need it done with a VBA macro or subroutine.

    edit
    Ok I'm still really confused, I have looked up everything and I am feeling really stupid. Can you explain how I can set up this lookup table. This would be set up on another sheet that would not be seen without looking for it. Lets say this sheet is called "Lookup Table". My actual information the example I first put is found on the "Initiating Devices" page. Your saying I would set up the table on "Lookup Table" page. Column A = would be the combined "concatenation" of B2&D2 and column B would be the part number? So example would be Column A = "HeatEST_Add Column B = "SIGA-HRS" Column B result would then be displayed in the "Initiating Devices" page column "E" "Part #" . I guess this is where im lost, because I don't understand how this works. Would it be a VBA macro? The range E7:E2500 would be the range of the values to be displayed. I'm sorry if this doesn't make sense I have a hard time getting this to make sense. Thanks again for any help you can provide.

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Attach a small sample workbook with expected results. So that me or others can create codes easily.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  4. #4
    Member
    Join Date
    Jun 2013
    Posts
    52
    Rep Power
    11

    Question

    here is the requested outcome of what I'm looking for. I need this to run via a Macro that I can call upon. I can not use the automated method because this will only be used after the initial pasting of the information from word into excel. Once that is done it will be up to the inspectors in the field to make sure that ALL part numbers match up correctly as per the site. The table needs to be easy to add stuff too if needed. There are a lot of different manuf and they get categorized as both addressable and conventional devices per manuf. Then there are the different types. Heat, Pull, Photo, Ion, Combo, Duct, Flow, Tamp, Pres, Temp, Control, ....etc this list also goes on for awhile.

    Thanks again
    Attached Files Attached Files

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    OK. Here you go. Clear all the texts you have written on the sheet.

    Code:
    Sub kTest()
        
        Dim MyData      As Variant
        Dim MyTable     As Variant
        Dim i           As Long
        Dim r           As Long
        Dim rFound      As Variant
        
        With Worksheets(1)
            r = .Range("a" & .Rows.Count).End(xlUp).Row
            MyData = .Range("a2:f" & r).Value2
        End With
        
        With Worksheets(2)
            r = .Range("a" & .Rows.Count).End(xlUp).Row
            MyTable = .Range("a2:c" & r).Value2
            .Range("a2:a" & r).Name = "Type"
            .Range("b2:b" & r).Name = "Manu"
    
            .Range("h2").FormulaArray = "=match(f2&""|""&g2,type & ""|"" & manu,0)"
            
            For i = 1 To UBound(MyData, 1)
                'put the Type nad Manu in F2 and G2 respectively
                .Range("f2") = MyData(i, 2) 'type
                .Range("g2") = MyData(i, 4) 'manu
                'match row
                rFound = .Range("h2").Value2
                If Not IsError(rFound) Then
                    'insert part # in the array
                    MyData(i, 5) = MyTable(rFound, 3)
                End If
            Next
        End With
        'write back the array with parts
        Worksheets(1).Range("a2:f" & UBound(MyData, 1) + 1) = MyData
        
    End Sub
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  6. #6
    Member
    Join Date
    Jun 2013
    Posts
    52
    Rep Power
    11
    Thank you for the wonderful subroutine but I'm feeling really stupid I can not get this to work. I can not post the entire workbook because it is way to big of a file. Can you dummy this down a little more. Worksheet 1 I assume is for the "Initiating devices" page but I cant type that in between the () and get it to work.

    I also do not fully understand the ranges A2:F I guess it must look at the whole row and all the columns. I also assume that I just simply need to change A2 to A7 due to the headings and graphics at the top of the page. (I did not send that to keep file size down). All the worksheet that are actually printed rows start at row 7.

    Worksheet 2 should be the "Auto Fill" page. This is pretty much exactly what I sent to you but with a ton more information. however it will still only have the three columns.

    After looking at the formula a little more I can see that h2 and f2 are referring to a range that you have defined. Originally when I read this I thought that was part of the range of worksheet(1). I guess.

    Sorry still learning but I will keep playing around with this. Thanks again for all the hard work.

  7. #7
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    HI,

    I have made some more comments and write the actual sheet names in the code. Hope this helps you.

    Code:
    Sub kTest()
        
        Dim MyData          As Variant
        Dim MyTable         As Variant
        Dim i               As Long
        Dim r               As Long
        Dim rFound          As Variant
        Dim wksIniDevices   As Worksheet
        Dim wksPartNumList  As Worksheet
        Dim rngIniDevices   As Range
        
        Set wksIniDevices = ThisWorkbook.Worksheets("Initiating devices")
        Set wksPartNumList = ThisWorkbook.Worksheets("Sheet2")
        
        With wksIniDevices
            r = .Range("a" & .Rows.Count).End(xlUp).Row         'find last row in col A
            Set rngIniDevices = .Range("a2:f" & r)              'range where your data
            MyData = rngIniDevices.Value2                       'convert the range into variant array as array works faster than range
        End With
        
        With wksPartNumList
            r = .Range("a" & .Rows.Count).End(xlUp).Row
            MyTable = .Range("a2:c" & r).Value2                 'part num list
            .Range("a2:a" & r).Name = "Type"                    'define a name range to use in formula
            .Range("b2:b" & r).Name = "Manu"                    'define a name range to use in formula
    
            .Range("h2").FormulaArray = "=match(f2&""|""&g2,type & ""|"" & manu,0)" 'put the formula. see f2 and g2 - adjust these cells if you use a different cells
            
            For i = 1 To UBound(MyData, 1)
                'put the Type nad Manu in F2 and G2 respectively.adjust f2 and g2 if necessary
                .Range("f2") = MyData(i, 2) 'type
                .Range("g2") = MyData(i, 4) 'manu
                'find match row
                rFound = .Range("h2").Value2
                If Not IsError(rFound) Then
                    'insert part # in the array
                    MyData(i, 5) = MyTable(rFound, 3)
                End If
            Next
        End With
        'write back the array with parts
        rngIniDevices = MyData
        
    End Sub
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  8. #8
    Member
    Join Date
    Jun 2013
    Posts
    52
    Rep Power
    11
    ok I will try this, I cant believe I forgot the "" on the name of the sheet, wow. That's probably the whole problem. I will give it a try thanks again.

  9. #9
    Member
    Join Date
    Jun 2013
    Posts
    52
    Rep Power
    11
    OK you changed a few things from the first one but I just want to get one thing straight if you could explain this part of the code.
    Code:
    With wksIniDevices
            r = .Range("a" & .Rows.Count).End(xlUp).Row         'find last row in col A
            Set rngIniDevices = .Range("a7:f" & r)              'range where your data
            MyData = rngIniDevices.Value2                       'convert the range into variant array as array works faster than range
        End With
        
        With wksPartNumList
            r = .Range("a" & .Rows.Count).End(xlUp).Row
            MyTable = .Range("a2:c" & r).Value2                 'part num list
            .Range("a2:a" & r).Name = "Type"                    'define a name range to use in formula
            .Range("b2:b" & r).Name = "Manu"                    'define a name range to use in formula
    
            .Range("h2").FormulaArray = "=match(f2&""|""&g2,type & ""|"" & manu,0)" 'put the formula. see f2 and g2 - adjust these cells if you use a different cells
            
            For i = 1 To UBound(MyData, 1)
                'put the Type nad Manu in F2 and G2 respectively.adjust f2 and g2 if necessary
                .Range("f2") = MyData(i, 2) 'type
                .Range("g2") = MyData(i, 4) 'manu
                'find match row
                rFound = .Range("h2").Value2
                If Not IsError(rFound) Then
                    'insert part # in the array
                    MyData(i, 5) = MyTable(rFound, 3)
                End If
            Next
        End With
        'write back the array with parts
        rngIniDevices = MyData

    In one of the comments you say to adjust f2 and g2 to the correct cells if needed. I'm confused about what this should actually be. I changed the name of the worksheet to "AUTO FILL PARTS" to fit what I have the sheet name for. I changed that in the set wksPartNumList = thisworkbook.worksheets ("AUTO FILL PARTS")

    Now this is where I get confused
    Code:
    .Range("h2").FormulaArray = "=match(f2&""|""&g2,type & ""|"" & manu,0)"
    I'm assuming that this range is referring to the part number list if that Is the case then the range would be the range for the column Type and Manuf?
    If that is the case then it would change to A2 and b2 correct or is this referring to something else?

    Almost there but still not 100% sure.

  10. #10
    Member
    Join Date
    Jun 2013
    Posts
    52
    Rep Power
    11
    thanks a ton i got the first version of the code to work by changing my range and simply adding the "" that i forgot all about to the name of the worksheet. Wow I feel stupid that's why im far from the expert. It works fine now as far as i can tell thanks once again.

    Great job!

Similar Threads

  1. Replies: 4
    Last Post: 07-08-2013, 05:36 PM
  2. Replies: 3
    Last Post: 05-23-2013, 11:17 PM
  3. Replies: 4
    Last Post: 05-01-2013, 09:49 PM
  4. Replies: 1
    Last Post: 12-04-2012, 08:56 AM
  5. Unmerge Cells and Fill with Duplicate Values
    By princ_wns in forum Excel Help
    Replies: 3
    Last Post: 10-09-2012, 07:36 AM

Tags for this Thread

Posting Permissions

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