Combining Text in Multiple Lines

J

JimFor

Hi,

I am downloading from a mainframe and convert the file to an Excel file.
Eventually I sort the data. If I have a date field and I sort by date, Excel
keeps everything which appears on the same line the date appeared on. My data,
however, has multiple lines of text between dates. The extra data contains
things like order details. Sometimes I need that info and have to go back
to the unsorted file and type it into the sorted list. Is there anyway I can
highlight the cells with the text data, merge them so that Excel views them as
being on the same line as the date, and thus be able to keep the entire text
message in a sort? Right now the way I sort, text which appears on lines below
a date line drop out of the sort.

For example, suppose I have dates in col A. And I will sort by date. Several
lines of text appear in col B. The first date appears in row 1 col A. The
text associated with that date appear in rows 1 through 5 col B. The second
date appears in row 6, col A. The text associated with that date appears in
col B, row 6 through 11. After a sort by date, the text in rows 2 through 5
and below row 6 drop out. Can I highlight, say, cells B1-B5 and merge them so
the text all appears to be in the first row thus keeping the complete text in
the sort? Hope this is clear.
Thanks
 
E

Earl Kiosterud

Jim,

It sounds as if you have different types of records in your table. Excel's
functionality doesn't support that. There should be a date in every row
(every record). You may be able to have a macro copy dates down until
another date comes along. Or combine the text rows into the original dated
row.
 
B

BrianB

I am familiar with this sort of thing with files from Oracle which doe
not always produce the comma delimited textfile that we would wish for
Often including page headings and other garbage mixed in. See if you ca
get delimited first - or something like it that can be imported int
Excel direct.

You have to make sure that the rogue data always appears in the sam
column. If it does already, the first part of the problem is solved
You should find that this always relates to the same column heading
for the final table.

Have a look at the file in a text editor like Notepad to see what th
plain file looks like. If the data is still as you suggest it is a ver
complicated issue to sort out because we have to import the file int
Excel as Space Delimited - which gives the option to set colum
dividers - and use a macro to transfer the data correctly to anothe
sheet.

My latest effort extracts 250,000 records in 10 minutes - saving th
user a whole day's working manually. Run from hard drive, not server
 
J

JimFor

Or combine the text rows into the original >dated
row.
Click to expand...

That maybe what I want to do but don't know how. Say I have column of dates
and a column of other information which is in text format. The reason why
there is not a date in every row is because the text takes up multiple rows.
Here is a rough example:


COL A Col B
Row 1 10/29/00 This is an
Row 2 example of
Row 3 where the text
Row 4 prints
Row 5 10/30/00 More text in
.. every row...
More dates etc....

When I sort, I get the text "This is an" and "More text in..." The rest of the
text drops out. I think I need to merge(?) the text "This is an example of
where the text prints" into the cell in Col B Row 1. Then the entire text will
remain in the sorted list. Right now, all but the first line drop out. If I
need it, I have to find it again and then have to key it into the sorted
spreadsheet and use the text wrap function to enable the reader to see the
entire text. I may have hundreds of these rekeys. Makes sense? I admit I am
still working this out. Trying to handle a large volume of data using a system
which was not designed to be used this way, perhaps. However, times change...

Thanks for the replies...
 
D

Dave Peterson

Run this against a copy of your worksheet (or save your workbook first).

If it doesn't work, you can close without saving. (It destroys the original
data.)

Option Explicit
Sub testme()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

With ActiveSheet
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If IsEmpty(.Cells(iRow, "A")) Then
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value _
& vbLf & .Cells(iRow, "B").Value
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

If you see little squares in the cell that's combined, make sure that
format|cells|alignment tab has wraptext checked.
 
Top