Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Comparing description between files, if matches pull price data

  1. #1
    New Member
    Join Date
    Apr 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Comparing description between files, if matches pull price data

    Hello,

    it's been a while since I wrote in VBA so I'm a bit rusty but i am hoping I could get some help.

    here is the context:
    I have 9 excel files with each excel file which has rows with a description and associated price, we'll call these excel files Prices. I then have 5 excel files that require the price data from the Prices excel files, well call these 5 excel files Main Data. the prices are placed in the Main data file based on the description, sometimes the description won't be exact but similar. each of the Main Data excel files has 3 tabs (similar tab names among the excel files). Currently I do this all by hand and decided it would make more sense to write up a macro.

    what I am wanting to do is create a macro that will compare the descriptions that are in the Prices excel files to the Main Data files. if the description matches, or is similar (using instr() possibly?), I want to pull the price in the same row as the description in prices excel file and place it in the main data excel file, again in the same row as the description.

    I may have to run the code multiple times for each Prices excel file, which is fine. I'm hoping someone can help get me started in the right path and I can go from there.

    any help would be appreciate.

    I hope this makes sense, if you need anymore explanation please let me know.

  2. #2
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing description between files, if matches pull price data

    Quote Originally Posted by spyderjam View Post
    ...............

    I hope this makes sense, if you need anymore explanation please let me know.
    Hi spyderjam


    . You explained you requirement quite well
    . But I think it is still not to clear to anyone trying to help you exactly what you want. It is much easier for someone to help if you provide a good “picture” of what you want. - As always, “A (Good!) Picture paints a thousand words”

    . Provide shortened sample data (exact data can be changed if any is sensitive, but it should represent all typical scenarios) Also importantly give some hand prepared sample output of exactly how the final output should look like in the Excel File after running any macro based on your actual example data after running any macro.


    . There are various ways to do this. The first is preferred by this Forum for excel files as then everyone can see wot is going on quickly..

    . 1 Check out the links in my signature below. In particular those for posting screenshots which we can copy into an excel file. It is worth it in the long run taking the time to learn how to use these free tools
    Or
    . 2 Up left in the Thread editor is a table icon. Click that, create an appropriately sized table and fill it in. (To get this icon up in the Reply window you may need to click on the “Go Advanced” Button next to the Reply Button)
    Or
    . 3 Supply us with example Excel files
    . For example send over this free thing: Box Net,
    Remember to select Share after uploading and give us the link they provide.


    Alan
    P.s. Welcome to the Board!

  3. #3
    New Member
    Join Date
    Apr 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Re: Comparing description between files, if matches pull price data

    Thanks for replying Alan, I took your advice and got the HTML add-in for Excel. I'll paste the tables below and explain what they are for:

    So what I am trying to do is compare the description in the Prices Spreadsheet to the W-ElecBulk (or Main Data) spreadsheet. If the description is the same, or similar if possible, take the price from the Prices spreadsheet and put it in the Main Data Spreadsheet.

    Excel 2010
    BCDEFGHI
    1Description
    2UnitTotal
    3
    425kV Expulsion fuse cut-out 200A, 125kV BIL c/w pole mount bracketEA$65.00
    5Crossarm V Brace 72" x 3/16" x 2" x 2"EA$150.00
    6Guy HookEA$7.00
    7Guy grip for 3/8" steel EHS Guy Wire$3.00
    8Steel Guy Wire 3/8" Steel EHS Guy WireM$2.00
    9Screw AnchorEA$70.00
    10Yellow Guy Guard EA$7.00
    115/8" X 10' Ground rod copper cladEA$19.00
    12Meter Revenue Current Transformer, 600V, 300:5 EA$400.00
    13Meter Revenue Current Transformer, 600V, 800:5 EA$300.00
    14Meter Revenue Voltage Transformer, 600/120VEA$300.00
    15Meter Revenue Voltage Transformer, 4160/120V EA$1,600.00
    166" PVC DuctM$15.00
    176" PVC Bend 90 Deg$109.00
    18PVC Bell Ends$10.00
    196" PVC Couplings$16.00
    203/4" Ground rod clamp$3.00
    216" PVC Spacers$6.00

    Prices Spreadsheet






    Excel 2010
    FGHI
    14DESCRIPTION QTYUNIT of MEAS.UNIT PRICE
    15
    16
    17
    18Model 1
    19
    20Transformer Bracket1.00Ea$25.00
    21
    22
    23Pole
    24
    25Wood Pole (Treated) - Class 4, Length: 40 ft2.00Ea$554.00
    26Wood Pole (Treated) - Class 2, Length: 40 ft: 1.00Ea$739.00
    27Pin Insulator 9.00Ea$154.94
    28Centre Bracket for Pin Insulator 3.00Ea$15.00
    29Combined bracket for fuse cutout and arrestor 1.00Ea$50.00
    3025 kV fuse cutout  c/w fuse 1.00Ea$237.33
    31Overhead line termination to fused cutout2.00Ea$35.00
    3225kV Lightning arrester1.00Ea$77.35
    33Tangent Fiberglass Crossarm 3.00Ea$112.03
    34Secondary Rack c/w insulators.6.00Ea$35.00

    W-ElectBulk







    again, any help is appreciated. Thanks!

  4. #4
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing description between files, if matches pull price data

    Quote Originally Posted by spyderjam View Post
    Thanks for replying Alan, I took your advice and got the HTML add-in for Excel. I'll paste the tables below and explain what they are for:

    So what I am trying to do .........
    Hi spyderjam
    .. Well done on learning to use the Tool. New Member's don't usually take the bother. Seems quite easy. But it is late here now. If someone (Like hiker95) does not jump in and beat me to it, I will try to do it for you tomorrow

    Alan
    Bavaria

  5. #5
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing description between files, if matches pull price data

    Hi Spidejam,
    . At first glance I do not see any description in the Prices Spreadsheet that looks anything like the descriptions in the W-ElecBulk (or Main Data) spreadsheet.
    . It is always best to supply test data showing how things look before AND after running any macro, with the after based on the actual data you supply. ( see my Post #2 )
    . This is especially important as your descriptions may be similar but not exactly the same , ( as you mentioned).

    . I will make a start but please supply that info asap

    Alan

  6. #6
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing description between files, if matches pull price data

    Hi Spidejam,
    . I drop off the initial bits I have done. Codes are a bit messy and provisional, for now, but work…
    . It may get you started or form the basis for applying more exactly to your needs if you reply back ….

    . For now and for demonstration purposes I will keep your Prices sheet exactly as you gave. I modify your main sheet to look like this at the start Before running any codes

    Using Excel 2007
    -
    A
    B
    C
    D
    13
    14
    DESCRIPTION
    QTY
    UNIT of MEAS.
    UNIT PRICE
    15
    25kV Expulsion fuse
    16
    Current Transformer, 600V, 800:5
    17
    18
    _Model 1
    19
    20
    Transformer Bracket
    1
    Ea
    21
    22
    23
    _Pole
    24
    25
    Wood Pole (Treated) - Class 4, Length: 40 ft
    2
    Ea
    26
    Wood Pole (Treated) - Class 2, Length: 40 ft:
    1
    Ea
    27
    Pin Insulator
    9
    Ea
    28
    Centre Bracket for Pin Insulator
    3
    Ea
    29
    Combined bracket for fuse cutout and arrestor
    1
    Ea
    30
    25 kV fuse cutout c/w fuse
    1
    Ea
    31
    Overhead line termination to fused cutout
    2
    Ea
    32
    25kV Lightning arrester
    1
    Ea
    33
    Tangent Fiberglass Crossarm
    3
    Ea
    34
    Secondary Rack c/w insulators.
    6
    Ea
    35
    Voltage Transformer, 4160/120V
    Ea
    W-ElectBulk

    . After running the codes I give you at the end it will look something** like this

    Using Excel 2007
    -
    A
    B
    C
    D
    13
    14
    DESCRIPTION
    QTY
    UNIT of MEAS.
    UNIT PRICE
    15
    25kV Expulsion fuse
    65.00 €
    16
    Current Transformer, 600V, 800:5
    300.00 €
    17
    18
    _Model 1
    19
    20
    Transformer Bracket
    1
    Ea
    21
    22
    23
    _Pole
    24
    25
    Wood Pole (Treated) - Class 4, Length: 40 ft
    2
    Ea
    26
    Wood Pole (Treated) - Class 2, Length: 40 ft:
    1
    Ea
    27
    Pin Insulator
    9
    Ea
    28
    Centre Bracket for Pin Insulator
    3
    Ea
    29
    Combined bracket for fuse cutout and arrestor
    1
    Ea
    30
    25 kV fuse cutout c/w fuse
    1
    Ea
    31
    Overhead line termination to fused cutout
    2
    Ea
    32
    25kV Lightning arrester
    1
    Ea
    33
    Tangent Fiberglass Crossarm
    3
    Ea
    34
    Secondary Rack c/w insulators.
    6
    Ea
    35
    Voltage Transformer, 4160/120V
    Ea
    1,600.00 €
    W-ElectBulk

    . Code 1: This is fairly simple and straight forward. Based on simple looping ( 2 LOOPS ) through all rows in both description columns . (Using VBA Arrays Approach). I have arbitrarily chosen a couple of search criteria for matching your descriptions. I used the VBA Instr() Function idea you mentioned (In conjunction with the VBA.Mid Function ), and also just to put another possibility in did a VBA Right() Function idea. The Instr() looks for part of the Main Description in The Prices description. The Right() Function compares the last 20 characters for both Main and Price Descriptions. At the end of the day you will have to play around, I guess, with both the actual criteria combinations you use and the various position and length parameters (the Function arguments) to get the best chance of matching and therefore pulling out the right stuff…. And, if possible, consider organizing your data in some other way to simplify trying to match descriptions..

    . Code 2: You may not want at this stage to look at this. I was practicing Error Handling just now so did a code that uses extensive use of that. Basically it does a search of the entire Price Descriptions in one go, for each main row, to do away with ONE LOOP. It is also only catching matches in the last right end bits of the descriptions**. In the unlikely event you both choose to look at this and learn about the error handling ideas, then here is some notes I made to help me remember / understand (WORD 2007 .doc ), and other info:-
    https://app.box.com/s/8zkhjcmbxrqnlnexqpktuy41clgqm4zo
    On Error WTF? | Excel Matters


    Alan

    P.s. There are lots of green comments. I tend not to like using codes myself that I do not understand so I put lots of comments in. Just a personal choice. The MrExcel Coe window adds carriage returns which ma make the code here difficult to follow. You can wipe them comments out in one go easily – see Posts #7 shg and #10 Smitty, here for example:-
    http://www.mrexcel.com/forum/about-b...ents-code.html
    . Also if you copy the code to a Module in the Visual Basic Development environment then the extra carriage returns are not included so a lot of the comments are only to see if you want too by scrolling right. So they are less distracting there.


    Codes:

    Code:
    Option Explicit 'Not necerssary but forces to dim everything, reducing memory and making errors more obvios
    Sub Code1_SpiderjamPricePullVBAArrayLooping()
    Dim wsPrices As Worksheet: Set wsPrices = ThisWorkbook.Worksheets("Prices Spreadsheet") 'Give the abbreviations the variuos Methods Properties, etc. of ...
    Dim wsMain As Worksheet: Set wsMain = ThisWorkbook.Worksheets("W-ElectBulk") 'Worksheets Object (Obtainable through typing . dot )
     
    'Define start row numbers in sheets
    Dim sP As Long, sM As Long: Let sP = 4: Let sM = 15
     
    'Define Ranges for Data Arrays and "Capture" the data values..
    Dim DescP() As Variant, DescM() As Variant ' Descriptions Array Variables must be dynamic to work in next lines
    Let DescP() = wsPrices.Range("A" & sP & ":A" & wsPrices.Cells(Rows.Count, 1).End(xlUp).Row & "").Value 'One line "capture" of values of spradsheet to dynamic array is conveniently allowed by VBA and the last row here is...
    Let DescM() = wsMain.Range("A" & sM & ":A" & wsMain.Cells(Rows.Count, 1).End(xlUp).Row & "").Value 'determined by by quasi going to the last row in first column and going backUp untill a cell with something in it is found (.End Property, and then obtaining the that row number from the row property
     
    Dim PricesP() As Variant, PricesM() As Variant 'Prices , and Prices to pull data Arrays
    Let PricesP() = wsPrices.Range("H" & sP & ":H" & wsPrices.Cells(Rows.Count, 1).End(xlUp).Row & "").Value 'Column H for prices, (last row that for column A)
    ReDim PricesM(1 To UBound(DescM(), 1), 1 To UBound(DescM(), 2)) 'For now leave the output array empty, but give it a consistant size for later looping
     
     
    'Main Looping to check for similar Descriptions
    Dim rP As Long, rM As Long 'variables for rows within the arrays
        For rP = 1 To UBound(DescP(), 1) 'go down each price row, and for each of these rows...
            For rM = 1 To UBound(DescM(), 1) 'go down the entire Main desription
                'This is the bit where some tricky comparisons must be made
                '                Debug.Print "DescP=" & DescP(rP, 1) & " DescM=" & DescM(rM, 1)
                '                Debug.Print VBA.Mid(DescM(rM, 1), 2, 15)
                '
                '                Debug.Print VBA.Right(DescP(rP, 1), 20)
                '                Debug.Print VBA.Right(DescM(rM, 1), 20)
                If VBA.InStr(1, DescP(rP, 1), (VBA.Mid(DescM(rM, 1), 2, 15)), 0) > 1 Or VBA.Right(DescP(rP, 1), 33) = VBA.Right(DescM(rM, 1), 33) Then '
                'If VBA.Right(DescP(rP, 1), 20) = VBA.Right(DescM(rM, 1), 20) Then  'Check for match in lastparts in both lists
                'If VBA.InStr(1, DescP(rP, 1), (VBA.Mid(DescM(rM, 1), 2, 15)), 0) > 1 Then 'Check for part of description im main row in full description in prices
                Let PricesM(rM, 1) = PricesP(rP, 1) 'Put price for matched description in Main Price Array
                Else ' No near match so do nothing
                End If
            Next rM
        Next rP
       
    'Output main prices
    Let wsMain.Range("D" & sM & "").Resize(UBound(PricesM(), 1)) = PricesM() 'A typical VBA Allowse "One Liner" Output of an Array to a Range: Resize the output start cell to the size of the array and then make the values of that range equal to the array
     
    End Sub 'Code1_SpiderjamPricePullVBAArrayLooping()
    '
     
    '
    '
    '
    '
    '
     
    Sub Code2_SpiderjamPricePull_MatchWithOnError()
    On Error GoTo TheEnd 'Main error handler for unexpected errors - go to near the end and do anything important should an unexpected error occur
    Dim wsPrices As Worksheet: Set wsPrices = ThisWorkbook.Worksheets("Prices Spreadsheet") 'Give the abbreviations the variuos Methods Properties, etc. of ...
    Dim wsMain As Worksheet: Set wsMain = ThisWorkbook.Worksheets("W-ElectBulk") 'Worksheets Object (Obtainable through typing . dot )
    wsPrices.Columns(10).ClearContents 'Clear the column that I later use for a temporary range in the .Match second argument.
     
    'Define start row numbers in sheets
    Dim sP As Long, sM As Long: Let sP = 4: Let sM = 15
     
    'Define Ranges for Data Arrays and "Capture" the data values..
    Dim DescP() As Variant, DescM() As Variant ' Descriptions Array Variables, - must be dynamic to work in next lines
    Let DescP() = wsPrices.Range("A" & sP & ":A" & wsPrices.Cells(Rows.Count, 1).End(xlUp).Row & "").Value 'One line "capture" of values of spradsheet to dynamic array is conveniently allowed by VBA and the last row here is...
    Let DescM() = wsMain.Range("A" & sM & ":A" & wsMain.Cells(Rows.Count, 1).End(xlUp).Row & "").Value 'determined by by quasi going to the last row in first column and going backUp untill a cell with something in it is found (.End Property, and then obtaining the that row number from the row property
     
    Dim PricesP() As Variant, PricesM() As Variant 'Prices , and Prices to pull data Arrays
    Let PricesP() = wsPrices.Range("H" & sP & ":H" & wsPrices.Cells(Rows.Count, 1).End(xlUp).Row & "").Value 'Column H for prices, (last row that for column A)
    ReDim PricesM(1 To UBound(DescM(), 1), 1 To UBound(DescM(), 2)) 'For now leave the output array empty, but give it a consistant size for later looping
     
    'Create Arrays for Right end of descriptions, - it may look like a lot of extra overhead for code, But VBA Array workings go very fast
    Dim rP As Long, rM As Long 'variables for rows within the arrays
    Dim RechtsMain() As String, RechtsPrices() As String ' We are going to make these fixed dimension arrays so an assign string type
    ReDim RechtsMain(1 To UBound(DescM(), 1), 1 To UBound(DescM(), 2)) 'Use ReDim just as Dim only takes numbers does not take Variables
        For rM = 1 To UBound(DescM(), 1) Step 1
        RechtsMain(rM, 1) = VBA.Right(DescM(rM, 1), 20)
        Next rM
     
    ReDim RechtsPrices(1 To UBound(DescP(), 1), 1 To UBound(DescP(), 2))
        For rP = 1 To UBound(DescP(), 1) Step 1
        RechtsPrices(rP, 1) = VBA.Right(DescP(rP, 1), 20)
        Next rP
    Let wsPrices.Range("J1").Resize(UBound(DescP(), 1), 1).Value = RechtsPrices() 'Write out a column to use in Application.Match - resize J1 to size of array for right bit of prices description then assign the values of this range to the array in this allowed "VBA one liner"
     
    'Main ONE LOOP to check for similar Descriptions
     
        'For rP = 1 To UBound(DescP(), 1) 'go down each price row, and for each of these rows...'NOT NEEDED IN Code2
            For rM = 1 To UBound(DescM(), 1) 'go down the entire Main desription
                'This is the bit where some tricky comparisons must be made
                '                Debug.Print "DescP=" & DescP(rP, 1) & " DescM=" & DescM(rM, 1)
                '                Debug.Print VBA.Mid(DescM(rM, 1), 2, 15)
                '                Debug.Print VBA.Right(DescP(rP, 1), 33)
                'If VBA.InStr(1, DescP(rP, 1), (VBA.Mid(DescM(rM, 1), 2, 15)), 0) > 1 Or VBA.Right(DescP(rP, 1), 33) = DescM(rM, 1) Then ' Check for part of dscription im main row in full description in prices
                'If VBA.Right(DescP(rP, 1), 20) = VBA.Right(DescM(rM, 1), 20) Then  'Check for match in lastparts in both lists
                'If RechtsPrices(rP, 1) = RechtsMain(rM, 1) Then 'Check for match in lastparts in both lists
                On Error GoTo nextbit ' This intended to make VBA go on to the next Main Row, rather than crashing if no match is found , as this would cause the .Match Function to error - We expect this error to sometimes occur, there are many alternatives to this method that will not error, for example http://www.excelforum.com/excel-new-users-basics/1072093-match-with-on-error-on-error-resume-next-works-on-error-goto-only-works-once-err-clear.html  , but I am playing with error here for fun. I can reassign ann error handler as long as the exception is not raised####
                'Let rP = Application.WorksheetFunction.Match(RechtsMain(rM, 1), wsPrices.Columns(10), 0) 'If this finds a match (so does not error) it returns the row number for the Prices array where the match occurred
                Let rP = Application.WorksheetFunction.Match(RechtsMain(rM, 1), wsPrices.Range("J1:J" & wsPrices.Cells(Rows.Count, 10).End(xlUp).Row & ""), 0) 'Found in the practice that a specific range rather thann the whole column for the arrgument tended to work quicker : Post #6 http://www.mrexcel.com/forum/excel-questions/792647-simple-data-sort-merge-code.html
                Let PricesM(rM, 1) = PricesP(rP, 1) 'Put price for matched description in Main Price Array
     
    nextbit:    On Error GoTo -1 'Clears the exception####, that means, get VBA out of its "I think error are being handeled so I will get confused and go back to defailt mode if another comes along". Does not disable the On Error GoTo error handler but Deactivates that error handler so it can be used again.
            Next rM ' Go to next main row
            'On Error GoTo -1' Not needed in this case as it would have been done in any event a couple of lines above
            'On Error GoTo 0 'disables (kills) last goto error handler, actually also not needed - can re assign an error handler, if it is not switched on
            On Error GoTo TheEnd 'Enable the original main error handler - On Error GoTo -1 has Cleared the exception and On Error GoTo 0 has killed the last so we can enable a new one (active)
        'Next rP' NOT NEEDED IN Code2
     
    'Output main prices
    Let wsMain.Range("D" & sM & "").Resize(UBound(PricesM(), 1)) = PricesM() 'A typical VBA Allowse "One Liner" Output of an Array to a Range: Resize the output start cell to the size of the array and then make the values of that range equal to the array
     
    TheEnd:
    'Do anything here you should in the case of an error, like turning any important things back on
    wsPrices.Columns(10).ClearContents 'Clear contents of column used for tempory range for use in .Match second argument.
    End Sub 'Code2_SpiderjamPricePull_MatchWithOnError()
    '

  7. #7
    New Member
    Join Date
    Apr 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing description between files, if matches pull price data

    Hi Alan,

    sorry, I forgot to mention that the tables I pasted above is a small "sample" of the spreadsheet. For example, the W-ElecBulk spreadsheet has over 500 rows.... didn't think you'd want to read all of that lol.

    I would have responded to your message yesterday but for some reason I didn't' get a notification until now that you replied.

    Thanks for taking the time to help me with this! it is very appreciated! I'm going to read through all the code and your previous messages; I'm sure after all that I'll have some questions.

  8. #8
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing description between files, if matches pull price data

    Quote Originally Posted by spyderjam View Post

    sorry, I forgot to mention that the tables I pasted above is a small "sample" of the spreadsheet. For example, the W-ElecBulk spreadsheet has over 500 rows.... didn't think you'd want to read all of that lol.......
    better to keep sample data reduced anyways..looks better in the thread for others to follow.., - just keep it typical, representative , etc. of the real data and scenarious...

    catch you tomorrow maybe... Post as many questions you want in the meantime. I will try to answer them if / when I can

    Alan

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
  •