software

I've come upon a problem with sorting that I don't know how to
tackle... I have entries in a workbook that I want to sort by a
transaction number, but each entry spans multiple rows. One quot;entryquot;
might look like this, for example:

TransID PassengerName Ticket#
leg of travel: Departure Arrival
leg of travel: Departure Arrival

I need to be able to sort by TransID or PassengerName while keeping the
quot;legs of travelquot; attached to the correct TransID/Ticket#.

Any Suggestions?
Thanks,
AndrewTry this:

Select the whole data list, including column headings.
Datagt;Sort
Select the column you want to sort by.
Click the [OK] button

That column will sort and all of the other cells will sort appropriately.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Proquot;Andrew Robertsquot; wrote:

gt; I've come upon a problem with sorting that I don't know how to
gt; tackle... I have entries in a workbook that I want to sort by a
gt; transaction number, but each entry spans multiple rows. One quot;entryquot;
gt; might look like this, for example:
gt;
gt; TransID PassengerName Ticket#
gt; leg of travel: Departure Arrival
gt; leg of travel: Departure Arrival
gt;
gt; I need to be able to sort by TransID or PassengerName while keeping the
gt; quot;legs of travelquot; attached to the correct TransID/Ticket#.
gt;
gt; Any Suggestions?
gt; Thanks,
gt; Andrew
gt;
gt;

When I sort the entire list, it leaves all of the quot;legs of travelquot;
sorted out at the bottom. I need them to stay with their respective
TransIDs (directly beneath them).

Incidentally, quot;legs of travelquot; shows up in the same column as quot;TransIDquot;
at the moment.

Thanks again,
AndrewInsert a new row to left of your dataset.
At the first incidence of TransID enter the following formula: (assumes data
starts at B2, that the TransID is followed by some unique indentifier such as
the TransID number)
=IF(left(B2,4)=quot;Transquot;,right(B2,5),B1)
Next copy this formula all the way down the row for the entire data set.
Select the entire row, right click, select Paste Special, select Values.

You can now sort the entire data set how ever you want but use the new field
to keep the three rows together.

quot;Andrew Robertsquot; wrote:

gt; I've come upon a problem with sorting that I don't know how to
gt; tackle... I have entries in a workbook that I want to sort by a
gt; transaction number, but each entry spans multiple rows. One quot;entryquot;
gt; might look like this, for example:
gt;
gt; TransID PassengerName Ticket#
gt; leg of travel: Departure Arrival
gt; leg of travel: Departure Arrival
gt;
gt; I need to be able to sort by TransID or PassengerName while keeping the
gt; quot;legs of travelquot; attached to the correct TransID/Ticket#.
gt;
gt; Any Suggestions?
gt; Thanks,
gt; Andrew
gt;
gt;

Ooops formula should be as follows:

=IF(left(B2,4)=quot;Tranquot;,right(B2,5),B1)
I had more than 4 letters in quot;Transquot;. An simpler version of this would be as
follows:

=IF(left(B2,4)=quot;Transquot;,B2,B1)
This would work if you didn't care if the words quot;TransIDquot; also appeared. The
previous formula is desigend to return the number only. Usefull if you are
then going to use this data to lookup other data.
quot;WCoasterquot; wrote:

gt; Insert a new row to left of your dataset.
gt; At the first incidence of TransID enter the following formula: (assumes data
gt; starts at B2, that the TransID is followed by some unique indentifier such as
gt; the TransID number)
gt; =IF(left(B2,4)=quot;Transquot;,right(B2,5),B1)
gt; Next copy this formula all the way down the row for the entire data set.
gt; Select the entire row, right click, select Paste Special, select Values.
gt;
gt; You can now sort the entire data set how ever you want but use the new field
gt; to keep the three rows together.
gt;
gt; quot;Andrew Robertsquot; wrote:
gt;
gt; gt; I've come upon a problem with sorting that I don't know how to
gt; gt; tackle... I have entries in a workbook that I want to sort by a
gt; gt; transaction number, but each entry spans multiple rows. One quot;entryquot;
gt; gt; might look like this, for example:
gt; gt;
gt; gt; TransID PassengerName Ticket#
gt; gt; leg of travel: Departure Arrival
gt; gt; leg of travel: Departure Arrival
gt; gt;
gt; gt; I need to be able to sort by TransID or PassengerName while keeping the
gt; gt; quot;legs of travelquot; attached to the correct TransID/Ticket#.
gt; gt;
gt; gt; Any Suggestions?
gt; gt; Thanks,
gt; gt; Andrew
gt; gt;
gt; gt;

I see what you're trying to do with the formula... You're trying to
give all of the rows for a specific transaction a single ID number...
(Right?)

The formula almost works, but right now it only accounts for a single
line of quot;legs of travelquot; and many of these transactions have several.
Each trans. may span as little as one row (if there are no legs of
travel) or many (if there are lots of layovers in a flight).

Any tweaks? BTW, the data in the quot;TransIDquot; field is of the form:
quot;TR:######quot; (six digits following the quot;TR:quot;)

Thanks again,
AndrewThis formula should work no matter how many records come after TR:. It only
changes when it finds TR: again. If you are now sorting the dat but it is
still being broken up you may have to incorporate this new row row in your
sort.

any more than that is hard to say with out seeing the data set.

quot;Andrew Robertsquot; wrote:

gt; I see what you're trying to do with the formula... You're trying to
gt; give all of the rows for a specific transaction a single ID number...
gt; (Right?)
gt;
gt; The formula almost works, but right now it only accounts for a single
gt; line of quot;legs of travelquot; and many of these transactions have several.
gt; Each trans. may span as little as one row (if there are no legs of
gt; travel) or many (if there are lots of layovers in a flight).
gt;
gt; Any tweaks? BTW, the data in the quot;TransIDquot; field is of the form:
gt; quot;TR:######quot; (six digits following the quot;TR:quot;)
gt;
gt; Thanks again,
gt; Andrew
gt;
gt;

If I have the data:
TR:848595BRITISH AWYSSmith, John
legs of travel:NBO - Nairobi, KenyaLHR - London, U.K.
legs of travel:LHR - London, U.K.XXX

And I insert your code to the left, the output for that new column is
848595
TR:848595
legs of travel:

The formula refers to a concrete entry where you have it say quot;B1quot; so
it's changing at every row to refer to B2, B3, B4, etc... Any
suggestions?

Thanks for taking the time,
AndrewI am not quite sure I follow. What should happen is if the formula finds TR:
then it should return the number. If it does not find the TR: it should
return the same value as the record above it and so on until it finds TR:
again.
That is step 1. When that is right, select entire row and paste special,
values. then srting can begin. If this is what you did but are not getting
the right result, where did the rest of the records end up?quot;Andrew Robertsquot; wrote:

gt; If I have the data:
gt; TR:848595BRITISH AWYSSmith, John
gt; legs of travel:NBO - Nairobi, KenyaLHR - London, U.K.
gt; legs of travel:LHR - London, U.K.XXX
gt;
gt; And I insert your code to the left, the output for that new column is
gt; 848595
gt; TR:848595
gt; legs of travel:
gt;
gt; The formula refers to a concrete entry where you have it say quot;B1quot; so
gt; it's changing at every row to refer to B2, B3, B4, etc... Any
gt; suggestions?
gt;
gt; Thanks for taking the time,
gt; Andrew
gt;
gt;

A variation of this would be the following formula:

=IF(LEFT(B2,2)=quot;TR:quot;, RIGHT(B2,6),A1 0.1)

assuming that it starts in cell A2. This will get a new transaction
number when it changes, and will then add 0.1 onto it for each leg of
travel - you can always make this 0.01 is you expect more than 10 legs.
Copy this down, fix the values with Edit | Paste Special, then sort the
data set using this column.

Hope this helps.

Pete