Home |
Search |
Today's Posts |
#1
|
|||
|
|||
merging and formating data
I have data in 2 columns that i need to merge.
IE: A B 234 245 needs to be: 234245 Now i know i can concantenate the data but then i'm stuck. The data can be of any number of rows, so i need to select only the cells with data in them. I then have to take the data and transpose it. IE: A 234 456 689 needs to be: A B C 234 456 689. This way i can save it as comma delimited and import it into another source. Basically i need my ending data to be 123,345,677,8984. Any ideas on how to get this all done? I would like to make it a macro so i dont have to manually do it each time. thanks. |
#2
|
|||
|
|||
Daniel.
Without transposing....... Sub ConCat_Cells() Dim x As Range Dim y As Range Dim z As Range Dim w As String Dim sbuf As String On Error GoTo endit w = InputBox("Enter the Type of De-limiter Desired") Set z = Application.InputBox("Select Destination Cell", _ "Destination Cell", , , , , , 8) Application.SendKeys "+{F8}" Set x = Application.InputBox _ ("Select Cells...Contiguous or Non-Contiguous", _ "Cells Selection", , , , , , 8) For Each y In x If Len(y.text) 0 Then sbuf = sbuf & y.text & w Next z = Left(sbuf, Len(sbuf) - 1) Exit Sub endit: MsgBox "Nothing Selected. Please try again." End Sub Will ignore blanks in the selected range. Gord Dibben Excel MVP On Fri, 18 Feb 2005 02:02:15 GMT, "Daniel M" wrote: I have data in 2 columns that i need to merge. IE: A B 234 245 needs to be: 234245 Now i know i can concantenate the data but then i'm stuck. The data can be of any number of rows, so i need to select only the cells with data in them. I then have to take the data and transpose it. IE: A 234 456 689 needs to be: A B C 234 456 689. This way i can save it as comma delimited and import it into another source. Basically i need my ending data to be 123,345,677,8984. Any ideas on how to get this all done? I would like to make it a macro so i dont have to manually do it each time. thanks. |
#3
|
|||
|
|||
Thanks, The problem with this is i have to select every cell one at a time.
I have 40 plus rows that i need joined. This macro forces me to do this 40 plus times. Any ideas on fixing this? "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Daniel. Without transposing....... Sub ConCat_Cells() Dim x As Range Dim y As Range Dim z As Range Dim w As String Dim sbuf As String On Error GoTo endit w = InputBox("Enter the Type of De-limiter Desired") Set z = Application.InputBox("Select Destination Cell", _ "Destination Cell", , , , , , 8) Application.SendKeys "+{F8}" Set x = Application.InputBox _ ("Select Cells...Contiguous or Non-Contiguous", _ "Cells Selection", , , , , , 8) For Each y In x If Len(y.text) 0 Then sbuf = sbuf & y.text & w Next z = Left(sbuf, Len(sbuf) - 1) Exit Sub endit: MsgBox "Nothing Selected. Please try again." End Sub Will ignore blanks in the selected range. Gord Dibben Excel MVP On Fri, 18 Feb 2005 02:02:15 GMT, "Daniel M" wrote: I have data in 2 columns that i need to merge. IE: A B 234 245 needs to be: 234245 Now i know i can concantenate the data but then i'm stuck. The data can be of any number of rows, so i need to select only the cells with data in them. I then have to take the data and transpose it. IE: A 234 456 689 needs to be: A B C 234 456 689. This way i can save it as comma delimited and import it into another source. Basically i need my ending data to be 123,345,677,8984. Any ideas on how to get this all done? I would like to make it a macro so i dont have to manually do it each time. thanks. |
#4
|
|||
|
|||
Daniel
I guess I don't understand your layout. First you state you want Column A 234 joined with column B 245 to return 234245 This can be done with =A1 & B1 entered in C1 to give 234245 per your example and dragged down Then you state that Column A has numbers in A1, A2 and A3 of 234, 456, 689 and you want them in a row as 234 456 689 so youi save as comma-delimited. For that step, select column A and run my macro. You will get 234,456,689 in one cell of your choice. Now........What happened to column B? Where does your first example fit with your second example? Gord On Sat, 19 Feb 2005 03:29:33 GMT, "Daniel M" wrote: Thanks, The problem with this is i have to select every cell one at a time. I have 40 plus rows that i need joined. This macro forces me to do this 40 plus times. Any ideas on fixing this? "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . Daniel. Without transposing....... Sub ConCat_Cells() Dim x As Range Dim y As Range Dim z As Range Dim w As String Dim sbuf As String On Error GoTo endit w = InputBox("Enter the Type of De-limiter Desired") Set z = Application.InputBox("Select Destination Cell", _ "Destination Cell", , , , , , 8) Application.SendKeys "+{F8}" Set x = Application.InputBox _ ("Select Cells...Contiguous or Non-Contiguous", _ "Cells Selection", , , , , , 8) For Each y In x If Len(y.text) 0 Then sbuf = sbuf & y.text & w Next z = Left(sbuf, Len(sbuf) - 1) Exit Sub endit: MsgBox "Nothing Selected. Please try again." End Sub Will ignore blanks in the selected range. Gord Dibben Excel MVP On Fri, 18 Feb 2005 02:02:15 GMT, "Daniel M" wrote: I have data in 2 columns that i need to merge. IE: A B 234 245 needs to be: 234245 Now i know i can concantenate the data but then i'm stuck. The data can be of any number of rows, so i need to select only the cells with data in them. I then have to take the data and transpose it. IE: A 234 456 689 needs to be: A B C 234 456 689. This way i can save it as comma delimited and import it into another source. Basically i need my ending data to be 123,345,677,8984. Any ideas on how to get this all done? I would like to make it a macro so i dont have to manually do it each time. thanks. |
#5
|
|||
|
|||
Gord,
Thanks for the explination. I went back and looked at your code. I was not using it right. I do need column A and B added in C with =A1&B1. After this is done i need to take all of the data in column C and have it comma delimited. I think i can get there from here. Thanks for the help! daniel. "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Daniel I guess I don't understand your layout. First you state you want Column A 234 joined with column B 245 to return 234245 This can be done with =A1 & B1 entered in C1 to give 234245 per your example and dragged down Then you state that Column A has numbers in A1, A2 and A3 of 234, 456, 689 and you want them in a row as 234 456 689 so youi save as comma-delimited. For that step, select column A and run my macro. You will get 234,456,689 in one cell of your choice. Now........What happened to column B? Where does your first example fit with your second example? Gord On Sat, 19 Feb 2005 03:29:33 GMT, "Daniel M" wrote: Thanks, The problem with this is i have to select every cell one at a time. I have 40 plus rows that i need joined. This macro forces me to do this 40 plus times. Any ideas on fixing this? "Gord Dibben" <gorddibbATshawDOTca wrote in message . .. Daniel. Without transposing....... Sub ConCat_Cells() Dim x As Range Dim y As Range Dim z As Range Dim w As String Dim sbuf As String On Error GoTo endit w = InputBox("Enter the Type of De-limiter Desired") Set z = Application.InputBox("Select Destination Cell", _ "Destination Cell", , , , , , 8) Application.SendKeys "+{F8}" Set x = Application.InputBox _ ("Select Cells...Contiguous or Non-Contiguous", _ "Cells Selection", , , , , , 8) For Each y In x If Len(y.text) 0 Then sbuf = sbuf & y.text & w Next z = Left(sbuf, Len(sbuf) - 1) Exit Sub endit: MsgBox "Nothing Selected. Please try again." End Sub Will ignore blanks in the selected range. Gord Dibben Excel MVP On Fri, 18 Feb 2005 02:02:15 GMT, "Daniel M" wrote: I have data in 2 columns that i need to merge. IE: A B 234 245 needs to be: 234245 Now i know i can concantenate the data but then i'm stuck. The data can be of any number of rows, so i need to select only the cells with data in them. I then have to take the data and transpose it. IE: A 234 456 689 needs to be: A B C 234 456 689. This way i can save it as comma delimited and import it into another source. Basically i need my ending data to be 123,345,677,8984. Any ideas on how to get this all done? I would like to make it a macro so i dont have to manually do it each time. thanks. |
#6
|
|||
|
|||
Yep.
First combine A and B in C then paste values. Run the macro and select all of column C to give a comma delimited string of all column C in one cell. Gord On Sun, 20 Feb 2005 05:22:01 GMT, "Daniel M" wrote: Gord, Thanks for the explination. I went back and looked at your code. I was not using it right. I do need column A and B added in C with =A1&B1. After this is done i need to take all of the data in column C and have it comma delimited. I think i can get there from here. Thanks for the help! daniel. "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . Daniel I guess I don't understand your layout. First you state you want Column A 234 joined with column B 245 to return 234245 This can be done with =A1 & B1 entered in C1 to give 234245 per your example and dragged down Then you state that Column A has numbers in A1, A2 and A3 of 234, 456, 689 and you want them in a row as 234 456 689 so youi save as comma-delimited. For that step, select column A and run my macro. You will get 234,456,689 in one cell of your choice. Now........What happened to column B? Where does your first example fit with your second example? Gord On Sat, 19 Feb 2005 03:29:33 GMT, "Daniel M" wrote: Thanks, The problem with this is i have to select every cell one at a time. I have 40 plus rows that i need joined. This macro forces me to do this 40 plus times. Any ideas on fixing this? "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Daniel. Without transposing....... Sub ConCat_Cells() Dim x As Range Dim y As Range Dim z As Range Dim w As String Dim sbuf As String On Error GoTo endit w = InputBox("Enter the Type of De-limiter Desired") Set z = Application.InputBox("Select Destination Cell", _ "Destination Cell", , , , , , 8) Application.SendKeys "+{F8}" Set x = Application.InputBox _ ("Select Cells...Contiguous or Non-Contiguous", _ "Cells Selection", , , , , , 8) For Each y In x If Len(y.text) 0 Then sbuf = sbuf & y.text & w Next z = Left(sbuf, Len(sbuf) - 1) Exit Sub endit: MsgBox "Nothing Selected. Please try again." End Sub Will ignore blanks in the selected range. Gord Dibben Excel MVP On Fri, 18 Feb 2005 02:02:15 GMT, "Daniel M" wrote: I have data in 2 columns that i need to merge. IE: A B 234 245 needs to be: 234245 Now i know i can concantenate the data but then i'm stuck. The data can be of any number of rows, so i need to select only the cells with data in them. I then have to take the data and transpose it. IE: A 234 456 689 needs to be: A B C 234 456 689. This way i can save it as comma delimited and import it into another source. Basically i need my ending data to be 123,345,677,8984. Any ideas on how to get this all done? I would like to make it a macro so i dont have to manually do it each time. thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|