Match Serial Number to matching Cell Number based on SN last 4# & last 2 letters

JackHoff

New Member
Joined
Jan 7, 2014
Messages
44
I just spent an hour making a post and its gone, I do not see how to retreive AUTO SAVED post, I will search for that info later. SO, here is the very short version.

I have a list of serial numbers (SN) on SHEET 2, Col B, maybe a few Rows to Hundreds of Rows for todays work. The format is always the same but the length is shorter or longer but the important part is the first nine digits can always be cut out to get what I need. EX: m1234567845ex, I need 45ex into another Col, then separate the numbers from the letters into two other Col. The SN may be a single number to 7 numbers but using the MID(text,9,9) gets what I need.

I use a VLOOKUP Table (2 Col) to change 10 different two letter categories into a single letter Col designation. In this case 'ex' will transfer into Col 'L'. I also need the SN 45 to go into SHEET 1 Cell L45, actually the entire SN from SHEET 2, m1234567845ex into, SHEET 1 Cell L45.

The SN's are randomized for the leading 9 digits and not important, the ending numbers and letters are required info, the 10 sets of letters will dictate which column in sheet 1 will be used and the number digits will dictate which row the entire SN will be pasted into.

Let me post this incase I have internet issues, thanks to all for any assistance on this process. Post copied to clipboard.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
JackHoff,

What version of Excel and Windows are you using?

Can you post screenshots of the worksheets?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 

JackHoff

New Member
Joined
Jan 7, 2014
Messages
44
https://app.box.com/s/j8bnuahsp09m2o22w3s7

Thank you for your interest in my request for help, the PC with the spreadsheet is XP and Excel 2003. Please view the notes to the right of the spreadsheet in Box Net. If anymore info is needed please let me know. It is marked as SHARE. SHEET 1, Pg 10 is fairly populated for practice and the last page 479 has SHEET 2 where I get my final product to use with the third party Macro Recorder in Col A to transfer the data via Copy Paste outside of Excel but is time consuming to go through each Cell Ref and SN one at a time. Colors were not intended to come into this picture but now I see that I need the final paste of each Cell to SHEET 1 to be made Black Text please.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
JackHoff,

Thanks for the workbook.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</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=";">NAME BOX</td><td style="font-weight: bold;;">IMPORT SER #</td><td style=";">SUB SERIAL #</td><td style=";">SERIAL #</td><td style=";">CELL REF.</td><td style=";">VLOOKUP</td><td style="border-right: 1px solid black;;">COL.CATAGORY</td><td style="border-top: 1px solid black;border-left: 1px solid black;;">LOOKUP</td><td style="border-top: 1px solid black;border-right: 1px solid black;;">TABLE</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">D9431 a00771129431dr</td><td style="background-color: #FFFF00;;">a00771129431dr</td><td style=";">9431dr</td><td style="text-align: right;;">9431</td><td style="background-color: #FFFF00;;">D9431</td><td style=";">D</td><td style="border-right: 1px solid black;;">dr</td><td style="border-left: 1px solid black;;">bo</td><td style="border-right: 1px solid black;;">A</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">br</td><td style="border-right: 1px solid black;;">B</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">ch</td><td style="border-right: 1px solid black;;">C</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">dr</td><td style="border-right: 1px solid black;;">D</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">en</td><td style="border-right: 1px solid black;;">E</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">el</td><td style="border-right: 1px solid black;;">L</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">ac</td><td style="border-right: 1px solid black;;">H</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;color: #FF0000;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">mi</td><td style="border-right: 1px solid black;;">I</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;color: #FF0000;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">pt</td><td style="border-right: 1px solid black;;">J</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-bottom: 1px solid black;border-left: 1px solid black;;">tr</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;;">K</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Import</p><br /><br />

In reference to the above worksheet:

Column A information is not needed by me for the macro.

I do need the Lookup Table in range H1:I11.

1. The macro does not need to do the calculations in range C2:G2. Do you need all the information in range C2:G2 for any reason?
2. You just need a00771129431dr to be copied to the following cell in worksheet Asian dB?


<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">9431</td><td style="text-align: right;background-color: #FFFF00;;">9431</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Asian dB</p><br /><br />
 
Last edited:

JackHoff

New Member
Joined
Jan 7, 2014
Messages
44

ADVERTISEMENT

