Advanced Sorting

G

Guest

Guest
Hello
I have posted a question about sorting
I am wondering is it possible to sort a data with the following format (in excel)
Date..Time..Color..Freq
.2.....3......B.....C
..............A.....D
..............C.....P
Date..Time..Color..Freq
.3.....4......D.....A
..............D.....Q
..............O.....R

...means blank cells

so I can sort Date or Time and move the corrisponding data all together without changing the original format

Thanks in advance
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
You could fill-in the blanks with the value from the cell above and then use conditional formatting to hide duplicate date and time values. So, using your data in cells A1:D7 after the removal of the duplicate column headers...

1. Select cells A1:B7 and choose the Edit | Go To... | Special... Blanks menu command.

2. Without altering the selection Control+Enter the formula, =A2, into cell A3.

3. Select columns A:B and press [ Tab ] until cell A2 is the active cell.

4. Choose the Format | Conditional Formatting ... menu command.

5. Setup a "Formula Is" condition using the formula, =A1=A2, and format the Font color to match the cell background color (white, in my case).

Now you should be able to sort on the values in columns A:B and still preserve the appearance of your data.
This message was edited by Mark W. on 2002-03-01 07:04
 
Upvote 0
Hi Mark
I am not sure if I get your solution
.A.....B.....C......D
1Date..Time..Color..Freq
2.2.....3......B.....C
3..............A.....D
4..............C.....P
5Date..Time..Color..Freq
6.3.....4......D.....A
7..............D.....Q
8..............O.....R

...means blank cells

So right now I have columns A~D rows 1~8


1. Select cells A1:B7 and choose the Edit | Go To... | Special... Blanks menu command.

-->After I done this step, I selected all the blank cells in columns A~B(I selected A1:B8 becasue I have 8 rows)

2. Without altering the selection Control+Enter the formula, =A2, into cell A3.

-->Now, I copied the value 2(from A2)to A3

3. Select columns A:B and press [ Tab ] until cell A2 is the active cell.

-->Then I selected Columns A:B with A2 actived

4. Choose the Format | Conditional Formatting ... menu command.

5. Setup a "Formula Is" condition using the formula, =A1=A2, and format the Font color to match the cell background color (white in my case).

-->Then I setup a "Formula Is" and type in =A2=A3(because A1 is my header). And click Ok
But nothing happens

Now you should be able to sort on the values in columns A:B and still preserve the appearance of your data.

-->Then I select all the data and press sort button. But the 2nd header(Date, Time..)can't move with the sorted data, and the appearance of the corresponding data are also sorted(can't preserve the appearance)

So, is there something I did incorrectly?
 
Upvote 0
Sorry, I forgot Step 2.5...

...So, using your data in cells A1:D7
after the removal of the duplicate column headers...

1. Select cells A1:B7 and choose the Edit | Go To... | Special... Blanks menu command.

2. Without altering the selection Control+Enter the formula, =A2, into cell A3.

2.5 Select columns A:B and perform Copy/Paste Special... Values.

3. Select columns A:B and press [ Tab ] until cell A2 is the active cell.

4. Choose the Format | Conditional Formatting ... menu command.

5. Setup a "Formula Is" condition using the formula, =A1=A2, and format the Font color to match the cell background color (white, in my case).

Now you should be able to sort on the values in columns A:B and still preserve the appearance of your data.

Follow these instructions exactly as listed... Don't alter any of these formulas and don't fail to remove the duplicate header labels.
This message was edited by Mark W. on 2002-03-01 08:11
 
Upvote 0
Hi Mark
In the beginning you said , using your data in cells A1:D7 after the removal of the duplicate column headers...
Do you mean I have to delete all the header before I perform the steps 1~5?

Also, when I do step 2.5, it gives me an error message (That command cannot be used on multiple selection) after I press OK

I am not sure what does step2.5 do. It just copy the ColumnA and B and paste back to its original position
 
Upvote 0
Hi Mark
I am just wondering when I successfuly sort the data. Does it only sort the columns A and B? I am trying to do is that to sort either column A or B and move the other columns together. For example

Date Time something
33...44.....A
............B
............C
Date Time Something
22...33.....C
............D
............E
After I perform a Time sort, I can get somthing like

Date Time Something
22...33......C
.............D
.............E
Date Time Something
33....44.....A
.............B
.............C

So,22 33 CDE is one spec, 33 44 ABC is another spec

Thank you
 
Upvote 0
You should remove all but the 1st row of headers. Otherwise, they would be affected by the sort. You shouldn't need them any way.

If you truly selected columns A:B at step 2.5 you wouldn't have a multiple selection. Click on the column A heading and drag your selection to column B.

Step 2.5 preserves the values calculated at Step 2 so that the sort won't affect them.
 
Upvote 0
When I sort should I also select column C and D? I am trying to have the data in C and D move with the sorted value in column A and B.(Format shown on a couple of message box before)
After I finish steps 1~5. Then I select all the data form columns A~D and press sort button. The data are sort separately by each column and not just by one column. Also, the space in columns A and B are all move to the buttom(no preserved space) Why???
 
Upvote 0
On 2002-03-01 09:13, Anonymous wrote:
When I sort should I also select column C and D? I am trying to have the data in C and D move with the sorted value in column A and B.(Format shown on a couple of message box before)
After I finish steps 1~5. Then I select all the data form columns A~D and press sort button. The data are sort separately by each column and not just by one column. Also, the space in columns A and B are all move to the buttom(no preserved space) Why???

After performing the steps above there won't be any more empty cells in columns A and B -- only cells that appear empty, and ordering of those cells relative to one another should not be affected by the sort.

If you select any one cell in the range A1:D7 the sort will sort all columns in that range.
 
Upvote 0
Hello Mark
I am trying to perform a sorting like the following:
Date Time something
33...44.....A
............B
............C
Date Time Something
22...33.....C
............D
............E
After I perform a Time sort, I can get somthing like

Date Time Something
22...33......C
.............D
.............E
Date Time Something
33....44.....A
.............B
.............C

Is it possible to keep all the blank cells(space) after sorting, and the data under column Something can match the sorted Date and Time?
I can't sort the data under Somthing column, but I must move those data to match their corresponding Date and Time data.

I tried the 5 steps(I hope I did them correctly) but I still can't achieve the sort above

What should I do???
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top