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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

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.


Excel 2007
ABCDEFGHI
1NAME BOXIMPORT SER #SUB SERIAL #SERIAL #CELL REF.VLOOKUPCOL.CATAGORYLOOKUPTABLE
2D9431 a00771129431dra00771129431dr9431dr9431D9431DdrboA
3brB
4chC
5drD
6enE
7elL
8acH
9miI
10ptJ
11trK
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
94319431
Asian dB
 
Last edited:

JackHoff

New Member
Joined
Jan 7, 2014
Messages
44
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):


Excel 2007
ABCDEHIJKL
1a00026231bo111111111
2a00026232bo2222a00012632ac2222
750a0086354750br750750750750a0053768750tr750
18801880a00175501880en1880a00418191880el
24512451a00329112451en2451
3148314831483148
317031703170a00082543170el
94319431
1014010140
1014110141
1014210142
Asian dB


Sample raw data in worksheet Import before the macro:


Excel 2007
ABCDEFGHI
1NAME BOXIMPORT SER #SUB SERIAL #SERIAL #CELL REF.VLOOKUPCOL.CATAGORYLOOKUPTABLE
2a00771129431dra00771129431drboA
3a007329710140drbrB
4a00477672451elchC
5a00669733170endrD
6a00447513148enenE
7a0087367750ptelL
8a01021771880tracH
9miI
10ptJ
11trK
12
Import


Sample raw data in worksheet Import after the macro:


Excel 2007
ABCDEFGHI
1NAME BOXIMPORT SER #SUB SERIAL #SERIAL #CELL REF.VLOOKUPCOL.CATAGORYLOOKUPTABLE
2D9431 a00771129431dra00771129431dr9431dr9431D9431DdrboA
3a007329710140dr10140dr10140D10140DdrbrB
4a00477672451el2451el2451L2451LelchC
5a00669733170en3170en3170E3170EendrD
6a00447513148en3148en3148E3148EenenE
7a0087367750pt750pt750J750JptelL
8a01021771880tr1880tr1880K1880KtracH
9miI
10ptJ
11trK
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
ABCDEHIJKL
1a00026231bo111111111
2a00026232bo2222a00012632ac2222
750a0086354750br750750750a0087367750pta0053768750tr750
18801880a00175501880ena01021771880tra00418191880el
24512451a00329112451ena00477672451el
31483148a00447513148en3148
31703170a00669733170ena00082543170el
9431a00771129431dr
10140a007329710140dr
1014110141
1014210142
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.

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
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,171,169
Messages
5,874,155
Members
433,029
Latest member
theodoros1

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