Need VBS script to copy data and verify

[tjbailey10]

My company recently switched vendors on thousands of parts and during the switch the database has not been able to copy the part numbers from one table to another.

Now in Excel I have two spread sheets. One with the original vendor the other with the new vendor. The part number I need to transfer is the supplier part number so for instance.

SHEET 1
OUR PART NUMBER SUPPLIER NUMBER
YD0000012 132FT-D1231*SMC

SHEET 2
OUR PART NUMBER SUPPLIER NUMBER
YD0000012 (blank)

So basically I need a script that will look at (OUR PART NUMBER) match it to (OUR PART NUMBER) in the 2nd sheet, check to see if (SUPPLIER NUMBER) has an entry in it and if not copy the (SUPPLIER NUMBER) from the first sheet and place it in the (SUPPLIER NUMBER) in the 2nd sheet.

Now I wanted to know if the program could also verify the data, b/c if the supplier number is wrong and the wrong part is ordered it could cause a plant shutdown.

Please let me know a solution if any of you brilliant individuals know of one. Copy and pasting 40K part numbers perfectly doesn't sound possible, I'm really praying ya'll have a script that can do this.

 

[markmzz]

TjBailey10,

If I understand correctly what you want, then the walkthrough below solve the first part of your problem.

Important: make a copy of your workbook first.

Try this (note my examples below):

Sheet1

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="FONT-WEIGHT: bold">OUR PART NUMBER</TD><TD style="FONT-WEIGHT: bold">SUPPLIER NUMBER</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>YD0000012</TD><TD>132FT-D1231*SMC1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>YD0000013</TD><TD>132FT-D1231*SMC2</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>YD0000014</TD><TD>132FT-D1231*SMC3</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>YD0000015</TD><TD>132FT-D1231*SMC4</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>YD0000016</TD><TD>132FT-D1231*SMC5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>YD0000017</TD><TD>132FT-D1231*SMC6</TD></TR></TBODY></TABLE>

Sheet2

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="FONT-WEIGHT: bold">OUR PART NUMBER</TD><TD style="FONT-WEIGHT: bold">SUPPLIER NUMBER</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>YD0000012</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>YD0000013</TD><TD>132FT-D1231*SMC8</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>YD0000014</TD><TD>132FT-D1231*SMC9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>YD0000015</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>YD0000016</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>YD0000017</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>


Before started, sort the two list by the field OUR PART NUMBER (Sheet1 and Sheet2) if necessary:

1. On Sheet2, copy the label from B1 to cell D1.

2. (Excel 2007) Still with Sheet2 selected, make active the Data tab and choose the Advanced command in the Sort & Filter group.

2. (Excel 2003) Still with Sheet2 selected, open the Data menu, point to Filter, and choose the Advanced Filter command.

3. In the Advanced Filter dialog box, choose the option Copy to another location and make the following changes in the text boxes of the respective dialog box:

List range: select the range A1:B7 on Sheet1.

Criteria range: select the range A1:A7 on Sheet2.

Copy to: Select cell D1 in Sheet2.

4. Now, click OK.

5. Select the region B1:B7 in Sheet2 and choose the Copy command.

6. Select the range D1:D7 in Sheet2 and choose the Paste Special command, enable the option Ignore blank and click OK.

7. Now, copy the range D1:D7 to B1:B7.

8. Finally, delete the column D.

As for your second request (supplier number wrong) I need more information (give us examples).

Markmzz

 

[tjbailey10]

How did you post those Excel examples into your message? I think I need to clarify what I'm looking for and the situation I'm facing.

 

[markmzz]

How did you post those Excel examples into your message? I think I need to clarify what I'm looking for and the situation I'm facing.

Tjbailey10,

I'm sorry. I was just trying to help you.

Then, you could put more examples?

Markmzz

 

[tjbailey10]

No, no you mis-understood me, I'm sorry.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
You're solution and help is excellent. I'm just doing a horrible job of getting me message across. I was saying the examples you have above with the spreadsheets are what I want to post in my reply but I don't know how to do it. Is there a special add-on I need to download in order to do that?<o></o>

My problem is a little complex because while Sheet 1 has all the information I need to put in sheet 2 the column "Our Part Numbers" doesn't line up with both sheets.<o></o>

If you can point me to somewhere that can show me how to post a spreadsheet into my replies I can give you a better idea about what I'm talking about.<o></o>

COMPLETLY my fault for not explaining clearly.<o></o>

 

[markmzz]

Tjbailey10,

It's ok, I think it was a failed of translation of my part.

You can find more information on how to post on the link below.

http://www.mrexcel.com/forum/showthread.php?t=89356

Markmzz

 

[Norie]

A couple of things:

1 Must you use Excel? This could possibly be done with a simple SQL UPDATE query if the data was in a database.

2 How would the verification be done? Is there some other source that could be used to check against?

 

[tjbailey10]

Thank you both for your help. Unfortunatly I just pulled the rest of the records and this is going to be WAY to long of a process to do by hand. The problem occured in the actual table transfer originally with the MySQL ERP program. We've now decided to just try this from a MySQL standpoint again. The last time didn't have very good results, but maybe 2nd time will be the charm.

Thanks again ya'll.

 

[Norie]

Do you mean you'll try and pull the data again?

 

[markmzz]

Thank you both for your help. Unfortunatly I just pulled the rest of the records and this is going to be WAY to long of a process to do by hand. The problem occured in the actual table transfer originally with the MySQL ERP program. We've now decided to just try this from a MySQL standpoint again. The last time didn't have very good results, but maybe 2nd time will be the charm.

Thanks again ya'll.

Tjbailey10,

Ok. But if you decide to try Excel again, you are welcome.

Markmzz