Sorting

[Guest]

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?

 

[Leo Heuser]

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 D22500,
which are not in P22500 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$2500<>""),
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.