I have a list I need to sort. Column A has rows with indented text that
needs to stay with above non-indented rows. How do I sort the whole column
to keep indented text with non-indented text row above? In the below
example, my end result should be Cindy with her group of Pat & Amy should
come before Jack, but Pat and Amy remain indented. Is there a way to do this
by keeping Amy & Pat indented in the same column A?

Example:

A
1 Jack
2 Cindy
3 Pat
4 Amy

One way is as follows;
In B1 enter
=A1
In B2 enter
=IF(LEFT(A2,1)=" ",B1,A2) and copy down

Sort the data set on B then A...

"amezera" wrote:

I have a list I need to sort. Column A has rows with indented text that
needs to stay with above non-indented rows. How do I sort the whole column
to keep indented text with non-indented text row above? In the below
example, my end result should be Cindy with her group of Pat & Amy should
come before Jack, but Pat and Amy remain indented. Is there a way to do this
by keeping Amy & Pat indented in the same column A?

Example:

A
1 Jack
2 Cindy
3 Pat
4 Amy

The OP said the other names were indented, no matter how far indented, the
left character isn't a blank space. Now, if the OP meant there were leading
spaces.....then it would work.
--
John C


"Sheeloo" wrote:

One way is as follows;
In B1 enter
=A1
In B2 enter
=IF(LEFT(A2,1)=" ",B1,A2) and copy down

Sort the data set on B then A...

"amezera" wrote:

I have a list I need to sort. Column A has rows with indented text that
needs to stay with above non-indented rows. How do I sort the whole column
to keep indented text with non-indented text row above? In the below
example, my end result should be Cindy with her group of Pat & Amy should
come before Jack, but Pat and Amy remain indented. Is there a way to do this
by keeping Amy & Pat indented in the same column A?

Example:

A
1 Jack
2 Cindy
3 Pat
4 Amy

I'm not sure what a leading space is....

Also, as for the first responder, I have other data in columns B,C,D.... so
I can't put a formula. (Not sure if I am allowed to alter the spreadsheet to
insert a column to fascilitate a sorting formula...as I am not the author of
it.)

"John C" wrote:

The OP said the other names were indented, no matter how far indented, the
left character isn't a blank space. Now, if the OP meant there were leading
spaces.....then it would work.
--
John C


"Sheeloo" wrote:

One way is as follows;
In B1 enter
=A1
In B2 enter
=IF(LEFT(A2,1)=" ",B1,A2) and copy down

Sort the data set on B then A...

"amezera" wrote:

I have a list I need to sort. Column A has rows with indented text that
needs to stay with above non-indented rows. How do I sort the whole column
to keep indented text with non-indented text row above? In the below
example, my end result should be Cindy with her group of Pat & Amy should
come before Jack, but Pat and Amy remain indented. Is there a way to do this
by keeping Amy & Pat indented in the same column A?

Example:

A
1 Jack
2 Cindy
3 Pat
4 Amy

A leading space is where when the data is entered, it is entered as
<spacebar<spacebarPat

Whereas indentation is utilized by clicking on the Format menu, Cells
selection, Alignment tab, and then checking if the Indent box is 0. If it is
not indented, then it was most likely entered with leading space(s).

As far as the solution Sheeloo gave, it will work with leading spaces.
click on cell B1
Insert-- Column
Follow Sheeloo's instructions
After sorting, highlight column B and delete column B

--
John C


"amezera" wrote:

I'm not sure what a leading space is....

Also, as for the first responder, I have other data in columns B,C,D.... so
I can't put a formula. (Not sure if I am allowed to alter the spreadsheet to
insert a column to fascilitate a sorting formula...as I am not the author of
it.)

"John C" wrote:

The OP said the other names were indented, no matter how far indented, the
left character isn't a blank space. Now, if the OP meant there were leading
spaces.....then it would work.
--
John C


"Sheeloo" wrote:

