I need help. i have a worksheet that is set up in columns like:
id acct no amount
I have 250 rows of data and a lots of columns
Whiat I would like to do is set it up so that the rows look like this
id account amount
for all.
is there a way to do a macro that will take all the data by column and move
it to a row and continue without overwriting the data?

Any help will be greatly appreciated

It's easy to do what you want, I think, but I don't know what you have nor
what you want to have. Your explanation of what you have is not clear. Is
it that you have repeating columns of 3 and you want all of the data put
into 3 columns total? Post back and provide some examples of what you have.
HTH Otto














"Mac" wrote in message
...
I need help. i have a worksheet that is set up in columns like:
id acct no amount
I have 250 rows of data and a lots of columns
Whiat I would like to do is set it up so that the rows look like this
id account amount
for all.
is there a way to do a macro that will take all the data by column and
move
it to a row and continue without overwriting the data?

Any help will be greatly appreciated

More info and sample layout.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mac" wrote in message
...
I need help. i have a worksheet that is set up in columns like:
id acct no amount
I have 250 rows of data and a lots of columns
Whiat I would like to do is set it up so that the rows look like this
id account amount
for all.
is there a way to do a macro that will take all the data by column and
move
it to a row and continue without overwriting the data?

Any help will be greatly appreciated

Thank you for answering my query.
My worksheet is set up like this.
id acct no 1025 acctno 1030 acct 1040 acct 1050
1234 1500 2500 3600 4000
2345 5000 5000 5000 5000

i want to have it look like this
id acct amount
1234 1025 1500
2345 1025 5000
1234 2500 2500
1234 1040 3600
1050 1050 4000

I have about 250 rows of data and up to 200 columns of data
Can you help.
Thank you!!!!

"Otto Moehrbach" wrote:

It's easy to do what you want, I think, but I don't know what you have nor
what you want to have. Your explanation of what you have is not clear. Is
it that you have repeating columns of 3 and you want all of the data put
into 3 columns total? Post back and provide some examples of what you have.
HTH Otto














"Mac" wrote in message
...
I need help. i have a worksheet that is set up in columns like:
id acct no amount
I have 250 rows of data and a lots of columns
Whiat I would like to do is set it up so that the rows look like this
id account amount
for all.
is there a way to do a macro that will take all the data by column and
move
it to a row and continue without overwriting the data?

Any help will be greatly appreciated

Your sample final product doesn't have a pattern. I think you meant for it
to have one though. How about ALL the ID of 1234 listed first with all the
Acc numbers and amounts that go with that ID, then the next ID, and so
forth? Or do you want ALL of the same Acc number to be listed together?
Either way you can always filter the final product to see what you want.
Unless you tell me different I'm going to use a new blank sheet for the
final product. Otto
"Mac" wrote in message
...
Thank you for answering my query.
My worksheet is set up like this.
id acct no 1025 acctno 1030 acct 1040 acct 1050
1234 1500 2500 3600 4000
2345 5000 5000 5000 5000

i want to have it look like this
id acct amount
1234 1025 1500
2345 1025 5000
1234 2500 2500
1234 1040 3600
1050 1050 4000

I have about 250 rows of data and up to 200 columns of data
Can you help.
Thank you!!!!

"Otto Moehrbach" wrote:

It's easy to do what you want, I think, but I don't know what you have
nor
what you want to have. Your explanation of what you have is not clear.
Is
it that you have repeating columns of 3 and you want all of the data put
into 3 columns total? Post back and provide some examples of what you
have.
HTH Otto














"Mac" wrote in message
...
I need help. i have a worksheet that is set up in columns like:
id acct no amount
I have 250 rows of data and a lots of columns
Whiat I would like to do is set it up so that the rows look like this
id account amount
for all.
is there a way to do a macro that will take all the data by column and
move
it to a row and continue without overwriting the data?

Any help will be greatly appreciated

