Sorting, Merging, and Re-merging

A co-worker has a spreadsheet with five columns. One of the columns contains
a string of characters similar to the following:

ABC 12345 description of the project
BBC 30492 another project description
ZZY 01234 a third description of a project

She split that column into three columns so that she could sort on the
numbers. Now she wants to put the rows back together in the order she sorted
them in.

Is this possible? Is there a way to sort on those numbers without spliting
them into separate columns?

Thanks!

> Is this possible? Is there a way to sort on those numbers without
spliting
> them into separate columns?

The easiest way is to use a helper column of formulas to extract the values
on which to base the sort. For example, if
ABC 12345
were in cell A1, then the formula:

=VALUE(MID(A1,FIND(" ",A1)+1,LEN(A1)))

will extract the final number. Copy that down, then select everything and
sort on that column.

HTH,
Bernie
MS Excel MVP


"Abi" <Abi@discussions.microsoft.com> wrote in message
news:2C81F7C7-37D2-499C-B1FA-6306C2FB3567@microsoft.com...
> A co-worker has a spreadsheet with five columns. One of the columns
contains
> a string of characters similar to the following:
>
> ABC 12345 description of the project
> BBC 30492 another project description
> ZZY 01234 a third description of a project
>
> She split that column into three columns so that she could sort on the
> numbers. Now she wants to put the rows back together in the order she
sorted
> them in.
>
> Is this possible? Is there a way to sort on those numbers without
spliting
> them into separate columns?
>
> Thanks!

> Is this possible? Is there a way to sort on those numbers without
spliting
> them into separate columns?

The easiest way is to use a helper column of formulas to extract the values
on which to base the sort. For example, if
ABC 12345
were in cell A1, then the formula:

=VALUE(MID(A1,FIND(" ",A1)+1,LEN(A1)))

will extract the final number. Copy that down, then select everything and
sort on that column.

HTH,
Bernie
MS Excel MVP


"Abi" <Abi@discussions.microsoft.com> wrote in message
news:2C81F7C7-37D2-499C-B1FA-6306C2FB3567@microsoft.com...
> A co-worker has a spreadsheet with five columns. One of the columns
contains
> a string of characters similar to the following:
>
> ABC 12345 description of the project
> BBC 30492 another project description
> ZZY 01234 a third description of a project
>
> She split that column into three columns so that she could sort on the
> numbers. Now she wants to put the rows back together in the order she
sorted
> them in.
>
> Is this possible? Is there a way to sort on those numbers without
spliting
> them into separate columns?
>
> Thanks!

Just how did your co-worker actually split the column?

You can split a column using TTC (Text To Columns), where the original data
remains *untouched*, then sort on the split column(s), while including the
original column within the sort range, and then throw away the split data,
leaving the original as is *but* sorted.

Select the column, then
<Data> <Text To Columns>,
Click "Fixed Width", then <Next>,
Click on where you wish to create your separations, where you can drag the
break lines to wherever, then <Next>.
In this third window of the TTC wizard, there's a "Destination" box, with
the address defaulted to that of the original column.
Simply change that to any other column, and the original will remain as is,
while the "split" columns will start at the column you entered in this
destination box.

Then, simply select all the columns, including the original, and sort and
set your sort key to whatever column you wish.

After sorting, you can throw away the "split" data columns.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================





"Abi" <Abi@discussions.microsoft.com> wrote in message
news:2C81F7C7-37D2-499C-B1FA-6306C2FB3567@microsoft.com...
> A co-worker has a spreadsheet with five columns. One of the columns
contains
> a string of characters similar to the following:
>
> ABC 12345 description of the project
> BBC 30492 another project description
> ZZY 01234 a third description of a project
>
> She split that column into three columns so that she could sort on the
> numbers. Now she wants to put the rows back together in the order she
sorted
> them in.
>
> Is this possible? Is there a way to sort on those numbers without
spliting
> them into separate columns?
>
> Thanks!

Just how did your co-worker actually split the column?

You can split a column using TTC (Text To Columns), where the original data
remains *untouched*, then sort on the split column(s), while including the
original column within the sort range, and then throw away the split data,
leaving the original as is *but* sorted.

Select the column, then
<Data> <Text To Columns>,
Click "Fixed Width", then <Next>,
Click on where you wish to create your separations, where you can drag the
break lines to wherever, then <Next>.
In this third window of the TTC wizard, there's a "Destination" box, with
the address defaulted to that of the original column.
Simply change that to any other column, and the original will remain as is,
while the "split" columns will start at the column you entered in this
destination box.

Then, simply select all the columns, including the original, and sort and
set your sort key to whatever column you wish.

After sorting, you can throw away the "split" data columns.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================





"Abi" <Abi@discussions.microsoft.com> wrote in message
news:2C81F7C7-37D2-499C-B1FA-6306C2FB3567@microsoft.com...
> A co-worker has a spreadsheet with five columns. One of the columns
contains
> a string of characters similar to the following:
>
> ABC 12345 description of the project
> BBC 30492 another project description
> ZZY 01234 a third description of a project
>
> She split that column into three columns so that she could sort on the
> numbers. Now she wants to put the rows back together in the order she
sorted
> them in.
>
> Is this possible? Is there a way to sort on those numbers without
spliting
> them into separate columns?
>
> Thanks!