That is correct, I only need Col B of SHEET 2 copied to the correct Cell of Asian dB. If you can make it that easy using the VLOOKUP, I will be happy, looking forward to your solution, have been searching still and find nothing to match up a number to its same number cell. Please get the entire long SN into cell D9431. I do not need C2:G2 if you dont. That is just my skill level for now. If the macro will be different for 2003 and 2007 please let me know, I will get this old PC updated someday soon. Much appreciated.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
JackHoff,

Worksheet Asian dB before the macro - the YELLOW cells are the cells to be changed (not all rows are shown for brevity):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">a00026231bo</td><td style="text-align: right;color: #FF0000;;">1</td><td style="text-align: right;color: #FF0000;;">1</td><td style="text-align: right;color: #FF0000;;">1</td><td style="text-align: right;color: #FF0000;;">1</td><td style="text-align: right;color: #FF0000;;">1</td><td style="text-align: right;color: #FF0000;;">1</td><td style="text-align: right;color: #FF0000;;">1</td><td style="text-align: right;color: #FF0000;;">1</td><td style="text-align: right;color: #FF0000;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">a00026232bo</td><td style="text-align: right;color: #FF0000;;">2</td><td style="text-align: right;color: #FF0000;;">2</td><td style="text-align: right;color: #FF0000;;">2</td><td style="text-align: right;color: #FF0000;;">2</td><td style=";">a00012632ac</td><td style="text-align: right;color: #FF0000;;">2</td><td style="text-align: right;color: #FF0000;;">2</td><td style="text-align: right;color: #FF0000;;">2</td><td style="text-align: right;color: #FF0000;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">750</td><td style="text-align: right;;"></td><td style=";">a0086354750br</td><td style="text-align: right;;"></td><td style="text-align: right;color: #FF0000;;">750</td><td style="text-align: right;color: #FF0000;;">750</td><td style="text-align: right;;">750</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;background-color: #FFFF00;;">750</td><td style=";">a0053768750tr</td><td style="text-align: right;;">750</td></tr><tr ><td style="color: #161120;text-align: center;">1880</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #FF0000;;">1880</td><td style=";">a00175501880en</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;background-color: #FFFF00;;">1880</td><td style=";">a00418191880el</td></tr><tr ><td style="color: #161120;text-align: center;">2451</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #FF0000;;">2451</td><td style=";">a00329112451en</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;background-color: #FFFF00;;">2451</td></tr><tr ><td style="color: #161120;text-align: center;">3148</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #FF0000;;">3148</td><td style="font-weight: bold;text-align: right;background-color: #FFFF00;;">3148</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3148</td></tr><tr ><td style="color: #161120;text-align: center;">3170</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #FF0000;;">3170</td><td style="font-weight: bold;text-align: right;color: #FF0000;background-color: #FFFF00;;">3170</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">a00082543170el</td></tr><tr ><td style="color: #161120;text-align: center;">9431</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #FF0000;;"></td><td style="font-weight: bold;text-align: right;background-color: #FFFF00;;">9431</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10140</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #FF0000;;"></td><td style="font-weight: bold;text-align: right;background-color: #FFFF00;;">10140</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10141</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #FF0000;;"></td><td style="text-align: right;;">10141</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10142</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #FF0000;;"></td><td style="text-align: right;;">10142</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Asian dB</p><br /><br />

Sample raw data in worksheet Import before the macro:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</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=";">NAME BOX</td><td style="font-weight: bold;;">IMPORT SER #</td><td style=";">SUB SERIAL #</td><td style=";">SERIAL #</td><td style=";">CELL REF.</td><td style=";">VLOOKUP</td><td style="border-right: 1px solid black;;">COL.CATAGORY</td><td style="border-top: 1px solid black;border-left: 1px solid black;;">LOOKUP</td><td style="border-top: 1px solid black;border-right: 1px solid black;;">TABLE</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";"> a00771129431dr</td><td style=";">a00771129431dr</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">bo</td><td style="border-right: 1px solid black;;">A</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style=";">a007329710140dr</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">br</td><td style="border-right: 1px solid black;;">B</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style=";">a00477672451el</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">ch</td><td style="border-right: 1px solid black;;">C</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style=";">a00669733170en</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">dr</td><td style="border-right: 1px solid black;;">D</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style=";">a00447513148en</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">en</td><td style="border-right: 1px solid black;;">E</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style=";">a0087367750pt</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">el</td><td style="border-right: 1px solid black;;">L</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style=";">a01021771880tr</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">ac</td><td style="border-right: 1px solid black;;">H</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;color: #FF0000;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">mi</td><td style="border-right: 1px solid black;;">I</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;color: #FF0000;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">pt</td><td style="border-right: 1px solid black;;">J</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-bottom: 1px solid black;border-left: 1px solid black;;">tr</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;;">K</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Import</p><br /><br />