Mac
Here is a macro that will do what you want. I assumed that the sheet that
holds the data is named "Start" and the sheet that will get the final
product is named "Finish". Change these in the code as you need to.
I had a bit of difficulty because I didn't know how to extract the account
number from the real headers that you have. Looking at what you gave me,
headers like "Acct no 1025", "Acct 1040", etc. I wrote this macro to take
the last 4 characters of those headers and use that as the account number in
the final product. Think about this and see if that fits with your data.
Let me know what changes you need to have made. Otto
Sub ReArrangeData()
Dim rColA As Range
Dim i As Range
Dim j As Range
Dim Dest As Range
Dim rRowi As Range
Application.ScreenUpdating = False
Sheets("Start").Select
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set Dest = Sheets("Finish").Range("A2")
For Each i In rColA
Set rRowi = Range(Cells(i.Row, 2), Cells(i.Row,
Columns.Count).End(xlToLeft))
For Each j In rRowi
Dest.Value = i.Value
Dest.Offset(, 1).Value = Right(Cells(1, j.Column).Value,
4)
Dest.Offset(, 2).Value = j.Value
Set Dest = Dest.Offset(1)
Next j
Next i
Application.ScreenUpdating = True
End Sub
"Mac" wrote in message
...
Thank you for answering my query.
My worksheet is set up like this.
id acct no 1025 acctno 1030 acct 1040 acct 1050
1234 1500 2500 3600 4000
2345 5000 5000 5000 5000

i want to have it look like this
id acct amount
1234 1025 1500
2345 1025 5000
1234 2500 2500
1234 1040 3600
1050 1050 4000

I have about 250 rows of data and up to 200 columns of data
Can you help.
Thank you!!!!

"Otto Moehrbach" wrote:

It's easy to do what you want, I think, but I don't know what you have
nor
what you want to have. Your explanation of what you have is not clear.
Is
it that you have repeating columns of 3 and you want all of the data put
into 3 columns total? Post back and provide some examples of what you
have.
HTH Otto














"Mac" wrote in message
...
I need help. i have a worksheet that is set up in columns like:
id acct no amount
I have 250 rows of data and a lots of columns
Whiat I would like to do is set it up so that the rows look like this
id account amount
for all.
is there a way to do a macro that will take all the data by column and
move
it to a row and continue without overwriting the data?

Any help will be greatly appreciated

Otto
I cannot thank you enough . It worked. The only problem I have is that the
ID number and account number are not coming out right. The id number is
dropping the 0. (al my id numbers start with 09) and my account number are
all text ex(3250.0) . Is this fixable?
I cannot tell you how much time you have saved me. Again thank you for
your help

--
thank you mac


"Otto Moehrbach" wrote:

Mac
Here is a macro that will do what you want. I assumed that the sheet that
holds the data is named "Start" and the sheet that will get the final
product is named "Finish". Change these in the code as you need to.
I had a bit of difficulty because I didn't know how to extract the account
number from the real headers that you have. Looking at what you gave me,
headers like "Acct no 1025", "Acct 1040", etc. I wrote this macro to take
the last 4 characters of those headers and use that as the account number in
the final product. Think about this and see if that fits with your data.
Let me know what changes you need to have made. Otto
Sub ReArrangeData()
Dim rColA As Range
Dim i As Range
Dim j As Range
Dim Dest As Range
Dim rRowi As Range
Application.ScreenUpdating = False
Sheets("Start").Select
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set Dest = Sheets("Finish").Range("A2")
For Each i In rColA
Set rRowi = Range(Cells(i.Row, 2), Cells(i.Row,
Columns.Count).End(xlToLeft))
For Each j In rRowi
Dest.Value = i.Value
Dest.Offset(, 1).Value = Right(Cells(1, j.Column).Value,
4)
Dest.Offset(, 2).Value = j.Value
Set Dest = Dest.Offset(1)
Next j
Next i
Application.ScreenUpdating = True
End Sub
"Mac" wrote in message
...
Thank you for answering my query.
My worksheet is set up like this.
id acct no 1025 acctno 1030 acct 1040 acct 1050
1234 1500 2500 3600 4000
2345 5000 5000 5000 5000

i want to have it look like this
id acct amount
1234 1025 1500
2345 1025 5000
1234 2500 2500
1234 1040 3600
1050 1050 4000

I have about 250 rows of data and up to 200 columns of data
Can you help.
Thank you!!!!

"Otto Moehrbach" wrote:

It's easy to do what you want, I think, but I don't know what you have
nor
what you want to have. Your explanation of what you have is not clear.
Is
it that you have repeating columns of 3 and you want all of the data put
into 3 columns total? Post back and provide some examples of what you
have.
HTH Otto














