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

[JackHoff]

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.

 

[hiker95]

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]

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]

JackHoff,

Thanks for the workbook.

Excel 2007
	A	B	C	D	E	F	G	H	I
1	NAME BOX	IMPORT SER #	SUB SERIAL #	SERIAL #	CELL REF.	VLOOKUP	COL.CATAGORY	LOOKUP	TABLE
2	D9431 a00771129431dr	a00771129431dr	9431dr	9431	D9431	D	dr	bo	A
3								br	B
4								ch	C
5								dr	D
6								en	E
7								el	L
8								ac	H
9								mi	I
10								pt	J
11								tr	K
Import

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?

Excel 2007
	D
9431	9431
Asian dB

 

[JackHoff]

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]

JackHoff,

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

Excel 2007
	A	B	C	D	E	H	I	J	K	L
1	a00026231bo	1	1	1	1	1	1	1	1	1
2	a00026232bo	2	2	2	2	a00012632ac	2	2	2	2
750		a0086354750br		750	750	750		750	a0053768750tr	750
1880				1880	a00175501880en				1880	a00418191880el
2451				2451	a00329112451en					2451
3148				3148	3148					3148
3170				3170	3170					a00082543170el
9431				9431
10140				10140
10141				10141
10142				10142
Asian dB

Sample raw data in worksheet Import before the macro:

Excel 2007
	A	B	C	D	E	F	G	H	I
1	NAME BOX	IMPORT SER #	SUB SERIAL #	SERIAL #	CELL REF.	VLOOKUP	COL.CATAGORY	LOOKUP	TABLE
2	a00771129431dr	a00771129431dr						bo	A
3		a007329710140dr						br	B
4		a00477672451el						ch	C
5		a00669733170en						dr	D
6		a00447513148en						en	E
7		a0087367750pt						el	L
8		a01021771880tr						ac	H
9								mi	I
10								pt	J
11								tr	K
12
Import

Sample raw data in worksheet Import after the macro:

Excel 2007
	A	B	C	D	E	F	G	H	I
1	NAME BOX	IMPORT SER #	SUB SERIAL #	SERIAL #	CELL REF.	VLOOKUP	COL.CATAGORY	LOOKUP	TABLE
2	D9431 a00771129431dr	a00771129431dr	9431dr	9431	D9431	D	dr	bo	A
3		a007329710140dr	10140dr	10140	D10140	D	dr	br	B
4		a00477672451el	2451el	2451	L2451	L	el	ch	C
5		a00669733170en	3170en	3170	E3170	E	en	dr	D
6		a00447513148en	3148en	3148	E3148	E	en	en	E
7		a0087367750pt	750pt	750	J750	J	pt	el	L
8		a01021771880tr	1880tr	1880	K1880	K	tr	ac	H
9								mi	I
10								pt	J
11								tr	K
12
Import

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):

Excel 2007
	A	B	C	D	E	H	I	J	K	L
1	a00026231bo	1	1	1	1	1	1	1	1	1
2	a00026232bo	2	2	2	2	a00012632ac	2	2	2	2
750		a0086354750br		750	750	750		a0087367750pt	a0053768750tr	750
1880				1880	a00175501880en				a01021771880tr	a00418191880el
2451				2451	a00329112451en					a00477672451el
3148				3148	a00447513148en					3148
3170				3170	a00669733170en					a00082543170el
9431				a00771129431dr
10140				a007329710140dr
10141				10141
10142				10142
Asian dB

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.

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]

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]

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]

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]

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

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.