Comparing description between files, if matches pull price data

spyderjam

New Member
Joined
Apr 30, 2015
Messages
3
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.
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
...............

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!
 

spyderjam

New Member
Joined
Apr 30, 2015
Messages
3
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.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-left: 1px solid black;;">Description</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Unit</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Total</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">25kV Expulsion fuse cut-out 200A, 125kV BIL c/w pole mount bracket</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">EA</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">$65.00</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Crossarm V Brace 72" x 3/16" x 2" x 2"</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">EA</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$150.00</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Guy Hook</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">EA</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$7.00</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Guy grip for 3/8" steel EHS Guy Wire</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$3.00</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Steel Guy Wire 3/8" Steel EHS Guy Wire</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">M</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$2.00</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Screw Anchor</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">EA</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">$70.00</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Yellow Guy Guard </td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">EA</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$7.00</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">5/8" X 10' Ground rod copper clad</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">EA</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$19.00</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Meter Revenue Current Transformer, 600V, 300:5 </td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">EA</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">$400.00</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Meter Revenue Current Transformer, 600V, 800:5 </td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">EA</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">$300.00</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Meter Revenue Voltage Transformer, 600/120V</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">EA</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">$300.00</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Meter Revenue Voltage Transformer, 4160/120V </td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">EA</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$1,600.00</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6" PVC Duct</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">M</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$15.00</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6" PVC Bend 90 Deg</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$109.00</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">PVC Bell Ends</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$10.00</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6" PVC Couplings</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$16.00</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3/4" Ground rod clamp</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$3.00</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6" PVC Spacers</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$6.00</td></tr></tbody></table><p style="width:6.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Prices Spreadsheet</p><br /><br />


<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">14</td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">DESCRIPTION </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">QTY</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">UNIT of MEAS.</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">UNIT PRICE</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;background-color: #FFCC99;;">Model 1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFCC99;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFCC99;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFCC99;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Transformer Bracket</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1.00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Ea</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">$25.00</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;;">Pole</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Wood Pole (Treated) - Class 4, Length: 40 ft</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2.00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Ea</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">$554.00</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Wood Pole (Treated) - Class 2, Length: 40 ft: </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1.00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Ea</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">$739.00</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Pin Insulator </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">9.00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Ea</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">$154.94</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Centre Bracket for Pin Insulator </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3.00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Ea</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">$15.00</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Combined bracket for fuse cutout and arrestor </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1.00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Ea</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">$50.00</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">25 kV fuse cutout  c/w fuse </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1.00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Ea</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">$237.33</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Overhead line termination to fused cutout</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2.00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Ea</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">$35.00</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">25kV Lightning arrester</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1.00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Ea</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">$77.35</td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Tangent Fiberglass Crossarm </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3.00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Ea</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">$112.03</td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Secondary Rack c/w insulators.</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6.00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Ea</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">$35.00</td></tr></tbody></table><p style="width:6.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">W-ElectBulk</p><br /><br />



