Sorting Source Data

Originally Posted by Dissembled

I have a large spreadsheet I create weekly charts from. There are 30 some odd column headers that are static and the data for those columns are filled in daily.

Once a week I create a chart of the "Top 10" from those 30 columns worth of data. I would like that chart to be a Clustered Column Chart in decending order showing the top 10 heavy hitters for the week (with the column headers from my worksheet being the X axis and the number of occurances being the Y axis). I can do this with a lot of copy/paste of the data I already have and then sorting it, but it seems ridiculous and the long, long way of doing it.

Does anyone have any suggestions on how to do what I am talking about? Am I making sense with my description?

Hi,

not a lot of sense to someone with a poor imagination and a lower capability of understanding.

The easy way as I understand your question would be to set the Chart from a spare area, and populate that area with an Offset dependant on a Large or Match function, but as there is no real indication as to what a 'hitter' is then only a general answer can be provided.

hth
---

Originally Posted by Dissembled

I was afraid my question was poorly worded, so thank you for the response.

I own a company that sells badgers. I have 30 salesmen who scour the country in search of badger sales.

I have their names in a row B1 through AE1 as column headers. In column A, I have each day of the year. Under each salesman, for each day, I track how much that saleman has sold in dollars. Every 8th row I have a weekly total for each salesman's sales amount for the week.

On the following Monday of each week, I like to print out a Column Chart where I show the top 10 salesmen and so I can present the Bronzed Badger Award. (I have found this to promote friendly competition among badger salesmen). I put the name of the saleman on the X axis and their total dollar amount sold for the week on the Y axis. They are in descending order, according to sales amount from left to right on the chart.

Currently, I copy all 30 of the salesmen names from B1 thru AE1 and place it somewhere off to the side of all my data. Then, I copy the row from the current week with the sales totals and paste in under their names. Then, I sort that data in descending order, create my chart using only the first 10 data points, and away I go.

I feel this is tedious and there has to be a better way to produce my Best Badgers chart without all the copying and pasting each week. Is anyone aware of a trick I'm missing here?

sounds like using Offset(A1,Large(B2:AE2,Row()),0) or something similar should do the sort for you, however "somewhere off to the side of all my data" is not enough to be able to write a formula for, perhaps a small disguised sample file might be useful.

---

I hate to ask this for fear of sounding stupid.. but you and your salesmen sell badgers?? As in, those large rodents with a very bad attitude? Or does "badgers" mean something else outside the U.S. (or inside the U.S. even!?)?

Originally Posted by pjoaquin

I hate to ask this for fear of sounding stupid.. but you and your salesmen sell badgers?? As in, those large rodents with a very bad attitude? Or does "badgers" mean something else outside the U.S. (or inside the U.S. even!?)?

another option is "a swablike device for cleaning excess mortar from the interiors of newly laid tile drains"


and of course there's http://www.weebls-stuff.com/toons/badgers/
---