|
Sorting
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 |
Sorting
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 |
Sorting
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 |
Sorting
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 |
Sorting
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 |
Sorting
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 |
Sorting
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 |
Sorting
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 |
Sorting
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 |
Sorting
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 |
Sorting
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 |
Sorting
<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 |
Sorting
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 |
| All times are GMT +1. The time now is 01:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com