"Mac" wrote in message
...
I need help. i have a worksheet that is set up in columns like:
id acct no amount
I have 250 rows of data and a lots of columns
Whiat I would like to do is set it up so that the rows look like this
id account amount
for all.
is there a way to do a macro that will take all the data by column and
move
it to a row and continue without overwriting the data?

Any help will be greatly appreciated

Mac
I modified the code to format the first 2 columns of the "Finish" sheet
to text. That retains the leading zeros in the ID and makes the account
numbers text. Is that what you wanted? Otto
Sub ReArrangeData()
Dim rColA As Range
Dim i As Range
Dim j As Range
Dim Dest As Range
Dim rRowi As Range
Application.ScreenUpdating = False
Sheets("Start").Select
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set Dest = Sheets("Finish").Range("A2")
Dest.Resize(, 2).NumberFormat = "@"
For Each i In rColA
Set rRowi = Range(Cells(i.Row, 2), Cells(i.Row,
Columns.Count).End(xlToLeft))
For Each j In rRowi
Dest.Value = CStr(i.Value)
Dest.Offset(, 1).Value = Right(Cells(1, j.Column).Value,
4)
Dest.Offset(, 2).Value = j.Value
Set Dest = Dest.Offset(1)
Dest.Resize(, 2).NumberFormat = "@"
Next j
Next i
Application.ScreenUpdating = True
End Sub
"mac" wrote in message
...
Otto
I cannot thank you enough . It worked. The only problem I have is that
the
ID number and account number are not coming out right. The id number is
dropping the 0. (al my id numbers start with 09) and my account number are
all text ex(3250.0) . Is this fixable?
I cannot tell you how much time you have saved me. Again thank you for
your help

--
thank you mac


"Otto Moehrbach" wrote:

Mac
Here is a macro that will do what you want. I assumed that the sheet
that
holds the data is named "Start" and the sheet that will get the final
product is named "Finish". Change these in the code as you need to.
I had a bit of difficulty because I didn't know how to extract the
account
number from the real headers that you have. Looking at what you gave me,
headers like "Acct no 1025", "Acct 1040", etc. I wrote this macro to take
the last 4 characters of those headers and use that as the account number
in
the final product. Think about this and see if that fits with your data.
Let me know what changes you need to have made. Otto
Sub ReArrangeData()
Dim rColA As Range
Dim i As Range
Dim j As Range
Dim Dest As Range
Dim rRowi As Range
Application.ScreenUpdating = False
Sheets("Start").Select
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set Dest = Sheets("Finish").Range("A2")
For Each i In rColA
Set rRowi = Range(Cells(i.Row, 2), Cells(i.Row,
Columns.Count).End(xlToLeft))
For Each j In rRowi
Dest.Value = i.Value
Dest.Offset(, 1).Value = Right(Cells(1,
j.Column).Value,
4)
Dest.Offset(, 2).Value = j.Value
Set Dest = Dest.Offset(1)
Next j
Next i
Application.ScreenUpdating = True
End Sub
"Mac" wrote in message
...
Thank you for answering my query.
My worksheet is set up like this.
id acct no 1025 acctno 1030 acct 1040 acct 1050
1234 1500 2500 3600 4000
2345 5000 5000 5000 5000

i want to have it look like this
id acct amount
1234 1025 1500
2345 1025 5000
1234 2500 2500
1234 1040 3600
1050 1050 4000

I have about 250 rows of data and up to 200 columns of data
Can you help.
Thank you!!!!

"Otto Moehrbach" wrote:

It's easy to do what you want, I think, but I don't know what you have
nor
what you want to have. Your explanation of what you have is not
clear.
Is
it that you have repeating columns of 3 and you want all of the data
put
into 3 columns total? Post back and provide some examples of what you
have.
HTH Otto














"Mac" wrote in message
...
I need help. i have a worksheet that is set up in columns like:
id acct no amount
I have 250 rows of data and a lots of columns
Whiat I would like to do is set it up so that the rows look like
this
id account amount
for all.
is there a way to do a macro that will take all the data by column
and
move
it to a row and continue without overwriting the data?

Any help will be greatly appreciated

ello Otto,