One way is as follows;
In B1 enter
=A1
In B2 enter
=IF(LEFT(A2,1)=" ",B1,A2) and copy down

Sort the data set on B then A...

"amezera" wrote:

I have a list I need to sort. Column A has rows with indented text that
needs to stay with above non-indented rows. How do I sort the whole column
to keep indented text with non-indented text row above? In the below
example, my end result should be Cindy with her group of Pat & Amy should
come before Jack, but Pat and Amy remain indented. Is there a way to do this
by keeping Amy & Pat indented in the same column A?

Example:

A
1 Jack
2 Cindy
3 Pat
4 Amy

Ah, then I do have leading spaces. I will try this formula and see how it
works.

"John C" wrote:

A leading space is where when the data is entered, it is entered as
<spacebar<spacebarPat

Whereas indentation is utilized by clicking on the Format menu, Cells
selection, Alignment tab, and then checking if the Indent box is 0. If it is
not indented, then it was most likely entered with leading space(s).

As far as the solution Sheeloo gave, it will work with leading spaces.
click on cell B1
Insert-- Column
Follow Sheeloo's instructions
After sorting, highlight column B and delete column B

--
John C


"amezera" wrote:

I'm not sure what a leading space is....

Also, as for the first responder, I have other data in columns B,C,D.... so
I can't put a formula. (Not sure if I am allowed to alter the spreadsheet to
insert a column to fascilitate a sorting formula...as I am not the author of
it.)

"John C" wrote:

The OP said the other names were indented, no matter how far indented, the
left character isn't a blank space. Now, if the OP meant there were leading
spaces.....then it would work.
--
John C


"Sheeloo" wrote:

One way is as follows;
In B1 enter
=A1
In B2 enter
=IF(LEFT(A2,1)=" ",B1,A2) and copy down

Sort the data set on B then A...

"amezera" wrote:

I have a list I need to sort. Column A has rows with indented text that
needs to stay with above non-indented rows. How do I sort the whole column
to keep indented text with non-indented text row above? In the below
example, my end result should be Cindy with her group of Pat & Amy should
come before Jack, but Pat and Amy remain indented. Is there a way to do this
by keeping Amy & Pat indented in the same column A?

Example:

A
1 Jack
2 Cindy
3 Pat
4 Amy

I tried Sheeloo's formula and it sort of worked, but the data was no longer
accurate. When I sorted in the newly created column B, the data didn't sort,
but the formulas moved around; some of the names were lost and other names
were duplicated; and the data that follwed in columns C,D,E, etc were sorted
to the wrong people. Perhaps this is a lost cause?


"amezera" wrote:

I have a list I need to sort. Column A has rows with indented text that
needs to stay with above non-indented rows. How do I sort the whole column
to keep indented text with non-indented text row above? In the below
example, my end result should be Cindy with her group of Pat & Amy should
come before Jack, but Pat and Amy remain indented. Is there a way to do this
by keeping Amy & Pat indented in the same column A?

Example:

A
1 Jack
2 Cindy
3 Pat
4 Amy

After inserting the helper column, did you select the entire area to be
sorted? Or did you select just column's A & B. You need to select the entire
area, and then sort on column B first, then column A second.

Hope this helps.
--
John C


"amezera" wrote:

I tried Sheeloo's formula and it sort of worked, but the data was no longer
accurate. When I sorted in the newly created column B, the data didn't sort,
but the formulas moved around; some of the names were lost and other names
were duplicated; and the data that follwed in columns C,D,E, etc were sorted
to the wrong people. Perhaps this is a lost cause?


"amezera" wrote:

I have a list I need to sort. Column A has rows with indented text that
needs to stay with above non-indented rows. How do I sort the whole column
to keep indented text with non-indented text row above? In the below
example, my end result should be Cindy with her group of Pat & Amy should
come before Jack, but Pat and Amy remain indented. Is there a way to do this
by keeping Amy & Pat indented in the same column A?

Example:

A
1 Jack
2 Cindy
3 Pat
4 Amy

