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.

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/...pivotstart.htm

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Dave K." <canadave*REMOVETHIS*_@shaw.ca> wrote in message
news:VbY5d.545851$gE.150525@pd7tw3no...
> 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.
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.769 / Virus Database: 516 - Release Date: 24/09/2004

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

--Dave

"Ken Wright" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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/...pivotstart.htm
>
> http://www.contextures.com/xlPivot01.html

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 K." <canadave*REMOVETHIS*_@shaw.ca> wrote in message
news:qB%5d.124524$%S.30791@pd7tw2no...
> Thanks, Ken, that looks like exactly what I need.
>
> --Dave
>
> "Ken Wright" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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/...pivotstart.htm
>>
>> http://www.contextures.com/xlPivot01.html
>
>

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." wrote:
>
> 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.

--

Dave Peterson
(E-Mail Removed)

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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." wrote:
>>
>> 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.
>
> --
>
> Dave Peterson
> (E-Mail Removed)

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



"Dave K." wrote:
>
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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." wrote:
> >>
> >> 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.
> >
> > --
> >
> > Dave Peterson
> > (E-Mail Removed)

--

Dave Peterson
(E-Mail Removed)

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!
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
"Dave K." <canadave*REMOVETHIS*_@shaw.ca> wrote in message
news:VbY5d.545851$gE.150525@pd7tw3no...
> 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.
>
>

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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Dave K." <canadave*REMOVETHIS*_@shaw.ca> wrote in message
news:r%%5d.546666$gE.188955@pd7tw3no...
> 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 K." <canadave*REMOVETHIS*_@shaw.ca> wrote in message
> news:qB%5d.124524$%S.30791@pd7tw2no...
>> Thanks, Ken, that looks like exactly what I need.
>>
>> --Dave
>>
>> "Ken Wright" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> 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/...pivotstart.htm
>>>
>>> http://www.contextures.com/xlPivot01.html
>>
>>
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.769 / Virus Database: 516 - Release Date: 24/09/2004