Advanced Sorting

[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

 

[Mark W.]

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

 

[Guest]

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?

 

[Mark W.]

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

 

[Guest]

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

 

[Guest]

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

 

[Mark W.]

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.

 

[Guest]

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

 

[Mark W.]

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.

 

[Guest]

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