Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Hepler, Kansas, USA
    Posts
    151
    Thanks
    6
    Thanked 0 Times in 0 Posts

    make a list (Windows 98SE)

    I made a catalog in Exel listing all the music I have.
    Column A lists the publication
    Column B lists the date of publication if it's a monthly or the series number if it's one of a series.
    Column C lists the name of a composition.
    Column D lists the "tune name"
    Column E lists the page number
    Column F lists the composer
    Column G lists the arranger
    Column H lists the date I bought the publication
    Column I lists the amount I paid for the publication.
    I want to print a list of the publications (column A). In each publication there are multiple compositions so the publication is listed as many times are there are compositions in the publication.
    How do I print a list of publications so each publication is listed only once.?

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: make a list (Windows 98SE)

    wwob

    Try and see if you can work with the SubTotals on the Data menu. Also try filtering and deleting the extras.

    If you need help, post a reply to this message.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: make a list (Windows 98SE)

    I think the best tool for your requirements is the Advanced Filter, available under Data | Filter | Advanced Filter. This allows you to extract records based on selected criteria and copy the data to a new location and restrict it to unique items, so that if you set the Criteria as Publisher >"", and set Unique Items only, you will get a list of publications. In the extracted list you can include as many fields as you wish. I attach a sample which might illustrate what I mean.

    Excuse the lazy approach to data completion.

    Andrew C
    Attached Files Attached Files

  4. #4
    New Lounger
    Join Date
    Mar 2005
    Location
    Wellington, Wellington
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: make a list (Windows 98SE)

    I have found one way of dealing with duplicates that seems to work with records under 10,000.

    You can add a further column that identifies duplicates with the formula "=countif(A2:$A$1000,A2)"

    What this does is check from "this row" down and checks how many matching records there are. The last record will always have a value of 1 - this gives you all the unique records. You can extend it so that it reads "=if(countif(A2:$A$1000,A2)>1,"Dupl","Unique") " or summat like that.

    May be crude, but I find it has worked. If you need to de-dupe on multiple criteria - such as tune name and composer, you will need to create a unique key column - I create a new column that simply merges all the other columns I want to check on - e.g. "=D2&E2"

    But you don't really want to de-dupe at all. <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

    An alternative is to simply create a pivot table with Publication and Composition both in your row selection. You count can be anything - it doesn't really matter. If you need to, copy and paste values somewhere else to tidy it up.


    Gravey.

  5. #5
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Hepler, Kansas, USA
    Posts
    151
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: make a list (Windows 98SE)

    Thanks for your help. Worked just fine.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •