Can I combine 2 rows to make one record?

[GreenPower]

A Report that I imported into Excel 2003 (for sorting the records) comes in
with some of the fields on a second row. How can I sort these records?

[Dave O]

Are the records parsing correctly into discrete fields, or are the
records all text that somehow wrap onto the next line? How the data
starts out and how it's showing up vs. how you want it to show up makes
a difference in how you address the problem.

The software that is creating the file for export *may* be including
line feed characters in the output; if the line feeds were not there
the second line would not be an issue. If this might be the case and
if you're feeling adventureous, make a backup of the export, open it
using MS Word, and search for a special character, ^l (that's the
caret symbol, shift 6, and a lower case L). If you see those within a
record, do a search and replace and replace with nothing, then save and
import to Excel. If the MS Word search yields nothing, try other
special characters in the search window (click the More button and the
Special button to reveal all your options).

Another possibility, again depending on the nature of the data: import
to Access rather than Excel.

[Earl Kiosterud]

Green,

If the two records don't have the same identifying entry in the column on
which you need to sort the table, you can't. And if it does, you'll still
probably need to have something in some column whereby you can distinguish
the two record types. Excel doesn't really have any tools for multiple
record types.

If not, that is, if the second record can be identified only by it's
position immediataly after it's matching first record, your best bet is to
combine them with a formula. In fact, you may wish to do that anyway, to
avail yourself of other tools you may want to use (Pivot tables, Subtotals,
Filtering, etc.). Post back. Give an example of a pair of matching
records.
--
Earl Kiosterud
www.smokeylake.com

"GreenPower" <GreenPower@discussions.microsoft.com> wrote in message
news:04E45E0A-B27E-4E2A-90F2-37879099146C@microsoft.com...
>A Report that I imported into Excel 2003 (for sorting the records) comes in
> with some of the fields on a second row. How can I sort these records?

[Michele]

I do this often, although there may be an easier way, this works. I insert
columns, one for each of the desired fields on the first row. On the second
row for the first set of records I read the data from the appropriate fields
in the first row and concatenate if necessary. For example:

Before inserting:
A B C
1 Michele 1234 Help Chicago
2 123-45-6789 773-123-4567

After inserting
A B C D
E F
1 Michele
1234 Help Chicago
2 =D1 =E1 =F1 123-45-6789
773-123-4567

After copying the formula to the bottom of the sheet, I insert a new
worksheet where I copy and paste this sheet as values. Sort the new
worksheet and delete the columns you no longer need and the old first rows,
which should be easy to identify because the data doesn't match. The data
isn't parsed well for your needs, concatenate the fields into the new columns.

It's cumbersome, but it works.

Hope this helps.
Michele


"GreenPower" wrote:

> A Report that I imported into Excel 2003 (for sorting the records) comes in
> with some of the fields on a second row. How can I sort these records?