I don't know how to thank you for helping me. The id is working fine but
the account number are not coming over correctly. Example is acct no 0100.0
is coming over as 00.0 and 3003.1 is coming over as 03.1. Otto, I would
like to thank you again. I have about 20,000 acccount number and i used to
copy and paste in order to get it the way I need it so you have saved me a
ton of time.
--
thank you mac


"Otto Moehrbach" wrote:

Mac
I modified the code to format the first 2 columns of the "Finish" sheet
to text. That retains the leading zeros in the ID and makes the account
numbers text. Is that what you wanted? Otto
Sub ReArrangeData()
Dim rColA As Range
Dim i As Range
Dim j As Range
Dim Dest As Range
Dim rRowi As Range
Application.ScreenUpdating = False
Sheets("Start").Select
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set Dest = Sheets("Finish").Range("A2")
Dest.Resize(, 2).NumberFormat = "@"
For Each i In rColA
Set rRowi = Range(Cells(i.Row, 2), Cells(i.Row,
Columns.Count).End(xlToLeft))
For Each j In rRowi
Dest.Value = CStr(i.Value)
Dest.Offset(, 1).Value = Right(Cells(1, j.Column).Value,
4)
Dest.Offset(, 2).Value = j.Value
Set Dest = Dest.Offset(1)
Dest.Resize(, 2).NumberFormat = "@"
Next j
Next i
Application.ScreenUpdating = True
End Sub
"mac" wrote in message
...
Otto
I cannot thank you enough . It worked. The only problem I have is that
the
ID number and account number are not coming out right. The id number is
dropping the 0. (al my id numbers start with 09) and my account number are
all text ex(3250.0) . Is this fixable?
I cannot tell you how much time you have saved me. Again thank you for
your help

--
thank you mac


"Otto Moehrbach" wrote:

Mac
Here is a macro that will do what you want. I assumed that the sheet
that
holds the data is named "Start" and the sheet that will get the final
product is named "Finish". Change these in the code as you need to.
I had a bit of difficulty because I didn't know how to extract the
account
number from the real headers that you have. Looking at what you gave me,
headers like "Acct no 1025", "Acct 1040", etc. I wrote this macro to take
the last 4 characters of those headers and use that as the account number
in
the final product. Think about this and see if that fits with your data.
Let me know what changes you need to have made. Otto
Sub ReArrangeData()
Dim rColA As Range
Dim i As Range
Dim j As Range
Dim Dest As Range
Dim rRowi As Range
Application.ScreenUpdating = False
Sheets("Start").Select
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set Dest = Sheets("Finish").Range("A2")
For Each i In rColA
Set rRowi = Range(Cells(i.Row, 2), Cells(i.Row,
Columns.Count).End(xlToLeft))
For Each j In rRowi
Dest.Value = i.Value
Dest.Offset(, 1).Value = Right(Cells(1,
j.Column).Value,
4)
Dest.Offset(, 2).Value = j.Value
Set Dest = Dest.Offset(1)
Next j
Next i
Application.ScreenUpdating = True
End Sub
"Mac" wrote in message
...
Thank you for answering my query.
My worksheet is set up like this.
id acct no 1025 acctno 1030 acct 1040 acct 1050
1234 1500 2500 3600 4000
2345 5000 5000 5000 5000

i want to have it look like this
id acct amount
1234 1025 1500
2345 1025 5000
1234 2500 2500
1234 1040 3600
1050 1050 4000

I have about 250 rows of data and up to 200 columns of data
Can you help.
Thank you!!!!

"Otto Moehrbach" wrote:

It's easy to do what you want, I think, but I don't know what you have
nor
what you want to have. Your explanation of what you have is not
clear.
Is
it that you have repeating columns of 3 and you want all of the data
put
into 3 columns total? Post back and provide some examples of what you
have.
HTH Otto














"Mac" wrote in message
...
I need help. i have a worksheet that is set up in columns like:
id acct no amount
I have 250 rows of data and a lots of columns
Whiat I would like to do is set it up so that the rows look like
this
id account amount
for all.
is there a way to do a macro that will take all the data by column
and
move
it to a row and continue without overwriting the data?

Any help will be greatly appreciated

Otto

