Announcement

Collapse
No announcement yet.

tips on manipulating list data

Collapse

Unconfigured Ad Widget

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • #46
    To celebrate the 6 month anniversary of the last post, here is another tip that might be useful.

    While www.alltime-athletics.com provides an overall ranking number for each performance, many people are interested in performances ranked by athlete, Top5, Top10 etc.

    You can use a formula that relies on the data being sorted in the right order (eg by Athlete name) but this can be dangerous as the formula will break if you add extra data and forget to resort or sort for some other purpose and forget to return to the name sort.

    So what we need is a formula that can calculate a rank by category, irrespective of the order of data.

    Unfortunately, Excel does not have an explicit category ranking function but we can achieve the same effect by using another Excel function called SUMPRODUCT. There is a sample formula and an explanation in the attached image.

    Let me know if you find this useful.


    Excel_SUMPRODUCT_ranking.JPG

    Comment


    • #47
      El Toro - all of Olympedia's databases start out as Excel files so I use them a lot. The way I solve the problem of losing the original sort is simply to create an extra field/column called sorter which is just the original order. I can then play with it and always go back to the original if I need to. Sorter is just numbered 1 to whatever. Several different ways to create it but easiest is just to insert 1 in the first record and then the others are =A2+1. You then have to cut/paste special (values only) to convert all to numbers from formulae.

      Comment


      • #48
        One other thing about cut/paste from online databases - usually these are tab-delimited. However, often times there will be an extra space thrown in in front of the tab. So you need to do a search/replace for (space-tab)-->(tab), otherwise you'll have all sorts of extraneous spaces at the end of fields.

        Comment


        • #49
          Originally posted by bambam1729 View Post
          El Toro - all of Olympedia's databases start out as Excel files so I use them a lot. The way I solve the problem of losing the original sort is simply to create an extra field/column called sorter which is just the original order. I can then play with it and always go back to the original if I need to. Sorter is just numbered 1 to whatever. Several different ways to create it but easiest is just to insert 1 in the first record and then the others are =A2+1. You then have to cut/paste special (values only) to convert all to numbers from formulae.
          Thanks for contributing bambam. It is always interesting to see how others do things.

          I have also used helper columns to sort and resort data when checking data from multiple sources that had different sort orders. This made it easy to switch sort order back and forth as I changed source documents without constantly having to reenter a multi-level sort sequence manually.

          However, to apply a simple number sequence against a sorted list, I would just use the Excel Autofill capability rather than a formula.

          For those unfamiliar with this process:
          1. Sort your data in the order you want it.
          2. In the column next to you data, number the first two cells in the column with 1 and 2
          3. select both cells
          4. double click the little square at the bottom right of the selected area
          5. Excell will insert 3,4,5...etc automatically right down to the end of your data.

          Autofill can be used to create a range of other useful sequential data as well. For anybody interested, the following tutorial covers most of the things you can do with Autofill:

          https://www.howtogeek.com/279148/how...e-fill-handle/

          Comment

          Working...
          X