Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to re-number data
I have a spread sheet that could be a few hundred rows or 50000 plus row of data. In the first column is a 38 character (letters and numbers) data string. But in that string there is a six digit sequence number. The numbers always run consecutively. The first cell A1 could start a 000001 or 014235 or any six digit number including leading zeros. The sequence number starts at character 9 - 14. I'm trying to create a macro that will resequence the characters (9-14) staring from a sequence number I chose. For example" The first sequene number below is 010926 thru 010932 A 1 ABCDASAY01092600040004000000000000XXXX 2 ABCDASAY01092700040004000000000000XXXX 3 ABCDASAY01092800040004000000000000XXXX 4 ABCDASAY01092900040004000000000000XXXX 5 ABCDASAY01093000040004000000000000XXXX 6 ABCDASAY01093100040004000000000000XXXX 7 ABCDASAY01093200040004000000000000XXXX f I wanted to resequence column at starting at 004421 the macro would return this A 1 ABCDASAY00442100040004000000000000XXXX 2 ABCDASAY00442200040004000000000000XXXX 3 ABCDASAY00442300040004000000000000XXXX 4 ABCDASAY00442400040004000000000000XXXX 5 ABCDASAY00442500040004000000000000XXXX 6 ABCDASAY00442600040004000000000000XXXX 7 ABCDASAY00442700040004000000000000XXXX Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to re-number data
Maybe you could use a formula like:
="ABCDASAY"&text(row()+1,"000000")&"00040004000000 000000XXXX" (starting in row 1) or =left(a1,8)&text(row()+1,"000000")&mid(a1,15,255) in B1 Then drag the formula down as far as you need. And then convert the formulas to values (edit|Copy, edit|Paste special|values). Little Penny wrote: I have a spread sheet that could be a few hundred rows or 50000 plus row of data. In the first column is a 38 character (letters and numbers) data string. But in that string there is a six digit sequence number. The numbers always run consecutively. The first cell A1 could start a 000001 or 014235 or any six digit number including leading zeros. The sequence number starts at character 9 - 14. I'm trying to create a macro that will resequence the characters (9-14) staring from a sequence number I chose. For example" The first sequene number below is 010926 thru 010932 A 1 ABCDASAY01092600040004000000000000XXXX 2 ABCDASAY01092700040004000000000000XXXX 3 ABCDASAY01092800040004000000000000XXXX 4 ABCDASAY01092900040004000000000000XXXX 5 ABCDASAY01093000040004000000000000XXXX 6 ABCDASAY01093100040004000000000000XXXX 7 ABCDASAY01093200040004000000000000XXXX f I wanted to resequence column at starting at 004421 the macro would return this A 1 ABCDASAY00442100040004000000000000XXXX 2 ABCDASAY00442200040004000000000000XXXX 3 ABCDASAY00442300040004000000000000XXXX 4 ABCDASAY00442400040004000000000000XXXX 5 ABCDASAY00442500040004000000000000XXXX 6 ABCDASAY00442600040004000000000000XXXX 7 ABCDASAY00442700040004000000000000XXXX Thanks -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to re-number data
That did Joel.
Thank you Thank you Thank you. Sub resequence() Dim SequenceCount Dim RowCount Dim Prefix Dim Suffix Dim SequenceString SequenceCount = _ Val(InputBox("Enter Start Sequence Number")) RowCount = 1 Do While Cells(RowCount, "A") < "" Prefix = Left(Cells(RowCount, "A").Value, 8) Suffix = Mid(Cells(RowCount, "A").Value, 15) SequenceString = _ Format(SequenceCount, "0#####") Cells(RowCount, "B") = Prefix & _ SequenceString & Suffix SequenceCount = SequenceCount + 1 RowCount = RowCount + 1 Loop End Sub "Little Penny" wrote: I have a spread sheet that could be a few hundred rows or 50000 plus row of data. In the first column is a 38 character (letters and numbers) data string. But in that string there is a six digit sequence number. The numbers always run consecutively. The first cell A1 could start a 000001 or 014235 or any six digit number including leading zeros. The sequence number starts at character 9 - 14. I'm trying to create a macro that will resequence the characters (9-14) staring from a sequence number I chose. For example" The first sequene number below is 010926 thru 010932 A 1 ABCDASAY01092600040004000000000000XXXX 2 ABCDASAY01092700040004000000000000XXXX 3 ABCDASAY01092800040004000000000000XXXX 4 ABCDASAY01092900040004000000000000XXXX 5 ABCDASAY01093000040004000000000000XXXX 6 ABCDASAY01093100040004000000000000XXXX 7 ABCDASAY01093200040004000000000000XXXX f I wanted to resequence column at starting at 004421 the macro would return this A 1 ABCDASAY00442100040004000000000000XXXX 2 ABCDASAY00442200040004000000000000XXXX 3 ABCDASAY00442300040004000000000000XXXX 4 ABCDASAY00442400040004000000000000XXXX 5 ABCDASAY00442500040004000000000000XXXX 6 ABCDASAY00442600040004000000000000XXXX 7 ABCDASAY00442700040004000000000000XXXX Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to re-number data
Thanks again Joel That did it................ One day I hope i'm as good as you with this stuff. But i'm learning Thanks On Fri, 7 Sep 2007 19:18:02 -0700, Joel wrote: That did Joel. Thank you Thank you Thank you. Sub resequence() Dim SequenceCount Dim RowCount Dim Prefix Dim Suffix Dim SequenceString SequenceCount = _ Val(InputBox("Enter Start Sequence Number")) RowCount = 1 Do While Cells(RowCount, "A") < "" Prefix = Left(Cells(RowCount, "A").Value, 8) Suffix = Mid(Cells(RowCount, "A").Value, 15) SequenceString = _ Format(SequenceCount, "0#####") Cells(RowCount, "B") = Prefix & _ SequenceString & Suffix SequenceCount = SequenceCount + 1 RowCount = RowCount + 1 Loop End Sub "Little Penny" wrote: I have a spread sheet that could be a few hundred rows or 50000 plus row of data. In the first column is a 38 character (letters and numbers) data string. But in that string there is a six digit sequence number. The numbers always run consecutively. The first cell A1 could start a 000001 or 014235 or any six digit number including leading zeros. The sequence number starts at character 9 - 14. I'm trying to create a macro that will resequence the characters (9-14) staring from a sequence number I chose. For example" The first sequene number below is 010926 thru 010932 A 1 ABCDASAY01092600040004000000000000XXXX 2 ABCDASAY01092700040004000000000000XXXX 3 ABCDASAY01092800040004000000000000XXXX 4 ABCDASAY01092900040004000000000000XXXX 5 ABCDASAY01093000040004000000000000XXXX 6 ABCDASAY01093100040004000000000000XXXX 7 ABCDASAY01093200040004000000000000XXXX f I wanted to resequence column at starting at 004421 the macro would return this A 1 ABCDASAY00442100040004000000000000XXXX 2 ABCDASAY00442200040004000000000000XXXX 3 ABCDASAY00442300040004000000000000XXXX 4 ABCDASAY00442400040004000000000000XXXX 5 ABCDASAY00442500040004000000000000XXXX 6 ABCDASAY00442600040004000000000000XXXX 7 ABCDASAY00442700040004000000000000XXXX Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating number formula to count number occurences in a data set | Excel Programming | |||
Convert a number formatted as text to a number in a macro | Excel Programming | |||
macro adding a number to a number already in a cell | Excel Programming | |||
macro adding a number to a number already in a cell | Excel Programming | |||
macro adding a number to a number already in a cell | Excel Programming |