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

[william516]

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.

[william516]

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.

[Admin]

Attach a small sample workbook with expected results. So that me or others can create codes easily.

[william516]

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

[Admin]

OK. Here you go. Clear all the texts you have written on the sheet.

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

[william516]

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.

[Admin]

HI,

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

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

[william516]

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.

[william516]

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.

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

.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.

[william516]

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!