Uaing arrays to solve a excel problem

Posted on 2011-05-12
Last Modified: 2012-05-11
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!

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.

    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.
    LVL 92

    Assisted Solution

    by:Patrick Matthews

    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.


    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.
    LVL 31

    Expert Comment

    by:Rob Henson
    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

    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

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    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…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now