> Is this possible? Is there a way to sort on those numbers without
spliting
> them into separate columns?

The easiest way is to use a helper column of formulas to extract the values
on which to base the sort. For example, if
ABC 12345
were in cell A1, then the formula:

=VALUE(MID(A1,FIND(" ",A1)+1,LEN(A1)))

will extract the final number. Copy that down, then select everything and
sort on that column.

HTH,
Bernie
MS Excel MVP


"Abi" <Abi@discussions.microsoft.com> wrote in message
news:2C81F7C7-37D2-499C-B1FA-6306C2FB3567@microsoft.com...
> A co-worker has a spreadsheet with five columns. One of the columns
contains
> a string of characters similar to the following:
>
> ABC 12345 description of the project
> BBC 30492 another project description
> ZZY 01234 a third description of a project
>
> She split that column into three columns so that she could sort on the
> numbers. Now she wants to put the rows back together in the order she
sorted
> them in.
>
> Is this possible? Is there a way to sort on those numbers without
spliting
> them into separate columns?
>
> Thanks!

Just how did your co-worker actually split the column?

You can split a column using TTC (Text To Columns), where the original data
remains *untouched*, then sort on the split column(s), while including the
original column within the sort range, and then throw away the split data,
leaving the original as is *but* sorted.

Select the column, then
<Data> <Text To Columns>,
Click "Fixed Width", then <Next>,
Click on where you wish to create your separations, where you can drag the
break lines to wherever, then <Next>.
In this third window of the TTC wizard, there's a "Destination" box, with
the address defaulted to that of the original column.
Simply change that to any other column, and the original will remain as is,
while the "split" columns will start at the column you entered in this
destination box.

Then, simply select all the columns, including the original, and sort and
set your sort key to whatever column you wish.

After sorting, you can throw away the "split" data columns.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================





"Abi" <Abi@discussions.microsoft.com> wrote in message
news:2C81F7C7-37D2-499C-B1FA-6306C2FB3567@microsoft.com...
> A co-worker has a spreadsheet with five columns. One of the columns
contains
> a string of characters similar to the following:
>
> ABC 12345 description of the project
> BBC 30492 another project description
> ZZY 01234 a third description of a project
>
> She split that column into three columns so that she could sort on the
> numbers. Now she wants to put the rows back together in the order she
sorted
> them in.
>
> Is this possible? Is there a way to sort on those numbers without
spliting
> them into separate columns?
>
> Thanks!

> Is this possible? Is there a way to sort on those numbers without
spliting
> them into separate columns?

The easiest way is to use a helper column of formulas to extract the values
on which to base the sort. For example, if
ABC 12345
were in cell A1, then the formula:

=VALUE(MID(A1,FIND(" ",A1)+1,LEN(A1)))

will extract the final number. Copy that down, then select everything and
sort on that column.

HTH,
Bernie
MS Excel MVP


"Abi" <Abi@discussions.microsoft.com> wrote in message
news:2C81F7C7-37D2-499C-B1FA-6306C2FB3567@microsoft.com...
> A co-worker has a spreadsheet with five columns. One of the columns
contains
> a string of characters similar to the following:
>
> ABC 12345 description of the project
> BBC 30492 another project description
> ZZY 01234 a third description of a project
>
> She split that column into three columns so that she could sort on the
> numbers. Now she wants to put the rows back together in the order she
sorted
> them in.
>
> Is this possible? Is there a way to sort on those numbers without
spliting
> them into separate columns?
>
> Thanks!

Just how did your co-worker actually split the column?

You can split a column using TTC (Text To Columns), where the original data
remains *untouched*, then sort on the split column(s), while including the
original column within the sort range, and then throw away the split data,
leaving the original as is *but* sorted.

Select the column, then
<Data> <Text To Columns>,
Click "Fixed Width", then <Next>,
Click on where you wish to create your separations, where you can drag the
break lines to wherever, then <Next>.
In this third window of the TTC wizard, there's a "Destination" box, with
the address defaulted to that of the original column.
Simply change that to any other column, and the original will remain as is,
while the "split" columns will start at the column you entered in this
destination box.

Then, simply select all the columns, including the original, and sort and
set your sort key to whatever column you wish.

After sorting, you can throw away the "split" data columns.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================





"Abi" <Abi@discussions.microsoft.com> wrote in message
news:2C81F7C7-37D2-499C-B1FA-6306C2FB3567@microsoft.com...
> A co-worker has a spreadsheet with five columns. One of the columns
contains
> a string of characters similar to the following:
>
> ABC 12345 description of the project
> BBC 30492 another project description
> ZZY 01234 a third description of a project
>
> She split that column into three columns so that she could sort on the
> numbers. Now she wants to put the rows back together in the order she
sorted
> them in.
>
> Is this possible? Is there a way to sort on those numbers without
spliting
> them into separate columns?
>
> Thanks!