Sorting and displaying different results

  • Thread starter Thread starter Mac
  • Start date Start date
M

Mac

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
 
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)
 
Back
Top