Tricky problem - auto-sorting without VBA

[philkime]

I have two sheets.
Sheet 1 has columns A,B,C,D,E.
Sheet 2 needs to have copies of Sheet 1's columns A,B,D,E
Sheet 2 should be sorted by column E (all of the columns sorted on E,
like an extended range sort)
Sheet 2 should auto-update as Sheet 1 is changed

Now, I know I can do this in VBA but I can't use VBA (has to work on
Mac Excel 2008 too). It needs to be done in XLM and/or standard macros
only. Can't be manual at all (no clicking buttons, no manual sorting).

Any ideas? Such a simple task but seems quite hard without VBA.

 

[Gary''s Student]

1. are your data numbers or text?
2. are there duplicates in your data?

 

[philkime]

1. are your data numbers or text?
2. are there duplicates in your data?

Each column is only numbers or text. A and E are numeric, B and D
text.
There are duplicates, particularly in column E which I need to sort
on.

PK

 

[Bernd P]

http://www.sulprobil.com/html/sorting.html

Regards,
Bernd

 

[Gary''s Student]

Too bad.

Without duplicates you could use =SMALL(E:E,ROW()) to list the values and
then some type of lookup for the other columns.

With duplicates, I would use VBA.

 

[Max]

Try this relatively easy non-array formulas set-up,
which includes tiebreakers to take care of the possibilities
of ties (or multiple ties) within the numeric values in col E.

Source data in Sheet1, cols A to E, from row2 down,
with key (sort) col = col E (numbers)

For an Auto-Ascending Sort
In Sheet2,
In A2: =IF(Sheet1!E2="","",Sheet1!E2+ROW()/10^10)
Leave A1 empty

In B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(Sheet1!A:A,MATCH(SMALL($A:$A,ROWS($1:1)),$A:$A,0)))
Copy B2 across by 5 cols to F2. Select A2:F2, copy down to cover the max
expected extent of source data, say down to F200. Minimize/hide col A. The
automated results will be returned in cols B to F, viz. an auto-ascending
sort of Sheet1 by the numeric values in Sheet1's col E, with all lines
neatly packed at the top. Lines with tied key values (if any) will be
returned in the same relative order that they appear within the source.

For an Auto-Descending Sort
Same construct as above, but utilizing this set of point formulae
(try setting this up in another sheet)
In A2: =IF(Sheet1!E2="","",Sheet1!E2-ROW()/10^10)
In B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(Sheet1!A:A,MATCH(LARGE($A:$A,ROWS($1:1)),$A:$A,0)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---

1. are your data numbers or text?
2. are there duplicates in your data?

Each column is only numbers or text. A and E are numeric, B and D
text.
There are duplicates, particularly in column E which I need to sort
on.

PK

 

[philkime]

http://www.sulprobil.com/html/sorting.html

This just what I needed - many thanks. It worked really well.

 

[philkime]

http://www.sulprobil.com/html/sorting.html

This just what I needed - many thanks. It worked really well.