I figured it out I changed the 4 to 6 and it works!!!!! Thank you so
much.!!!!!
--
thank you mac


"Otto Moehrbach" wrote:

Mac
I modified the code to format the first 2 columns of the "Finish" sheet
to text. That retains the leading zeros in the ID and makes the account
numbers text. Is that what you wanted? Otto
Sub ReArrangeData()
Dim rColA As Range
Dim i As Range
Dim j As Range
Dim Dest As Range
Dim rRowi As Range
Application.ScreenUpdating = False
Sheets("Start").Select
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set Dest = Sheets("Finish").Range("A2")
Dest.Resize(, 2).NumberFormat = "@"
For Each i In rColA
Set rRowi = Range(Cells(i.Row, 2), Cells(i.Row,
Columns.Count).End(xlToLeft))
For Each j In rRowi
Dest.Value = CStr(i.Value)
Dest.Offset(, 1).Value = Right(Cells(1, j.Column).Value,
4)
Dest.Offset(, 2).Value = j.Value
Set Dest = Dest.Offset(1)
Dest.Resize(, 2).NumberFormat = "@"
Next j
Next i
Application.ScreenUpdating = True
End Sub
"mac" wrote in message
...
Otto
I cannot thank you enough . It worked. The only problem I have is that
the
ID number and account number are not coming out right. The id number is
dropping the 0. (al my id numbers start with 09) and my account number are
all text ex(3250.0) . Is this fixable?
I cannot tell you how much time you have saved me. Again thank you for
your help

--
thank you mac


"Otto Moehrbach" wrote:

Mac
Here is a macro that will do what you want. I assumed that the sheet
that
holds the data is named "Start" and the sheet that will get the final
product is named "Finish". Change these in the code as you need to.
I had a bit of difficulty because I didn't know how to extract the
account
number from the real headers that you have. Looking at what you gave me,
headers like "Acct no 1025", "Acct 1040", etc. I wrote this macro to take
the last 4 characters of those headers and use that as the account number
in
the final product. Think about this and see if that fits with your data.
Let me know what changes you need to have made. Otto
Sub ReArrangeData()
Dim rColA As Range
Dim i As Range
Dim j As Range
Dim Dest As Range
Dim rRowi As Range
Application.ScreenUpdating = False
Sheets("Start").Select
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set Dest = Sheets("Finish").Range("A2")
For Each i In rColA
Set rRowi = Range(Cells(i.Row, 2), Cells(i.Row,
Columns.Count).End(xlToLeft))
For Each j In rRowi
Dest.Value = i.Value
Dest.Offset(, 1).Value = Right(Cells(1,
j.Column).Value,
4)
Dest.Offset(, 2).Value = j.Value
Set Dest = Dest.Offset(1)
Next j
Next i
Application.ScreenUpdating = True
End Sub
"Mac" wrote in message
...
Thank you for answering my query.
My worksheet is set up like this.
id acct no 1025 acctno 1030 acct 1040 acct 1050
1234 1500 2500 3600 4000
2345 5000 5000 5000 5000

i want to have it look like this
id acct amount
1234 1025 1500
2345 1025 5000
1234 2500 2500
1234 1040 3600
1050 1050 4000

I have about 250 rows of data and up to 200 columns of data
Can you help.
Thank you!!!!

"Otto Moehrbach" wrote:

It's easy to do what you want, I think, but I don't know what you have
nor
what you want to have. Your explanation of what you have is not
clear.
Is
it that you have repeating columns of 3 and you want all of the data
put
into 3 columns total? Post back and provide some examples of what you
have.
HTH Otto














"Mac" wrote in message
...
I need help. i have a worksheet that is set up in columns like:
id acct no amount
I have 250 rows of data and a lots of columns
Whiat I would like to do is set it up so that the rows look like
this
id account amount
for all.
is there a way to do a macro that will take all the data by column
and
move
it to a row and continue without overwriting the data?

Any help will be greatly appreciated

Mac
Remember that the 6 that you put in the code means that VBA will take
the last 6 characters from that cell. If you ALWAYS want those last 6
characters, then it will work right for you, but if not, then tell me what
your actual data conditions are and I'll see how I can massage the code to
account for the variations you have. Otto
"mac" wrote in message
...
Otto

