If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sorting and displaying different results
I have a spreadsheet made up of 2 columns - Drawing Number
and Drawing Name. I would like to have 2 different sorted results show up on one page without actually typing the info twice (one list sorted by drawing number and the other sorted by name). In other words, I want the base data to be referenced somewhere else sorted differently and will reflect any future changes to the base data. Am I making sense here? Thanks in advance for any help Mac |
#2
|
|||
|
|||
Maybe something along these lines ..
Assuming the sample source data below is in Sheet1, cols A and B, data from row2 down (sample data intentionally contain tied items) Dwg# DwgN 1008 James 1005 Aaron 1003 Peter 1001 Kelly 1000 Peter 1009 George 1008 Mary 1001 Larry 1000 Michael etc where Dwg# = Drawing Number DwgN = Drawing Name Using 2 empty cols to the right of the data, say cols C & D? Put in C2: =IF(A2="","",A2+ROW()/10^10) Put in D2: =IF(B2="","",CODE(UPPER(LEFT(TRIM(B2),1)))+ROW()/10^10) Select C22, copy down to say, D100 to cover the max expected range of data in the source In Sheet2 ------------ Paste the labels: Dwg#, DwgN into say, A1:B1 and D1:E1 Put in A2: =IF(ISERROR(SMALL(Sheet1!$C:$C,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA LL(Sheet1!$C:$C,ROWS($A$1:A1)),Sheet1!$C:$C,0))) Copy A2 across to B2, fill down to B100 (cover the same range as in Sheet1) Put in D2: =IF(ISERROR(SMALL(Sheet1!$D:$D,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA LL(Sheet1!$D:$D,ROWS($A$1:A1)),Sheet1!$D:$D,0))) Copy D2 across to E2, fill down to E100 (cover the same range as in Sheet1) Cols A & B will return the ascending auto-sort by Dwg# of cols A & B in Sheet1 Cols D & E will return the ascending auto-sort by DwgN of cols A & B in Sheet1 For the sample data in Sheet1, you'll get: In Cols A & B ------------- Dwg# DwgN 1000 Peter 1000 Michael 1001 Kelly 1001 Larry 1003 Peter 1005 Aaron 1008 James 1008 Mary 1009 George In Cols D & E ------------- Dwg# DwgN 1005 Aaron 1009 George 1008 James 1001 Kelly 1001 Larry 1008 Mary 1000 Michael 1003 Peter 1000 Peter Note that in both sorted lists, tied items (if any) will be returned in the same relative order that they appear in the source in Sheet1 And if you want the auto-sorting in *descending* order, just change: "+ROW()/10^10" to "-ROW()/10^10" in the formulas in Sheet1's cols C & D and change: SMALL to LARGE in the formulas in Sheet2 (Edit Replace could be used to effect the changes easily) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik atyahoodotcom ---- "Mac" wrote in message ... I have a spreadsheet made up of 2 columns - Drawing Number and Drawing Name. I would like to have 2 different sorted results show up on one page without actually typing the info twice (one list sorted by drawing number and the other sorted by name). In other words, I want the base data to be referenced somewhere else sorted differently and will reflect any future changes to the base data. Am I making sense here? Thanks in advance for any help Mac |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Subreport Not Displaying | Ed Ardzinski | Setting Up & Running Reports | 2 | March 8th, 2005 01:45 PM |
Aggregate query - Displaying field associated with MAX record | ThomasTobey | Running & Setting Up Queries | 3 | February 9th, 2005 01:19 PM |
Help on Customizing Views and sorting by Categories | Judy | Contacts | 0 | September 1st, 2004 10:57 PM |
sorting contacts | steve | Contacts | 5 | August 20th, 2004 09:47 PM |
Trouble sorting contacts when sending email | Gary Townsend | Contacts | 2 | May 15th, 2004 02:21 AM |