Ok, we're almost there. This time I did sort the entire area, sorted by B
then A and it kept all the data together as it should, however Pat and Amy
did not stay with Cindy. The column was sorted as if it was a normal sort,
so everyone was in alphabetical order, and Pat and Amy were indented.

"John C" wrote:

After inserting the helper column, did you select the entire area to be
sorted? Or did you select just column's A & B. You need to select the entire
area, and then sort on column B first, then column A second.

Hope this helps.
--
John C


"amezera" wrote:

I tried Sheeloo's formula and it sort of worked, but the data was no longer
accurate. When I sorted in the newly created column B, the data didn't sort,
but the formulas moved around; some of the names were lost and other names
were duplicated; and the data that follwed in columns C,D,E, etc were sorted
to the wrong people. Perhaps this is a lost cause?


"amezera" wrote:

I have a list I need to sort. Column A has rows with indented text that
needs to stay with above non-indented rows. How do I sort the whole column
to keep indented text with non-indented text row above? In the below
example, my end result should be Cindy with her group of Pat & Amy should
come before Jack, but Pat and Amy remain indented. Is there a way to do this
by keeping Amy & Pat indented in the same column A?

Example:

A
1 Jack
2 Cindy
3 Pat
4 Amy

Okay, I got it figured out for you. I hadn't really tested Sheeloo's formula,
and yeah, it isn't quite there, so here you go.

1 Insert 2 columns to the right of your name list, i.e: 2 columns are now B
& C
2 B1: =A1
3 C1: =A1
4 B2: =IF(LEFT(A2,1)=" ",B1,A2)
5 C2: =IF(LEFT(A2,1)=" ",TRIM(A2),"")
6 Select B2:C2, and Edit--Copy--Down as far as needed
7 Select B1:C... whatever is the end of your data (i.e.: select B1:C100)
8 Copy this selection (Alt+C), then Paste--Special--Value (ALT, E, S, V)
9 Select entire data set, columns A thru M or whatever
10 Data--Sort, first by column B, ascending, then by column C ascending
(use the first 2 conditions listed, do not do separate sorts).
11 Delete columns B & C
12 Do the dance of joy.

Hope this helps.
--
John C


"amezera" wrote:

Ok, we're almost there. This time I did sort the entire area, sorted by B
then A and it kept all the data together as it should, however Pat and Amy
did not stay with Cindy. The column was sorted as if it was a normal sort,
so everyone was in alphabetical order, and Pat and Amy were indented.

"John C" wrote:

After inserting the helper column, did you select the entire area to be
sorted? Or did you select just column's A & B. You need to select the entire
area, and then sort on column B first, then column A second.

Hope this helps.
--
John C


"amezera" wrote:

I tried Sheeloo's formula and it sort of worked, but the data was no longer
accurate. When I sorted in the newly created column B, the data didn't sort,
but the formulas moved around; some of the names were lost and other names
were duplicated; and the data that follwed in columns C,D,E, etc were sorted
to the wrong people. Perhaps this is a lost cause?


"amezera" wrote:

I have a list I need to sort. Column A has rows with indented text that
needs to stay with above non-indented rows. How do I sort the whole column
to keep indented text with non-indented text row above? In the below
example, my end result should be Cindy with her group of Pat & Amy should
come before Jack, but Pat and Amy remain indented. Is there a way to do this
by keeping Amy & Pat indented in the same column A?

Example:

A
1 Jack
2 Cindy
3 Pat
4 Amy

Small correction:
C1: =""
--
John C


"amezera" wrote:

Ok, we're almost there. This time I did sort the entire area, sorted by B
then A and it kept all the data together as it should, however Pat and Amy
did not stay with Cindy. The column was sorted as if it was a normal sort,
so everyone was in alphabetical order, and Pat and Amy were indented.

"John C" wrote:

After inserting the helper column, did you select the entire area to be
sorted? Or did you select just column's A & B. You need to select the entire
area, and then sort on column B first, then column A second.

Hope this helps.
--
John C


"amezera" wrote:

I tried Sheeloo's formula and it sort of worked, but the data was no longer
accurate. When I sorted in the newly created column B, the data didn't sort,
but the formulas moved around; some of the names were lost and other names
were duplicated; and the data that follwed in columns C,D,E, etc were sorted
to the wrong people. Perhaps this is a lost cause?


"amezera" wrote:

I have a list I need to sort. Column A has rows with indented text that
needs to stay with above non-indented rows. How do I sort the whole column
to keep indented text with non-indented text row above? In the below
example, my end result should be Cindy with her group of Pat & Amy should
come before Jack, but Pat and Amy remain indented. Is there a way to do this
by keeping Amy & Pat indented in the same column A?

Example:

A
1 Jack
2 Cindy
3 Pat
4 Amy

<happy dance <happy dance!!!! Thank you, you are a genius! It worked
perfectly! I really appreciate your patience with me! You have made this
excel dork's day.

"John C" wrote:

Small correction:
C1: =""
--
John C


"amezera" wrote:

Ok, we're almost there. This time I did sort the entire area, sorted by B
then A and it kept all the data together as it should, however Pat and Amy
did not stay with Cindy. The column was sorted as if it was a normal sort,
so everyone was in alphabetical order, and Pat and Amy were indented.

"John C" wrote:

After inserting the helper column, did you select the entire area to be
sorted? Or did you select just column's A & B. You need to select the entire
area, and then sort on column B first, then column A second.

Hope this helps.
--
John C


"amezera" wrote:

I tried Sheeloo's formula and it sort of worked, but the data was no longer
accurate. When I sorted in the newly created column B, the data didn't sort,
but the formulas moved around; some of the names were lost and other names
were duplicated; and the data that follwed in columns C,D,E, etc were sorted
to the wrong people. Perhaps this is a lost cause?


"amezera" wrote:

I have a list I need to sort. Column A has rows with indented text that
needs to stay with above non-indented rows. How do I sort the whole column
to keep indented text with non-indented text row above? In the below
example, my end result should be Cindy with her group of Pat & Amy should
come before Jack, but Pat and Amy remain indented. Is there a way to do this
by keeping Amy & Pat indented in the same column A?

Example:

A
1 Jack
2 Cindy
3 Pat
4 Amy

You are welcome, and thanks for the feedback.
--
John C


"amezera" wrote:

<happy dance <happy dance!!!! Thank you, you are a genius! It worked
perfectly! I really appreciate your patience with me! You have made this
excel dork's day.

"John C" wrote:

Small correction:
C1: =""
--
John C


"amezera" wrote:

Ok, we're almost there. This time I did sort the entire area, sorted by B
then A and it kept all the data together as it should, however Pat and Amy
did not stay with Cindy. The column was sorted as if it was a normal sort,
so everyone was in alphabetical order, and Pat and Amy were indented.

"John C" wrote:

After inserting the helper column, did you select the entire area to be
sorted? Or did you select just column's A & B. You need to select the entire
area, and then sort on column B first, then column A second.

Hope this helps.
--
John C


"amezera" wrote:

I tried Sheeloo's formula and it sort of worked, but the data was no longer
accurate. When I sorted in the newly created column B, the data didn't sort,
but the formulas moved around; some of the names were lost and other names
were duplicated; and the data that follwed in columns C,D,E, etc were sorted
to the wrong people. Perhaps this is a lost cause?


"amezera" wrote:

I have a list I need to sort. Column A has rows with indented text that
needs to stay with above non-indented rows. How do I sort the whole column
to keep indented text with non-indented text row above? In the below
example, my end result should be Cindy with her group of Pat & Amy should
come before Jack, but Pat and Amy remain indented. Is there a way to do this
by keeping Amy & Pat indented in the same column A?

Example:

A
1 Jack
2 Cindy
3 Pat
4 Amy