Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting
I am sorting by the following
=ISNA(MATCH(D2,$P$2:$P$2500,FALSE)) is the formula that is in cell y2. =ISNA(MATCH(P2,$D$2:$D$2500,FALSE)) is the formula that is in cell z2. What I would like to know is there anyway I can all the "TRUE" values to be listed first and the "FALSE" values to be listed after those? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting
"OfficeNDN" skrev i en meddelelse
... I am sorting by the following =ISNA(MATCH(D2,$P$2:$P$2500,FALSE)) is the formula that is in cell y2. =ISNA(MATCH(P2,$D$2:$D$2500,FALSE)) is the formula that is in cell z2. What I would like to know is there anyway I can all the "TRUE" values to be listed first and the "FALSE" values to be listed after those? Here's one way to do it, but your ranges are so big, that the practical use of the formula is dubious. However the formula first lists the group of all values from D2:D2500, which are not in P2:P2500 and then the group of all values which are. In Y2: = INDEX($D$2:$D$2500, MATCH(SMALL(IF($D$2:$D$2500="",FALSE, IF(COUNTIF($P$2:$P$2500,$D$2:$D$2500),ROW($D$2:$D$ 2500)*100000, ROW($D$2:$D$2500))),ROW()-ROW($Y$2)+1),IF($D$2:$D$2500="",FALSE, IF(COUNTIF($P$2:$P$2500,$D$2:$D$2500)*($D$2:$D$250 0<""), ROW($D$2:$D$2500)*100000,ROW($D$2:$D$2500))),0)) If you start in another cell e.g. H5, the expression ROW()-ROW($Y$2) must be ROW()-ROW($H$5). The formula must be entered with <Shift<Ctrl<Enter, also if edited later. To get a visual indication of where one group ends and the other group starts, you can use this formula in Z2: =IF(COUNTIF($P$2:$P$2500,Y2)0,"XX","") Select Y2:Z2 and copy down. Similar for your other formula. -- Best regards Leo Heuser Followup to newsgroup only please. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Sorting Sorting | Excel Discussion (Misc queries) | |||
Sorting Data in ascending order | Excel Discussion (Misc queries) | |||
sorting non contiguous ranges | Excel Discussion (Misc queries) | |||
Advice sought: Multiple column sorting | Excel Worksheet Functions | |||
Sorting 1, 1A, 2, 2A, 3, 4, 4A, 4B . . . | Excel Discussion (Misc queries) |