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 "entry"
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
"legs of travel" attached to the correct TransID/Ticket#.

Any Suggestions?
Thanks,
Andrew

Try this:

Select the whole data list, including column headings.
DataSort
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-Pro


"Andrew Roberts" wrote:

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 "entry"
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
"legs of travel" attached to the correct TransID/Ticket#.

Any Suggestions?
Thanks,
Andrew

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

Incidentally, "legs of travel" shows up in the same column as "TransID"
at the moment.

Thanks again,
Andrew

Insert 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)="Trans",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.

"Andrew Roberts" wrote:

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 "entry"
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
"legs of travel" attached to the correct TransID/Ticket#.

Any Suggestions?
Thanks,
Andrew

Ooops formula should be as follows:

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

=IF(left(B2,4)="Trans",B2,B1)
This would work if you didn't care if the words "TransID" 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.



"WCoaster" wrote:

Insert 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)="Trans",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.

"Andrew Roberts" wrote:

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 "entry"
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
"legs of travel" attached to the correct TransID/Ticket#.

Any Suggestions?
Thanks,
Andrew

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 "legs of travel" 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 "TransID" field is of the form:
"TR:######" (six digits following the "TR:")

Thanks again,
Andrew

This 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.

"Andrew Roberts" wrote:

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 "legs of travel" 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 "TransID" field is of the form:
"TR:######" (six digits following the "TR:")

Thanks again,
Andrew

If I have the data:
TR:848595 BRITISH AWYS Smith, John
legs of travel: NBO - Nairobi, Kenya LHR - 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 "B1" so
it's changing at every row to refer to B2, B3, B4, etc... Any
suggestions?

Thanks for taking the time,
Andrew

I 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?


"Andrew Roberts" wrote:

If I have the data:
TR:848595 BRITISH AWYS Smith, John
legs of travel: NBO - Nairobi, Kenya LHR - 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 "B1" so
it's changing at every row to refer to B2, B3, B4, etc... Any
suggestions?

Thanks for taking the time,
Andrew

A variation of this would be the following formula:

=IF(LEFT(B2,2)="TR:", 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

That one did the trick... Thanks for all the input - all of you... The
final formula used, btw, was:
=IF(LEFT(B2,3)="TR:", RIGHT(B2,6),A1+0.1)

(Just a typo on the LEFT command)

Thanks again, all!
Andrew

Hi Andrew,

thanks for the feedback - sorry about the typo, but glad you managed to
get it working.

Pete

Dear Sir.
I am looking to develop progrmming in MSEXCEL and VBA if u have got
any souce code or study material plz send to this id

yours
sudhir