Sample raw data in worksheet Import after the macro:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</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=";">NAME BOX</td><td style="font-weight: bold;;">IMPORT SER #</td><td style=";">SUB SERIAL #</td><td style=";">SERIAL #</td><td style=";">CELL REF.</td><td style=";">VLOOKUP</td><td style="border-right: 1px solid black;;">COL.CATAGORY</td><td style="border-top: 1px solid black;border-left: 1px solid black;;">LOOKUP</td><td style="border-top: 1px solid black;border-right: 1px solid black;;">TABLE</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">D9431 a00771129431dr</td><td style=";">a00771129431dr</td><td style=";">9431dr</td><td style="text-align: right;;">9431</td><td style=";">D9431</td><td style=";">D</td><td style="border-right: 1px solid black;;">dr</td><td style="border-left: 1px solid black;;">bo</td><td style="border-right: 1px solid black;;">A</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style=";">a007329710140dr</td><td style=";">10140dr</td><td style="text-align: right;;">10140</td><td style=";">D10140</td><td style=";">D</td><td style="border-right: 1px solid black;;">dr</td><td style="border-left: 1px solid black;;">br</td><td style="border-right: 1px solid black;;">B</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style=";">a00477672451el</td><td style=";">2451el</td><td style="text-align: right;;">2451</td><td style=";">L2451</td><td style=";">L</td><td style="border-right: 1px solid black;;">el</td><td style="border-left: 1px solid black;;">ch</td><td style="border-right: 1px solid black;;">C</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style=";">a00669733170en</td><td style=";">3170en</td><td style="text-align: right;;">3170</td><td style=";">E3170</td><td style=";">E</td><td style="border-right: 1px solid black;;">en</td><td style="border-left: 1px solid black;;">dr</td><td style="border-right: 1px solid black;;">D</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style=";">a00447513148en</td><td style=";">3148en</td><td style="text-align: right;;">3148</td><td style=";">E3148</td><td style=";">E</td><td style="border-right: 1px solid black;;">en</td><td style="border-left: 1px solid black;;">en</td><td style="border-right: 1px solid black;;">E</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style=";">a0087367750pt</td><td style=";">750pt</td><td style="text-align: right;;">750</td><td style=";">J750</td><td style=";">J</td><td style="border-right: 1px solid black;;">pt</td><td style="border-left: 1px solid black;;">el</td><td style="border-right: 1px solid black;;">L</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style=";">a01021771880tr</td><td style=";">1880tr</td><td style="text-align: right;;">1880</td><td style=";">K1880</td><td style=";">K</td><td style="border-right: 1px solid black;;">tr</td><td style="border-left: 1px solid black;;">ac</td><td style="border-right: 1px solid black;;">H</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;color: #FF0000;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">mi</td><td style="border-right: 1px solid black;;">I</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;color: #FF0000;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">pt</td><td style="border-right: 1px solid black;;">J</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-bottom: 1px solid black;border-left: 1px solid black;;">tr</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;;">K</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Import</p><br /><br />

