Solved

# Uaing arrays to solve a excel problem

Posted on 2011-05-12
214 Views
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!) .

0
Question by:matt_m

LVL 92

Accepted Solution

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.
0

Author Comment

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.
0

LVL 92

Assisted Solution

matt_m,

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.

Patrick
0

Assisted Solution

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.
0

LVL 31

Expert Comment

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.

Cheers
Rob H
0

Author Comment

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
0

## Featured Post

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…