Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Advanced Sorting

  1. #1
    Guest

    Default

    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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    Guest

    Default

    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?


  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    Guest

    Default

    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




  6. #6
    Guest

    Default

    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

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  8. #8
    Guest

    Default

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

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  10. #10
    Guest

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •