+ Reply to Thread
Results 1 to 11 of 11

Finding Values in a 2d Array

Hybrid View

  1. 03-13-2013, 04:50 PM #1
    cheal2
    cheal2 is offline
    Forum Contributor
    Join Date
    03-11-2013
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    107

    Finding Values in a 2d Array

    Hi,

    I'm trying to find the top 5-10 values in a row of a 2d array. Of course this row varies.

    It would be nice to get the actual values, but if I end up with the headers to the columns that would be fine. I can find the headers if I can get the values returned.

    Example Output:

    Top Cities Count #1 Client #2 Client #3 Client
    Detroit 423 A1 C6 J12
    NYC 341 J4 A1 D2
    Chicago 201 D2 A1 J4


    Example 2D Array:

    Cities Sum A1 C6 D2 J4 J12
    Detroit 423 230 86 5 33 69
    NYC 341 77 38 54 163 9
    Chicago 201 41 29 75 19 37

    In the example output, I can already find the first two columns, its the last three I'm having problems with.

    Thanks
    Last edited by cheal2; 03-18-2013 at 09:50 AM.
    Reply With QuoteRegister To Reply
  2. 03-13-2013, 04:54 PM #2
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Finding Values in a 2d Array

    Hi

    I do not understand your sample output.

    Is this just part of your data or all your data?
    Reply With QuoteRegister To Reply
  3. 03-13-2013, 05:22 PM #3
    cheal2
    cheal2 is offline
    Forum Contributor
    Join Date
    03-11-2013
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    107

    Re: Finding Values in a 2d Array

    The output is a table to summarize performance in various cities by various clients. Performance data is stored in a very large 2d array. The cities and their associated count are already known/found values.

    Therefore if I want to find the top clients in Detroit, but I have the array above to search in order to determine what totals and client to display as 1 2 & 3.
    Reply With QuoteRegister To Reply
  4. 03-13-2013, 05:47 PM #4
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Finding Values in a 2d Array

    how many cities are you reporting on?
    Reply With QuoteRegister To Reply
  5. 03-13-2013, 05:47 PM #5
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Finding Values in a 2d Array

    Ok I am ready for you

    I thought a pivot table would give you your answer but its to hard for me. So we will go the macro route.

    I assume Your Cell headers represent customers or agents. Am I right?
    Reply With QuoteRegister To Reply
  6. 03-13-2013, 06:18 PM #6
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Finding Values in a 2d Array

    Ok

    I have written a macro to find the top 3 cities and the top three agents in each city.

    workbook attached.

    You can add additional cities but do not change the format.

    Enjoy.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 03-14-2013, 09:17 AM #7
    cheal2
    cheal2 is offline
    Forum Contributor
    Join Date
    03-11-2013
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    107

    Re: Finding Values in a 2d Array

    Thanks mehmetcik for the macro.

    But I was hoping to find a formula solution to this. Maybe someway to use a large in an index function to return the largest count for a client. This in turn could yield another index function to find the client name. Or vice versa.
    Reply With QuoteRegister To Reply
  8. 03-14-2013, 09:30 AM #8
    JosephP
    JosephP is offline
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Finding Values in a 2d Array

    perhaps like this?
    Attached Files Attached Files
    Josie

    if at first you don't succeed try doing it the way your wife told you to
    Reply With QuoteRegister To Reply
  9. 03-14-2013, 09:50 AM #9
    cheal2
    cheal2 is offline
    Forum Contributor
    Join Date
    03-11-2013
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    107

    Re: Finding Values in a 2d Array

    This looks perfect! Thank you!
    Reply With QuoteRegister To Reply
  10. 03-14-2013, 10:51 AM #10
    cheal2
    cheal2 is offline
    Forum Contributor
    Join Date
    03-11-2013
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    107

    Re: Finding Values in a 2d Array

    Forgot to check the Row Match Column. NVM
    Last edited by cheal2; 03-14-2013 at 11:04 AM.
    Reply With QuoteRegister To Reply
  11. 03-14-2013, 10:55 AM #11
    JosephP
    JosephP is offline
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Finding Values in a 2d Array

    the formula I posted only looked in the row for the specified city-not sure why you think otherwise?
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1