Worksheet Asian dB after the macro - the YELLOW cells are the cells that have been changed, and, the bold black text is to help see the results easier (not all rows are shown for brevity):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">a00026231bo</td><td style="text-align: right;color: #FF0000;;">1</td><td style="text-align: right;color: #FF0000;;">1</td><td style="text-align: right;color: #FF0000;;">1</td><td style="text-align: right;color: #FF0000;;">1</td><td style="text-align: right;color: #FF0000;;">1</td><td style="text-align: right;color: #FF0000;;">1</td><td style="text-align: right;color: #FF0000;;">1</td><td style="text-align: right;color: #FF0000;;">1</td><td style="text-align: right;color: #FF0000;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">a00026232bo</td><td style="text-align: right;color: #FF0000;;">2</td><td style="text-align: right;color: #FF0000;;">2</td><td style="text-align: right;color: #FF0000;;">2</td><td style="text-align: right;color: #FF0000;;">2</td><td style=";">a00012632ac</td><td style="text-align: right;color: #FF0000;;">2</td><td style="text-align: right;color: #FF0000;;">2</td><td style="text-align: right;color: #FF0000;;">2</td><td style="text-align: right;color: #FF0000;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">750</td><td style="text-align: right;;"></td><td style=";">a0086354750br</td><td style="text-align: right;;"></td><td style="text-align: right;color: #FF0000;;">750</td><td style="text-align: right;color: #FF0000;;">750</td><td style="text-align: right;;">750</td><td style="text-align: right;;"></td><td style="font-weight: bold;background-color: #FFFF00;;">a0087367750pt</td><td style=";">a0053768750tr</td><td style="text-align: right;;">750</td></tr><tr ><td style="color: #161120;text-align: center;">1880</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #FF0000;;">1880</td><td style=";">a00175501880en</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;background-color: #FFFF00;;">a01021771880tr</td><td style=";">a00418191880el</td></tr><tr ><td style="color: #161120;text-align: center;">2451</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #FF0000;;">2451</td><td style=";">a00329112451en</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;background-color: #FFFF00;;">a00477672451el</td></tr><tr ><td style="color: #161120;text-align: center;">3148</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #FF0000;;">3148</td><td style="font-weight: bold;background-color: #FFFF00;;">a00447513148en</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3148</td></tr><tr ><td style="color: #161120;text-align: center;">3170</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #FF0000;;">3170</td><td style="font-weight: bold;background-color: #FFFF00;;">a00669733170en</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">a00082543170el</td></tr><tr ><td style="color: #161120;text-align: center;">9431</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #FF0000;;"></td><td style="font-weight: bold;background-color: #FFFF00;;">a00771129431dr</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10140</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #FF0000;;"></td><td style="font-weight: bold;background-color: #FFFF00;;">a007329710140dr</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10141</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #FF0000;;"></td><td style="text-align: right;;">10141</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10142</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #FF0000;;"></td><td style="text-align: right;;">10142</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Asian dB</p><br /><br />

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Option Explicit
Sub CopyImportSerialNbrs()
' hiker95, 01/08/2013, ME748764
Dim lrb As Long, lrc As Long, c As Range
With Sheets("Import")
  lrb = .Cells(Rows.Count, 2).End(xlUp).Row
  If lrb = 1 Then
    MsgBox "There are no 'IMPORT SER#'s in column B - macro terminated!"
    Exit Sub
  End If
  Application.ScreenUpdating = False
  lrc = .Cells(Rows.Count, 3).End(xlUp).Row
  If lrc > 1 Then
    .Range("C2:G" & lrc).ClearContents
  End If
  lrb = .Cells(Rows.Count, 2).End(xlUp).Row
  .Range("C2:C" & lrb).FormulaR1C1 = "=MID(RC[-1],9,9)"
  .Range("D2:D" & lrb).FormulaR1C1 = "=MID(RC[-1],1,LEN(RC[-1])-2)"
  .Range("G2:G" & lrb).FormulaR1C1 = "=RIGHT(RC[-5],2)"
  .Range("F2:F" & lrb).FormulaR1C1 = "=VLOOKUP(RC[1],R1C8:R11C9,2,0)"
  .Range("E2:E" & lrb).FormulaR1C1 = "=RC[1]&RC[-1]"
  With .Range("C2:G" & lrb)
    .Value = .Value
  End With
  For Each c In Range("E2", .Range("E" & Rows.Count).End(xlUp))
    If c <> "" Then
      Sheets("Asian dB").Range(c).Value = c.Offset(, -3).Value
    End If
  Next c
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CopyImportSerialNbrs macro.
 

JackHoff

New Member
Joined
Jan 7, 2014
Messages
44

ADVERTISEMENT

hiker95, Thank you up to now, its almost 1AM here in Tokyo so I will try this out tomorrow AM and get back with you during my day sometime. I will study what you have done and try to learn something, much appreciated from an old dog trying to learn a new trick or two. With a quick scan of your code, I dont notice you turning the text black, I have the cells formatted to red if the full SN is not yet populated, sorry, just too tired now to set it up and try.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
JackHoff,

much appreciated from an old dog trying to learn a new trick or two

You are very welcome.

That word old has been remove from my brain. You are only as old as you think you are.

I began programming/being interested in computers when the Radio Shack TRS80 was rolled out in the early 80's I think. By that time I had four beautiful children.

I dont notice you turning the text black

