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

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

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

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