extract & sort problem

[Jim]

I have a spreadsheet with over 1,000 rows of information. Two of the columns
contain data such as a name, and a numerical value. I'd like to be able to
extract say, the top twenty values. That would be a simple sort. But I'd
like to take into consideration that certain values in the "name" column may
be repeated throughout the spreadsheet, with various values attached to
them. So, for example, if "smith" were repeated four times, with values of
1000, 2000, 3000 and 4000, those values could be added and combined into one
entry in that top twenty. Is there a way to do this automatically?

Thanks in advance for any help.

Jim

--

[Dav]

It could be added to a macro to automate it, even by just doing keystrokes, but if you run a pivot table report with names as rows and the sum of the values as the values this will give you the totals for each name

This table can then be sorted by the value column and then you have your result

So once you have set it up, if your range is big enough to include any new entries that you may add when you set up the pivot. You just need to refresh and then sort and then if you wished copy the top 20 rows to whereever you want in your report. It would be better to create by pressing a button, otherwise it would recalcualte everytime you added a new entry and this would not be an efficient use of resource.

Regards

Dav


Regards

Dav

[Bill Ridgeway]

Have you considered using a Pivot Table?

Click on <Data> <Pivot table and <Pivot chart report> and follow through.
The art in constructing a pivot table is in understand what goes in the
three areas but a bit of experiment with dragging and dropping should give
you a result.

Regards.

Bill Ridgeway
Computer Solutions

"Jim" <[email protected]> wrote in message
news:[email protected]...
>I have a spreadsheet with over 1,000 rows of information. Two of the
>columns contain data such as a name, and a numerical value. I'd like to be
>able to extract say, the top twenty values. That would be a simple sort.
>But I'd like to take into consideration that certain values in the "name"
>column may be repeated throughout the spreadsheet, with various values
>attached to them. So, for example, if "smith" were repeated four times,
>with values of 1000, 2000, 3000 and 4000, those values could be added and
>combined into one entry in that top twenty. Is there a way to do this
>automatically?
>
> Thanks in advance for any help.
>
> Jim
>
> --
>
>

[Jim]

Dav & Bill,

Thanks so much for the responses. I'm new to pivot tables, so this will be a
learning curve, but I'll give it a shot! Thanks again.

Jim

"Bill Ridgeway" <[email protected]> wrote in message
news:[email protected]...
> Have you considered using a Pivot Table?
>
> Click on <Data> <Pivot table and <Pivot chart report> and follow through.
> The art in constructing a pivot table is in understand what goes in the
> three areas but a bit of experiment with dragging and dropping should give
> you a result.
>
> Regards.
>
> Bill Ridgeway
> Computer Solutions
>
> "Jim" <[email protected]> wrote in message
> news:[email protected]...
>>I have a spreadsheet with over 1,000 rows of information. Two of the
>>columns contain data such as a name, and a numerical value. I'd like to be
>>able to extract say, the top twenty values. That would be a simple sort.
>>But I'd like to take into consideration that certain values in the "name"
>>column may be repeated throughout the spreadsheet, with various values
>>attached to them. So, for example, if "smith" were repeated four times,
>>with values of 1000, 2000, 3000 and 4000, those values could be added and
>>combined into one entry in that top twenty. Is there a way to do this
>>automatically?
>>
>> Thanks in advance for any help.
>>
>> Jim
>>
>> --
>>
>>
>
>