Page 1 of 4 123 ... LastLast
Results 1 to 10 of 36

Thread: How to return a result's position in a 2D array?

  1. #1
    New Member
    Join Date
    Nov 2010
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to return a result's position in a 2D array?

    Hi

    I have a list of 107 stocks and have generated a correlation matrix between them (i.e. I have a 107x107 matrix of results).

    Next step I have used LARGE() to generate an ordered list of the highest results.

    How can I map those results back to the names of the stocks?

    i.e. generically, I have two 107x107 matrices, how can I take a result from one matrix and return the analogously-positioned result from the other matrix?

    or another way of looking at it is how to return a result's grid ref {X,Y} from a matrix?

    The only thing i've thought of is for each row or column take a slice of the 1st, 2nd, 3rd highest results but that relies on me not looking to return that many results as it isn't scalable (i'm probably looking at the top 400 results from this ~11000 result matrix).

    Please help...

  2. #2
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    8,610
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to return a result's position in a 2D array?

    The MATCH function can be used to show an item's position in an array.
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  3. #3
    New Member
    Join Date
    Nov 2010
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to return a result's position in a 2D array?

    only a one-dimensional array though, not a matrix

  4. #4
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: How to return a result's position in a 2D array?

    Perhaps a combination of INDEX and MATCH like this http://www.j-walk.com/ss/excel/usertips/tip020.htm
    HTH, Peter
    Please test any code on a copy of your workbook.

  5. #5
    New Member
    Join Date
    Nov 2010
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to return a result's position in a 2D array?

    I'm very familiar with the concept of using MATCH against row and column headings and then INDEX on these two results to get the correct entry in a grid of results.

    This isn't that problem. Imagine a 9 entry matrix as follows

    {1, 2, 3}
    {4, 5, 6}
    {7, 8, 9}

    i.e. cell A1 contains 1, C3 contains 9.

    I need a function ARRAYFIND() such that ARRAYFIND(6) = C2
    or = {2,3} in coordinate form.

    The numbers in the grid are non-rounded correlation coefficients so will all be unique.

    Thanks

  6. #6
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,250
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to return a result's position in a 2D array?

    Hi

    If I understand correctly you want a reverse lookup on a 2D matrix.

    Try in H3:

    =MIN(IF($B$2:$E$6=H$2,ROW($B$2:$E$6)-ROW($B$2)+1))

    ... confirm with CTRL-SHIFT-ENTER

    In H4:

    =IF(H3=0,0,MATCH(H$2,INDEX($B$2:$E$6,H$3,0),0))

    Copy down.

    A result of 0 in the row and column means the value was not found.


     ABCDEFGHIJK
    1           
    2 39711052870 Value29374 
    3 89626210137 Row405 
    4 34544424395 Column204 
    5 44129557663      
    6 5266027074      
    7           
    [Book1]Sheet1
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  7. #7
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,028
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    8 Thread(s)

    Default Re: How to return a result's position in a 2D array?

    Try
    =SUMPRODUCT(--(dataRange=12)*ROW(dataRange)) and
    =SUMPRODUCT(--(dataRange=12)*COLUMN(dataRange))

    These will return the Row and column number of the cell containing 12.
    Note that the are Row and Column numbers (relative to the whole worksheet) not indices (relative to dataRange)

    =SUMPRODUCT(--(dataRange=12)*ROW(dataRange)) - ROW(dataRange) +1
    =SUMPRODUCT(--(dataRange=12)*COLUMN(dataRange)) - Column(dataRange) +1

    will return indices.

  8. #8
    New Member
    Join Date
    Nov 2010
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to return a result's position in a 2D array?

    Thanks chaps. Two good results, neither of which I understand!

    FYI Mike your solution is a bit more efficient in terms of sheet size so I think i'll go with that one

  9. #9
    New Member
    Join Date
    Nov 2010
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to return a result's position in a 2D array?

    Hi JP.

    The following code should give you the ARRAYFIND function you asked for. It has two arguments. The first is the value you are looking for, the second is the range of the table. So, for instance, write "=ARRAYFIND(6,A1:C3)".

    Code:
    Public Function ARRAYFIND(target1 As Double, target2 As Range)
    
    For Each cell In target2
    
    If cell.Value = target1 Then
        variable1 = cell.Row
        variable2 = cell.Column
        variable3 = Cells(variable1, variable2).Address(False, False)
    End If
    
    Next
    
    ARRAYFIND = variable3
    
    End Function

  10. #10
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,028
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    8 Thread(s)

    Default Re: How to return a result's position in a 2D array?

    My solution is pretty simple: given dataRange in A2:C4 of

    11 22 33
    44 55 66
    77 88 99

    --(dataRange=66) returns the array
    0 0 0
    0 0 1
    0 0 0

    Since each of the data in the dataRange is unique, there will be only one 1 in that array.
    The array ROW(dataRange) is
    2 2 2
    3 3 3
    4 4 4

    Multiplyting those two arrays component-wise gives the array
    0 0 0
    0 0 3
    0 0 0

    The Sum of which is 3, the row number (relative to the sheet) of the cell that contains 66.

    Subtract ROW(dataRange) (i.e.2) and add 1 to get 3-2+1 = 2, the Row index of that cell relative to dataRange.
    Last edited by mikerickson; Dec 21st, 2010 at 11:21 AM.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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