Sorting by use of formulas...

[Jay Gustafson]

Hello,

I have a spreadsheet with 3 columns.

Column 1:

Contains about 1600 items, about 90% are duplicates.

Column 2:

Contains a formula to extract the unique items. http://www.cpearson.com/excel/duplicat.htm#ExtractingUnique

Column 3:

Contains an array formula to eliminate blank cells from the list that was made in column 2. http://www.cpearson.com/excel/noblanks.htm


My problem is this. I would like that list to be automatically sorted in alphabetical order. Now those results are made by an array formula so I can't sort the list by hitting the sort button. But is it possible to have some other type of formula that will grab those items and sort them automatically for me?

I hope that made sense.

Jay

 

[Tod]

You can do a pastespecial>values and sort that list.
Hightlight the list you want to sort. Right click
somewhere in the highlighted area and choose Copy.

If you want to copy over your formulas with just the
values, then right click in the highlighted area again and
choose PasteSpecial. Choose Values and then OK.

If you want to preserve your formulas and do the sorting
elsewhere, highlight the range, right click, copy, then
right click in the top cell where you want the list to go,
THEN PasteSpecial, choose Values, click OK.

Either way, now you can sort the list.

tod

-----Original Message-----
Hello,

I have a spreadsheet with 3 columns.

Column 1:

Contains about 1600 items, about 90% are duplicates.

Column 2:

Contains a formula to extract the unique items. http://www.cpearson.com/excel/duplicat.htm#ExtractingUnique

Column 3:

Contains an array formula to eliminate blank cells from

the list that was made in column 2.
http://www.cpearson.com/excel/noblanks.htm

My problem is this. I would like that list to be

automatically sorted in alphabetical order. Now those
results are made by an array formula so I can't sort the
list by hitting the sort button. But is it possible to
have some other type of formula that will grab those items
and sort them automatically for me?

 

[Jay Gustafson]

Tod,

Thanks for the feedback. Origionally, that process is what I wanted to avoid. I didn't know if it was possible to sort by using formulas. I just figured I'd save myself the step in doing it. Oh well, I'll stick to manually sorting it.

Thanks again,
Jay Gustafson

 

[RagDyer]

Here's an array formula that Harlan posted a while ago for sorting *either*
all text, or all numbers.

=INDEX($D$1:$D$10,MATCH(SMALL(COUNTIF($D$1:$D$10,"<"&$D$1:$D$10),ROW()-ROW($
E$1)+1),COUNTIF($D$1:$D$10,"<"&$D$1:$D$10),0))

Enter with CSE (<Ctrl> <Shift> <Enter>)
Drag down to copy as needed,
OR,
Double click on the "fill handle", to copy down the column as far as there
is data in the adjacent column.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Tod,

Thanks for the feedback. Origionally, that process is what I wanted to
avoid. I didn't know if it was possible to sort by using formulas. I just
figured I'd save myself the step in doing it. Oh well, I'll stick to
manually sorting it.

Thanks again,
Jay Gustafson