Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Columns Function
The data I am bringing in to Excel comes in two rows for a single record. I
need to reformat it so that all of the data is on one line. As there are several thousand records, I need to do find a way to automate this. Any suggestions? -- KKD |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Columns Function
May be this...
https://www.microsoft.com/office/com...7be&sloc=en-us -------------------- (Ms-Exl-Learner) -------------------- "KKD" wrote: The data I am bringing in to Excel comes in two rows for a single record. I need to reformat it so that all of the data is on one line. As there are several thousand records, I need to do find a way to automate this. Any suggestions? -- KKD |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Columns Function
To my opinion it will be very difficult or impossible a solution if the
without having the Data in front of us. However, you may try to record a Macro of the Cutting/Pasting the data from the 2nd. row, up to the 1st. row and Deleting the empty 2nd. row. All this should be "nested" within some sort of loop. Micky "KKD" wrote: The data I am bringing in to Excel comes in two rows for a single record. I need to reformat it so that all of the data is on one line. As there are several thousand records, I need to do find a way to automate this. Any suggestions? -- KKD |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Columns Function
Try
Option Explicit Sub putononelinepereach() 'assumes NO HEADER row Dim lr As Long Dim i As Long Dim slc As Long Dim dlc As Long lr = Application.RoundUp(Cells(Rows.Count, 1) _ .End(xlUp).Row / 2, 0) * 2 For i = lr To 2 Step -2 slc = Cells(i, Columns.Count).End(xlToLeft).Column dlc = Cells(i - 1, Columns.Count).End(xlToLeft).Column + 1 Cells(i, 1).Resize(, slc).Copy Cells(i - 1, dlc) Rows(i).Delete Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "KKD" wrote in message ... The data I am bringing in to Excel comes in two rows for a single record. I need to reformat it so that all of the data is on one line. As there are several thousand records, I need to do find a way to automate this. Any suggestions? -- KKD |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Columns Function
the data is fairly simple:
1234 abcd xyz Name I want the data piece "name" to be moved into the above row in its own cell. 1234 abcd xyz Name When I bring the data in, I am using the "text to columns" function as it all comes into column A only. If you think a macro is the only way, can you instruct how to go about that? -- KKD "מיכאל (מיקי) אבידן" wrote: To my opinion it will be very difficult or impossible a solution if the without having the Data in front of us. However, you may try to record a Macro of the Cutting/Pasting the data from the 2nd. row, up to the 1st. row and Deleting the empty 2nd. row. All this should be "nested" within some sort of loop. Micky "KKD" wrote: The data I am bringing in to Excel comes in two rows for a single record. I need to reformat it so that all of the data is on one line. As there are several thousand records, I need to do find a way to automate this. Any suggestions? -- KKD |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Columns Function
3 columns
1 column 3 columns 1 column 3 columns 1 column (and on and on and on) right? I'd put this in D1: =A2 Put this in D2: =NA() Select D1:D2 (both cells!) and drag down as far as your data goes. (Give it a glance to make sure that the data looks right before you continue. Then select column D Edit|copy Edit|Paste special|Values Select column D (again if you have to) Ctrl-g (or F5 or edit|goto) Special|Check constants, but only check Errors (uncheck the other options). This will change the selection to just the cells with errors. Rightclick on one of the cells Delete Entire row KKD wrote: the data is fairly simple: 1234 abcd xyz Name I want the data piece "name" to be moved into the above row in its own cell. 1234 abcd xyz Name When I bring the data in, I am using the "text to columns" function as it all comes into column A only. If you think a macro is the only way, can you instruct how to go about that? -- KKD "מיכאל (מיקי) אבידן" wrote: To my opinion it will be very difficult or impossible a solution if the without having the Data in front of us. However, you may try to record a Macro of the Cutting/Pasting the data from the 2nd. row, up to the 1st. row and Deleting the empty 2nd. row. All this should be "nested" within some sort of loop. Micky "KKD" wrote: The data I am bringing in to Excel comes in two rows for a single record. I need to reformat it so that all of the data is on one line. As there are several thousand records, I need to do find a way to automate this. Any suggestions? -- KKD -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function on "Text to Columns" | Excel Worksheet Functions | |||
Using Text to Columns with a function | Excel Worksheet Functions | |||
Need Function for Text To Columns | Excel Worksheet Functions | |||
Text To Columns Function | Excel Worksheet Functions | |||
Linking text columns with text and data columns | Excel Worksheet Functions |