I figured it out I changed the 4 to 6 and it works!!!!! Thank you so
much.!!!!!
--
thank you mac


"Otto Moehrbach" wrote:

Mac
I modified the code to format the first 2 columns of the "Finish"
sheet
to text. That retains the leading zeros in the ID and makes the account
numbers text. Is that what you wanted? Otto
Sub ReArrangeData()
Dim rColA As Range
Dim i As Range
Dim j As Range
Dim Dest As Range
Dim rRowi As Range
Application.ScreenUpdating = False
Sheets("Start").Select
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set Dest = Sheets("Finish").Range("A2")
Dest.Resize(, 2).NumberFormat = "@"
For Each i In rColA
Set rRowi = Range(Cells(i.Row, 2), Cells(i.Row,
Columns.Count).End(xlToLeft))
For Each j In rRowi
Dest.Value = CStr(i.Value)
Dest.Offset(, 1).Value = Right(Cells(1,
j.Column).Value,
4)
Dest.Offset(, 2).Value = j.Value
Set Dest = Dest.Offset(1)
Dest.Resize(, 2).NumberFormat = "@"
Next j
Next i
Application.ScreenUpdating = True
End Sub
"mac" wrote in message
...
Otto
I cannot thank you enough . It worked. The only problem I have is
that
the
ID number and account number are not coming out right. The id number
is
dropping the 0. (al my id numbers start with 09) and my account number
are
all text ex(3250.0) . Is this fixable?
I cannot tell you how much time you have saved me. Again thank you
for
your help

--
thank you mac


"Otto Moehrbach" wrote:

Mac
Here is a macro that will do what you want. I assumed that the sheet
that
holds the data is named "Start" and the sheet that will get the final
product is named "Finish". Change these in the code as you need to.
I had a bit of difficulty because I didn't know how to extract the
account
number from the real headers that you have. Looking at what you gave
me,
headers like "Acct no 1025", "Acct 1040", etc. I wrote this macro to
take
the last 4 characters of those headers and use that as the account
number
in
the final product. Think about this and see if that fits with your
data.
Let me know what changes you need to have made. Otto
Sub ReArrangeData()
Dim rColA As Range
Dim i As Range
Dim j As Range
Dim Dest As Range
Dim rRowi As Range
Application.ScreenUpdating = False
Sheets("Start").Select
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set Dest = Sheets("Finish").Range("A2")
For Each i In rColA
Set rRowi = Range(Cells(i.Row, 2), Cells(i.Row,
Columns.Count).End(xlToLeft))
For Each j In rRowi
Dest.Value = i.Value
Dest.Offset(, 1).Value = Right(Cells(1,
j.Column).Value,
4)
Dest.Offset(, 2).Value = j.Value
Set Dest = Dest.Offset(1)
Next j
Next i
Application.ScreenUpdating = True
End Sub
"Mac" wrote in message
...
Thank you for answering my query.
My worksheet is set up like this.
id acct no 1025 acctno 1030 acct 1040 acct 1050
1234 1500 2500 3600 4000
2345 5000 5000 5000 5000

i want to have it look like this
id acct amount
1234 1025 1500
2345 1025 5000
1234 2500 2500
1234 1040 3600
1050 1050 4000

I have about 250 rows of data and up to 200 columns of data
Can you help.
Thank you!!!!

"Otto Moehrbach" wrote:

It's easy to do what you want, I think, but I don't know what you
have
nor
what you want to have. Your explanation of what you have is not
clear.
Is
it that you have repeating columns of 3 and you want all of the
data
put
into 3 columns total? Post back and provide some examples of what
you
have.
HTH Otto














"Mac" wrote in message
...
I need help. i have a worksheet that is set up in columns like:
id acct no amount
I have 250 rows of data and a lots of columns
Whiat I would like to do is set it up so that the rows look like
this
id account amount
for all.
is there a way to do a macro that will take all the data by
column
and
move
it to a row and continue without overwriting the data?

Any help will be greatly appreciated

Hello Otto,

All my accounts have the same number of digits xxxx.x so it works great. I
cannot thank you enought for the help you have given me. You have no idea of
the time I spent getting the data the way I wanted. Again thank you!!!!!!
--
thank you mac


"Otto Moehrbach" wrote:

