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!

Who is Participating?
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.

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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 HensonFinance AnalystCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.