Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP!!
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP!!
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP!!
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP!!
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP!!
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP!!
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP!!
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP!!
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|