copy sort results to different worksheet?

[Dave K.]

Hi all,

Relative Excel newbie here

I have an Excel file with three worksheets in it. Right now, only the first
worksheet has data--the data is four columns of unsorted text (it's a list
of karaoke songs...the columns are labeled "Song Title", "Artist", "CD", and
"Track Number").

I know how to sort the data by different columns so that I can either have
everything listed alphabetically by Song Title, or alphabetically by Artist.
What I'd like to do is somehow be able to take those two "sorts" and output
each one on a different worksheet--preferably automatically in real-time.

So in other words, if I add a few more rows of data to unsorted Sheet 1,
containing new songs/artists/CDs/tracks, I'd like to see a list sorted by
Song Title on Sheet 2 that reflects the updates to the list. And on Sheet
3, I'd like to see the same list sorted by Artist.

That way, all I have to do is add my data on Sheet 1 as I add to my list of
songs, then print off Sheet 2's contents and Sheet 3's contents, and I'm set
for karaoke.

Is this possible to do? Is it possible to do automatically in real-time, or
would a macro (preferably a single-button click macro) be needed?

Thanks in advance for any help you all can provide!

--Dave K.

 

[Ken Wright]

Take a look at Pivot tables and Dynamic ranges for your source data. Examples
of both can be found here:-

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm

http://www.contextures.com/xlPivot01.html

 

[Dave K.]

Thanks, Ken, that looks like exactly what I need.

--Dave

 

[Dave K.]

Actually, that doesn't seem to help. If I try to create a Pivot Table, the
data gets "counted" rather than simply "listed". So I get a "count" of how
many songs I have, rather than a list of the songs.

 

[Dave Peterson]

How about selecting your range (all the columns is nice)

Then Data|filter|autofilter

Filter to show only the info you want to see. Print that view. And off to
karaoke!

Data|filter|showall
is a quick way to see everything.

 

[Dave K.]

Dave,

Thanks, but I don't think that's quite what I'm looking for I'm not
really looking to filter the data (i.e. see some sort of subset of "all" of
the data); I just want see the entire data on a separate worksheet,
re-sorted automatically without me having to do any manual actions.
Obviously, I could re-sort it on the original worksheet, then do a copy and
paste to the new worksheet...but I just figured I'd try to find the more
elegant "automatic" method of doing it and learn some Excel in the process


--Dave K.

 

[Dave Peterson]

I think the more elegant way would be sorting and autofiltering, but if you
really want separate worksheets, maybe you could steal some code from Debra
Dalgleish's site:

http://www.contextures.com/excelfiles.html

Look for:

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- creates a list of unique items,
creates a sheet for each item, then replaces old data with current.
AdvFilterCity.xls 46 kb

and

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Max]

Just another option using formulas to play with ..
(remember to have fun <g>)

Assuming you have

In Sheet1, cols A to D, data in row2 down
-------------
Song Title_Artist_CD_Track Number
ABC_____ZZ____A___111
DEF_____YY____D___222
GHI_____XX____G___333
XYZ_____AA____X___444
etc

Put in H1: =CHAR(ROW(A65))
Put in I1: =ROW()
Select H1:I1, fill down to I26
(This quickly sets up an "alphanumeric" conversion table for use)

Put in F2:
=IF(A2="","",IF(ISNA(MATCH(LEFT(TRIM(A2),1),$H$1:$H$26,0)),ROW()*10^10,VLOOK
UP(LEFT(TRIM(A2),1),$H$1:$I$26,2,0)+ROW()/10^10))

Put in G2:
=IF(B2="","",IF(ISNA(MATCH(LEFT(TRIM(B2),1),$H$1:$H$26,0)),ROW()*10^10,VLOOK
UP(LEFT(TRIM(B2),1),$H$1:$I$26,2,0)+ROW()/10^10))

Select F2:G2, fill down to a safe, "max" expected number of rows
that will contain data in cols A to D, say, down to G1000

Cols F and G will set it up nicely for us to auto-extract the
desired sort order by Song Title and Artist in Sheets 2 and 3

(Hide away cols F to I if desired)

In Sheet2 (Auto-sort by Song Title)
-------------
With the same labels in A11,
Song Title_Artist_CD_Track Number

Put in A2:
=IF(ISERROR(MATCH(SMALL(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)),"",OFFSET(She
et1!$A$1,MATCH(SMALL(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)-1,COLUMN(A1)-1))

Copy A2 across to D2, fill down to D1000

In Sheet3 (Auto-sort by Artist)
-------------
With the same labels in A11,
Song Title_Artist_CD_Track Number

Put in A2:
=IF(ISERROR(MATCH(SMALL(Sheet1!$G:$G,ROW(A1)),Sheet1!$G:$G,0)),"",OFFSET(She
et1!$A$1,MATCH(SMALL(Sheet1!$G:$G,ROW(A1)),Sheet1!$G:$G,0)-1,COLUMN(A1)-1))

Copy A2 across to D2, fill down to D1000
--
Sheets 2 and 3 should return the auto-sorted lists
by Song Title (Sheet2) and by Artist (Sheet3)
for the original list in Sheet1

You continue adding new entries into Sheet1,
then just hop over to Sheets 2 and 3 to print
and head off for the karaoke!

 

[Ken Wright]

Just drag the Song field to the Row field as well as the Data field, and that
way you get the list and the count.