[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

Uaing arrays to solve a excel problem

Hi All,

I have a problem where I have two columns of data on a worksheet.  One column has names which can be repeated and the other has values.  What I need then is the top 10 names by value.  For a simplified example if column A was (Mat, Paul, Mat) and column B was (2,4,1) the top 1 would be Paul with 4, followed by Mat with 3.  

Now I would hazard a guess that there are multiple solutions to such a problem but when I encounter these things at work I like to do them a new way so I learn something!  Following this I want to do it with a VBA custom function where the values would be rangeA, rangeB, and rank required.  Secondly I wanted to do it where the function creates a 2 dimensional array within VBA with unique Values from Column A and then their cumulative values from Column B (working with arrays are one part pf VBA that confuses the hell out of me!) .

Thanks for your help all!

3 Solutions
Patrick MatthewsCommented:
Rather than use arrays, why not create a PivotTable?

If you create a PivotTable, you can sort the PT by the value column, and use a value filter to show only the top N items by sum of value.
matt_mAuthor Commented:
Hey MP thanks for your response - Yes there are heaps of possible ways to solve this but I was after a method using the criteria in  my question.
Patrick MatthewsCommented:

Understood, but with respect, in my opinion using arrays would be a highly suboptimal way to go about it, as would creating a UDF.  (You could, of course, use code to automate creation of the PT.  That would be a nice touch, I think.)

If you need assistance in implementing a PT approach, please let me know.  Otherwise, I am sure another Expert will be happy to help you pursue the array avenue.

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

If you insist on using an array, for the purpose of messing with arrays; then the simple way to do what you are looking to do is:

sort the source array on the first column.
step through source array row by row; updating the index for the destination array each time the col 1 value of the indexed source array changes.     copy into col 1, and add into col 2 of destination array as you go.

You could also leave the source unsorted, and do a lookup into the destination array to return an index, or say -1 if the col 1 value doesn't exist yet;   append if needed, else add to the col 2 value in the destination array.

Both seem like very "long way around" though, since your essentially using the spreadsheet as a very inefficient place to store the values; then the other inefficiencies of using the scripting language of the spreadsheet to read, iterate, and write back out to the spreadsheet.    As opposed to using the spreadsheet apps native data handing functions as others have noted.
Rob HensonIT & Database AssistantCommented:
Conditonal Sum Wizard will create Array formulas on a dataset, useful if you have more than one condition to add up but you appear to only have one condition, ie name, so you could just use SUMIF to get a total for each entry then do a Top 10 Autofilter on the summarised data.

Rob H
matt_mAuthor Commented:
ok guys I 'm going to have to swallow my pride and admit my idea was pretty off target and pivot tablesa are the way to go

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now