That was in the screenshot of after the macro in worksheet Asian dB. It was only added so that you could see the results easier.

Look forward to hearing from you tomorrow.
 

JackHoff

New Member
Joined
Jan 7, 2014
Messages
44
I kick myself in the butt for not getting into PC with the TRS80, I was 16 and had a job but was not into computers but my coworker was a true geek, big time into Star Wars too. I will be going to University in the Philippines next term to finally learn something officially but the schools there are minor leagues compared to USA, I am a Vet of 3 wars so I will use my GI BIll there. The font carried over in Black I will guess that it copied the formatting and pasted it, that is what I need so if no extra code is needed, fine. I played with it a little and got it to clear.contents on Row B also near the end of the solution. May I ask for two more minor add ons please, in cells H and I 13, merged by me, could I please get the date that the Macro was run and saved there. H and I 15, merged by me, could I please get the last SN at the bottom of Col B to be saved in these merged cells and saved there so I know where I left off last time which could be days or weeks later.

I will make a button to push to run the Macro from Import Page, I noticed that if I tried to run the macro from Asian dB page, I got an error which I understand but others may not, could I please get a Msgbox that says to run the macro from the Import Tab please.


.Range("C2:G" & lrc).ClearContents. Trying to figure out the code, I was thinking that C2:G was the same as lrc, long range c but when looking at .Range("C2:G" & lrb) I was thinking that you included the & lrc for coding format personal preferences?

I will go to the Philippines tomorrow AM and have a heavy schedule but I will make time to look this over again, thanks so much and thank God for the TSR80, I have a Casio handheld calculator that does a little Basic, I only learned a few things with that 20 years ago.
</pre>
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
JackHoff,

I will make a button to push to run the Macro from Import Page, I noticed that if I tried to run the macro from Asian dB page, I got an error which I understand but others may not, could I please get a Msgbox that says to run the macro from the Import Tab please.

I missed a single . character in the code.

And, when the macro is launched from a command button, or, in other way, it will first activate worksheet Import.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code:
Option Explicit
Sub CopyImportSerialNbrsV2()
' hiker95, 01/09/2013, ME748764
Dim lrb As Long, lrc As Long, c As Range
With Sheets("Import")
  .Activate
  lrb = .Cells(Rows.Count, 2).End(xlUp).Row
  If lrb = 1 Then
    MsgBox "There are no 'IMPORT SER#'s in column B - macro terminated!"
    Exit Sub
  End If
  Application.ScreenUpdating = False
  lrc = .Cells(Rows.Count, 3).End(xlUp).Row
  If lrc > 1 Then
    .Range("C2:G" & lrc).ClearContents
  End If
  lrb = .Cells(Rows.Count, 2).End(xlUp).Row
  .Range("C2:C" & lrb).FormulaR1C1 = "=MID(RC[-1],9,9)"
  .Range("D2:D" & lrb).FormulaR1C1 = "=MID(RC[-1],1,LEN(RC[-1])-2)"
  .Range("G2:G" & lrb).FormulaR1C1 = "=RIGHT(RC[-5],2)"
  .Range("F2:F" & lrb).FormulaR1C1 = "=VLOOKUP(RC[1],R1C8:R11C9,2,0)"
  .Range("E2:E" & lrb).FormulaR1C1 = "=RC[1]&RC[-1]"
  With .Range("C2:G" & lrb)
    .Value = .Value
  End With
  For Each c In .Range("E2", .Range("E" & Rows.Count).End(xlUp))
    If c <> "" Then
      Sheets("Asian dB").Range(c).Value = c.Offset(, -3).Value
    End If
  Next c
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CopyImportSerialNbrsV2 macro.



May I ask for two more minor add ons please, in cells H and I 13, merged by me, could I please get the date that the Macro was run and saved there. H and I 15, merged by me, could I please get the last SN at the bottom of Col B to be saved in these merged cells and saved there so I know where I left off last time which could be days or weeks later.

Could I have another workbook to see what this should look like?



and thank God for the TSR80

Yes.

I started with Basic, then Cobol, and, finally Assembler.

Then I ended up attending Gruman's Programming School, on Long Island, New York, and, maxed their classes. There was no job offer because I did not have any real world experience. But, it did lead to better and more challenging jobs.
 
Last edited:

Forum statistics

Threads
1,136,354
Messages
5,675,296
Members
419,559
Latest member
BraytonM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top