matt_m
asked on
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Cheers
Rob H
ASKER
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
ASKER