Mac
Remember that the 6 that you put in the code means that VBA will take
the last 6 characters from that cell. If you ALWAYS want those last 6
characters, then it will work right for you, but if not, then tell me what
your actual data conditions are and I'll see how I can massage the code to
account for the variations you have. Otto
"mac" wrote in message
...
Otto

I figured it out I changed the 4 to 6 and it works!!!!! Thank you so
much.!!!!!
--
thank you mac


"Otto Moehrbach" wrote:

Mac
I modified the code to format the first 2 columns of the "Finish"
sheet
to text. That retains the leading zeros in the ID and makes the account
numbers text. Is that what you wanted? Otto
Sub ReArrangeData()
Dim rColA As Range
Dim i As Range
Dim j As Range
Dim Dest As Range
Dim rRowi As Range
Application.ScreenUpdating = False
Sheets("Start").Select
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set Dest = Sheets("Finish").Range("A2")
Dest.Resize(, 2).NumberFormat = "@"
For Each i In rColA
Set rRowi = Range(Cells(i.Row, 2), Cells(i.Row,
Columns.Count).End(xlToLeft))
For Each j In rRowi
Dest.Value = CStr(i.Value)
Dest.Offset(, 1).Value = Right(Cells(1,
j.Column).Value,
4)
Dest.Offset(, 2).Value = j.Value
Set Dest = Dest.Offset(1)
Dest.Resize(, 2).NumberFormat = "@"
Next j
Next i
Application.ScreenUpdating = True
End Sub
"mac" wrote in message
...
Otto
I cannot thank you enough . It worked. The only problem I have is
that
the
ID number and account number are not coming out right. The id number
is
dropping the 0. (al my id numbers start with 09) and my account number
are
all text ex(3250.0) . Is this fixable?
I cannot tell you how much time you have saved me. Again thank you
for
your help

--
thank you mac


"Otto Moehrbach" wrote:

Mac
Here is a macro that will do what you want. I assumed that the sheet
that
holds the data is named "Start" and the sheet that will get the final
product is named "Finish". Change these in the code as you need to.
I had a bit of difficulty because I didn't know how to extract the
account
number from the real headers that you have. Looking at what you gave
me,
headers like "Acct no 1025", "Acct 1040", etc. I wrote this macro to
take
the last 4 characters of those headers and use that as the account
number
in
the final product. Think about this and see if that fits with your
data.
Let me know what changes you need to have made. Otto
Sub ReArrangeData()
Dim rColA As Range
Dim i As Range
Dim j As Range
Dim Dest As Range
Dim rRowi As Range
Application.ScreenUpdating = False
Sheets("Start").Select
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set Dest = Sheets("Finish").Range("A2")
For Each i In rColA
Set rRowi = Range(Cells(i.Row, 2), Cells(i.Row,
Columns.Count).End(xlToLeft))
For Each j In rRowi
Dest.Value = i.Value
Dest.Offset(, 1).Value = Right(Cells(1,
j.Column).Value,
4)
Dest.Offset(, 2).Value = j.Value
Set Dest = Dest.Offset(1)
Next j
Next i
Application.ScreenUpdating = True
End Sub
"Mac" wrote in message
...
Thank you for answering my query.
My worksheet is set up like this.
id acct no 1025 acctno 1030 acct 1040 acct 1050
1234 1500 2500 3600 4000
2345 5000 5000 5000 5000

i want to have it look like this
id acct amount
1234 1025 1500
2345 1025 5000
1234 2500 2500
1234 1040 3600
1050 1050 4000

I have about 250 rows of data and up to 200 columns of data
Can you help.
Thank you!!!!

"Otto Moehrbach" wrote:

It's easy to do what you want, I think, but I don't know what you
have
nor
what you want to have. Your explanation of what you have is not
clear.
Is
it that you have repeating columns of 3 and you want all of the
data
put
into 3 columns total? Post back and provide some examples of what
you
have.
HTH Otto














"Mac" wrote in message
...
I need help. i have a worksheet that is set up in columns like:
id acct no amount
I have 250 rows of data and a lots of columns
Whiat I would like to do is set it up so that the rows look like
this
id account amount
for all.
is there a way to do a macro that will take all the data by
column
and
move
it to a row and continue without overwriting the data?

Any help will be greatly appreciated