Text Columns Function

[KKD]

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?

 

[Ms-Exl-Learner]

May be this...

https://www.microsoft.com/office/co...c108bb-045d-40e5-9187-a5785baa67be&sloc=en-us

 

[מיכ×ל (מיקי) ×בידן]

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

 

[Don Guillett]

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

 

[KKD]

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?

 

[Dave Peterson]

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 D12 (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