Home |
Search |
Today's Posts |
#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 C2:D2, 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 <atyahoo<dotcom ---- "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|