again, any help is appreciated. Thanks!
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
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
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
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
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
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-board/795476-comments-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:
[color=blue]Option[/color] [color=blue]Explicit[/color] [color=lightgreen]'Not necerssary but forces to dim everything, reducing memory and making errors more obvios[/color]
[color=blue]Sub[/color] Code1_SpiderjamPricePullVBAArrayLooping()
[color=blue]Dim[/color] wsPrices [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wsPrices = ThisWorkbook.Worksheets("Prices Spreadsheet") [color=lightgreen]'Give the abbreviations the variuos Methods Properties, etc. of ...[/color]
[color=blue]Dim[/color] wsMain [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wsMain = ThisWorkbook.Worksheets("W-ElectBulk") [color=lightgreen]'Worksheets Object (Obtainable through typing . dot )[/color]
 
[color=lightgreen]'Define start row numbers in sheets[/color]
[color=blue]Dim[/color] sP [color=blue]As[/color] [color=blue]Long[/color], sM [color=blue]As[/color] Long: [color=blue]Let[/color] sP = 4: [color=blue]Let[/color] sM = 15
 
[color=lightgreen]'Define Ranges for Data Arrays and "Capture" the data values..[/color]
[color=blue]Dim[/color] DescP() [color=blue]As[/color] [color=blue]Variant[/color], DescM() [color=blue]As[/color] [color=blue]Variant[/color] [color=lightgreen]' Descriptions Array Variables must be dynamic to work in next lines[/color]
[color=blue]Let[/color] DescP() = wsPrices.Range("A" & sP & ":A" & wsPrices.Cells(Rows.Count, 1).End(xlUp).Row & "").Value [color=lightgreen]'One line "capture" of values of spradsheet to dynamic array is conveniently allowed by VBA and the last row here is...[/color]
[color=blue]Let[/color] DescM() = wsMain.Range("A" & sM & ":A" & wsMain.Cells(Rows.Count, 1).End(xlUp).Row & "").Value [color=lightgreen]'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[/color]
 
[color=blue]Dim[/color] PricesP() [color=blue]As[/color] [color=blue]Variant[/color], PricesM() [color=blue]As[/color] [color=blue]Variant[/color] [color=lightgreen]'Prices , and Prices to pull data Arrays[/color]
[color=blue]Let[/color] PricesP() = wsPrices.Range("H" & sP & ":H" & wsPrices.Cells(Rows.Count, 1).End(xlUp).Row & "").Value [color=lightgreen]'Column H for prices, (last row that for column A)[/color]
[color=blue]ReDim[/color] PricesM(1 [color=blue]To[/color] [color=blue]UBound[/color](DescM(), 1), 1 [color=blue]To[/color] [color=blue]UBound[/color](DescM(), 2)) [color=lightgreen]'For now leave the output array empty, but give it a consistant size for later looping[/color]
 
 
[color=lightgreen]'Main Looping to check for similar Descriptions[/color]
[color=blue]Dim[/color] rP [color=blue]As[/color] [color=blue]Long[/color], rM [color=blue]As[/color] [color=blue]Long[/color] [color=lightgreen]'variables for rows within the arrays[/color]
    [color=blue]For[/color] rP = 1 [color=blue]To[/color] [color=blue]UBound[/color](DescP(), 1) [color=lightgreen]'go down each price row, and for each of these rows...[/color]
        [color=blue]For[/color] rM = 1 [color=blue]To[/color] [color=blue]UBound[/color](DescM(), 1) [color=lightgreen]'go down the entire Main desription[/color]
            [color=lightgreen]'This is the bit where some tricky comparisons must be made[/color]
            [color=lightgreen]'                Debug.Print "DescP=" & DescP(rP, 1) & " DescM=" & DescM(rM, 1)[/color]
            [color=lightgreen]'                Debug.Print VBA.Mid(DescM(rM, 1), 2, 15)[/color]
            [color=lightgreen]'[/color]
            '                Debug.Print VBA.Right(DescP(rP, 1), 20)
            [color=lightgreen]'                Debug.Print VBA.Right(DescM(rM, 1), 20)[/color]
            [color=blue]If[/color] VBA.InStr(1, DescP(rP, 1), (VBA.Mid(DescM(rM, 1), 2, 15)), 0) > 1 [color=blue]Or[/color] VBA.Right(DescP(rP, 1), 33) = VBA.Right(DescM(rM, 1), 33) [color=blue]Then[/color] [color=lightgreen]'[/color]
            'If VBA.Right(DescP(rP, 1), 20) = VBA.Right(DescM(rM, 1), 20) Then  'Check for match in lastparts in both lists
            [color=lightgreen]'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[/color]
            [color=blue]Let[/color] PricesM(rM, 1) = PricesP(rP, 1) [color=lightgreen]'Put price for matched description in Main Price Array[/color]
            [color=blue]Else[/color] [color=lightgreen]' No near match so do nothing[/color]
            [color=blue]End[/color] [color=blue]If[/color]
        [color=blue]Next[/color] rM
    [color=blue]Next[/color] rP
   
[color=lightgreen]'Output main prices[/color]
[color=blue]Let[/color] wsMain.Range("D" & sM & "").Resize(UBound(PricesM(), 1)) = PricesM() [color=lightgreen]'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[/color]
 
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'Code1_SpiderjamPricePullVBAArrayLooping()[/color]
[color=lightgreen]'[/color]
 
'
'
'
'
[color=lightgreen]'[/color]
 
[color=blue]Sub[/color] Code2_SpiderjamPricePull_MatchWithOnError()
On [color=blue]Error[/color] [color=blue]GoTo[/color] TheEnd [color=lightgreen]'Main error handler for unexpected errors - go to near the end and do anything important should an unexpected error occur[/color]
[color=blue]Dim[/color] wsPrices [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wsPrices = ThisWorkbook.Worksheets("Prices Spreadsheet") [color=lightgreen]'Give the abbreviations the variuos Methods Properties, etc. of ...[/color]
[color=blue]Dim[/color] wsMain [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wsMain = ThisWorkbook.Worksheets("W-ElectBulk") [color=lightgreen]'Worksheets Object (Obtainable through typing . dot )[/color]
wsPrices.Columns(10).ClearContents [color=lightgreen]'Clear the column that I later use for a temporary range in the .Match second argument.[/color]
 
[color=lightgreen]'Define start row numbers in sheets[/color]
[color=blue]Dim[/color] sP [color=blue]As[/color] [color=blue]Long[/color], sM [color=blue]As[/color] Long: [color=blue]Let[/color] sP = 4: [color=blue]Let[/color] sM = 15
 
[color=lightgreen]'Define Ranges for Data Arrays and "Capture" the data values..[/color]
[color=blue]Dim[/color] DescP() [color=blue]As[/color] [color=blue]Variant[/color], DescM() [color=blue]As[/color] [color=blue]Variant[/color] [color=lightgreen]' Descriptions Array Variables, - must be dynamic to work in next lines[/color]
[color=blue]Let[/color] DescP() = wsPrices.Range("A" & sP & ":A" & wsPrices.Cells(Rows.Count, 1).End(xlUp).Row & "").Value [color=lightgreen]'[color=blue]On[/color]e line "capture" of values of spradsheet to dynamic array is conveniently allowed by VBA and the last row here is...[/color]
[color=blue]Let[/color] DescM() = wsMain.Range("A" & sM & ":A" & wsMain.Cells(Rows.Count, 1).End(xlUp).Row & "").Value [color=lightgreen]'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[/color]
 
[color=blue]Dim[/color] PricesP() [color=blue]As[/color] [color=blue]Variant[/color], PricesM() [color=blue]As[/color] [color=blue]Variant[/color] [color=lightgreen]'Prices , and Prices to pull data Arrays[/color]
[color=blue]Let[/color] PricesP() = wsPrices.Range("H" & sP & ":H" & wsPrices.Cells(Rows.Count, 1).End(xlUp).Row & "").Value [color=lightgreen]'Column H for prices, (last row that for column A)[/color]
[color=blue]ReDim[/color] PricesM(1 [color=blue]To[/color] [color=blue]UBound[/color](DescM(), 1), 1 [color=blue]To[/color] [color=blue]UBound[/color](DescM(), 2)) [color=lightgreen]'For now leave the output array empty, but give it a consistant size for later looping[/color]
 
[color=lightgreen]'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[/color]
[color=blue]Dim[/color] rP [color=blue]As[/color] [color=blue]Long[/color], rM [color=blue]As[/color] [color=blue]Long[/color] [color=lightgreen]'variables for rows within the arrays[/color]
[color=blue]Dim[/color] RechtsMain() [color=blue]As[/color] [color=blue]String[/color], RechtsPrices() [color=blue]As[/color] [color=blue]String[/color] [color=lightgreen]' We are going to make these fixed dimension arrays so an assign string type[/color]
[color=blue]ReDim[/color] RechtsMain(1 [color=blue]To[/color] [color=blue]UBound[/color](DescM(), 1), 1 [color=blue]To[/color] [color=blue]UBound[/color](DescM(), 2)) [color=lightgreen]'Use ReDim just as Dim only takes numbers does not take Variables[/color]
    [color=blue]For[/color] rM = 1 [color=blue]To[/color] [color=blue]UBound[/color](DescM(), 1) [color=blue]Step[/color] 1
    RechtsMain(rM, 1) = VBA.Right(DescM(rM, 1), 20)
    [color=blue]Next[/color] rM
 
[color=blue]ReDim[/color] RechtsPrices(1 [color=blue]To[/color] [color=blue]UBound[/color](DescP(), 1), 1 [color=blue]To[/color] [color=blue]UBound[/color](DescP(), 2))
    [color=blue]For[/color] rP = 1 [color=blue]To[/color] [color=blue]UBound[/color](DescP(), 1) [color=blue]Step[/color] 1
    RechtsPrices(rP, 1) = VBA.Right(DescP(rP, 1), 20)
    [color=blue]Next[/color] rP
[color=blue]Let[/color] wsPrices.Range("J1").Resize(UBound(DescP(), 1), 1).Value = RechtsPrices() [color=lightgreen]'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"[/color]
 
[color=lightgreen]'Main ONE LOOP to check for similar Descriptions[/color]
 
    [color=lightgreen]'For rP = 1 To UBound(DescP(), 1) 'go down each price row, and for each of these rows...'NOT NEEDED IN Code2[/color]
        [color=blue]For[/color] rM = 1 [color=blue]To[/color] [color=blue]UBound[/color](DescM(), 1) [color=lightgreen]'go down the entire Main desription[/color]
            [color=lightgreen]'This is the bit where some tricky comparisons must be made[/color]
            [color=lightgreen]'                Debug.Print "DescP=" & DescP(rP, 1) & " DescM=" & DescM(rM, 1)[/color]
            [color=lightgreen]'                Debug.Print VBA.Mid(DescM(rM, 1), 2, 15)[/color]
            [color=lightgreen]'                Debug.Print VBA.Right(DescP(rP, 1), 33)[/color]
            [color=lightgreen]'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[/color]
            [color=lightgreen]'If VBA.Right(DescP(rP, 1), 20) = VBA.Right(DescM(rM, 1), 20) Then  'Check for match in lastparts in both lists[/color]
            [color=lightgreen]'If RechtsPrices(rP, 1) = RechtsMain(rM, 1) Then 'Check for match in lastparts in both lists[/color]
            [color=blue]On[/color] [color=blue]Error[/color] [color=blue]GoTo[/color] nextbit [color=lightgreen]' 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####[/color]
            [color=lightgreen]'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[/color]
            [color=blue]Let[/color] rP = Application.WorksheetFunction.Match(RechtsMain(rM, 1), wsPrices.Range("J1:J" & wsPrices.Cells(Rows.Count, 10).End(xlUp).Row & ""), 0) [color=lightgreen]'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[/color]
            [color=blue]Let[/color] PricesM(rM, 1) = PricesP(rP, 1) [color=lightgreen]'Put price for matched description in Main Price Array[/color]
 
nextbit:    On Error [color=blue]GoTo[/color] -1 [color=lightgreen]'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.[/color]
        [color=blue]Next[/color] rM [color=lightgreen]' Go to next main row[/color]
        [color=lightgreen]'On Error GoTo -1' Not needed in this case as it would have been done in any event a couple of lines above[/color]
        [color=lightgreen]'[color=blue]On[/color] [color=blue]Error[/color] GoTo 0 'disables (kills) last goto error handler, actually also not needed - can re assign an error handler, if it is not switched on[/color]
        On Error [color=blue]GoTo[/color] TheEnd [color=lightgreen]'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)[/color]
    [color=lightgreen]'Next rP' NOT NEEDED IN Code2[/color]
 
[color=lightgreen]'Output main prices[/color]
[color=blue]Let[/color] wsMain.Range("D" & sM & "").Resize(UBound(PricesM(), 1)) = PricesM() [color=lightgreen]'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[/color]
 
TheEnd:
[color=lightgreen]'Do anything here you should in the case of an error, like turning any important things back on[/color]
wsPrices.Columns(10).ClearContents [color=lightgreen]'Clear contents of column used for tempory range for use in .Match second argument.[/color]
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'Code2_SpiderjamPricePull_MatchWithOnError()[/color]
[color=lightgreen]'[/color]
 

spyderjam

New Member
Joined
Apr 30, 2015
Messages
3
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.
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
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
 

Forum statistics

Threads
1,078,252
Messages
5,339,097
Members
399,277
Latest member
Jyoti C

Some videos you may like

This Week's Hot Topics

Top