Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tricky problem - auto-sorting without VBA
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tricky problem - auto-sorting without VBA
1. are your data numbers or text?
2. are there duplicates in your data? -- Gary''s Student - gsnu200855 "philkime" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tricky problem - auto-sorting without VBA
On May 26, 9:40*pm, Gary''s Student
wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tricky problem - auto-sorting without VBA
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tricky problem - auto-sorting without VBA
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. -- Gary''s Student - gsnu200855 "philkime" wrote: On May 26, 9:40 pm, Gary''s Student wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tricky problem - auto-sorting without VBA
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,MA TCH(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,MA TCH(LARGE($A:$A,ROWS($1:1)),$A:$A,0))) -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "philkime" wrote in message ... On May 26, 9:40 pm, Gary''s Student wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tricky problem - auto-sorting without VBA
On May 26, 11:31*pm, Bernd P wrote:
http://www.sulprobil.com/html/sorting.html This just what I needed - many thanks. It worked really well. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tricky problem - auto-sorting without VBA
On May 26, 11:31*pm, Bernd P wrote:
http://www.sulprobil.com/html/sorting.html This just what I needed - many thanks. It worked really well. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tricky Excel De-Dupe Problem | Excel Worksheet Functions | |||
Tricky lookup problem | Excel Discussion (Misc queries) | |||
tricky sum problem | Excel Discussion (Misc queries) | |||
Tricky question with grouping and sorting | Excel Worksheet Functions | |||
Tricky array problem…. | Excel Discussion (Misc queries) |