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

jp1981

New Member
Joined
Nov 24, 2010
Messages
6
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...
 
oops... INDEX is not necessary. Simpler formulas...

Array formula in G2 copied down
=SMALL(IF(B$2:D$4=F2,A$2:A$4),COUNTIF(F$2:F2,F2))
Ctrl+Shift+Enter

Array formula in H2 copied down
=MOD(SMALL(IF($B$2:$D$4=F2, ROW(B$2:D$4)+B$1:D$1/10),COUNTIF(F$2:F2,F2)),1)*10
Ctrl+Shift+Enter

M.
 
Last edited:
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
You have some formula solutions to consider, but if you want to go with a VBA solution like Mike suggested, here is another one that you can consider...
Code:
[table="width: 500"]
[tr]
	[td]Sub RowAndColumns()
  Dim R As Long, C As Long, X As Long, Data As Variant, Arr As Variant
  Data = Range("A2").CurrentRegion
  With CreateObject("Scripting.Dictionary")
    For R = 2 To UBound(Data, 1)
      For C = 2 To UBound(Data, 2)
        .Item(Data(R, C)) = .Item(Data(R, C)) & " " & Data(R, C) & "|" & Data(R, 1) & "|" & Data(1, C)
      Next
    Next
    Arr = Split(Application.Trim(Join(.Items)))
    Columns("F:H").Clear
    Range("F1").Value = "Num|Row|Col"
    Range("F2").Resize(1 + UBound(Arr)) = Application.Transpose(Arr)
    Columns("F").TextToColumns , xlDelimited, , , False, False, False, False, True, "|"
  End With
End Sub[/td]
[/tr]
[/table]
 
Upvote 0
Hi
Remark:
For number values Marcelo's solution is much simpler.
In my solution I assumed that the values can be number values but also strings or booleans.
 
Upvote 0
I have a Names based solution, that assumes that the numbers have only one decimal place

Define these names

Name:DataRange
RefersTo: =Sheet1!$B$2:$D$4

Name: Data_Row_Col
RefersTo: =10*DataRange+ROW(DataRange)/1000+COLUMN(DataRange)/1000000

Then in F2, put =SMALL(B$2:D$4,ROWS(F$2:F2)) (as in Marcelo's)
In G2 , =INT(1000*MOD(SMALL(Data_Row_Col,ROW(A1)),1)) -ROW(DataRange)+1
In H2, =ROUND(1000*MOD(1000*SMALL(Data_Row_Col,ROW(D1)),1),0)-COLUMN(DataRange)+1

If the data has two decimal places, change the first 10 in the definition of Data_Row_Col.

No CSE entry.
 
Last edited:
Upvote 0
Hi,

Thank you very much for your reply.

There is a flow problem below. Variables and their values are given in Columns A and B. Situtaions based on Variable 1 and Variable 2 are given at the D,E and F columns. The total flow must not be longer than 10. If so, it means overload and skip that step. All variables should be removed after the sums are reached to 10. At the end of the calculations, get final flows either it reaches satisfaction or not (as shown in the 1001th row).

How to calculate columns G,H,I and J?

Thank you very much


ABCDEFGHIJ
1VariableValue
SituationsFixed Variable 1Fixed Variable 2Total (MAX 10) Flow Explanation
2A3
1AC=3+5A-C
3B4
2AD=3+5+2A-C-DSatisfied, remove A,C and D+
4C5
3KL=5+2K-L

5D2
4LB=4+5+2B-K-LOverload, skip this step
6E1
5EA------A is removed skip this step
7F6
6CJ------C is removed skip this step
8G6
7HE=2+1E-H
9H2
8KH=5+2+2+1E-H-K-LSatisfied, remove E,H, K and L+
10I1
9PR.........
11J3

...............
12K5

...............
13L2








......


1001


1000XW=1+2W-XSatisfied, W and X never removed before+


<tbody>
</tbody>
 
Upvote 0
Hey guys, does anyone know how to obtain the exact position of the formula's result?

My formula is MINIF array formula conducted on a very complicated matrix with a lot of duplicates. Basically, I would like to use this (x,x) as a criteria for my further calculations.

So far, I have tried CELL("address" but it doesn't seem to work with MINIF.

I would prefer a formula solution if it exist, but VBA will work as well.

Thank you guys, all ideas are appreciated.
 
Upvote 0
Cera22,
Rather than resurrecting a necro-thread that has two different questions, it would be best if you started your own thread and explained your problem from the start.
 
Upvote 0
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.

To clarify:

dataRange inside the SUMPRODUCT(...) must be the range of the of the array to search, such as $B$2:$J$24

dataRange in only the ROW(dataRange) or COL(dataRange) that's outside of SUMPRODUCT(...) in the second version of the formulas above must be the address of the upper left cell in the range, such as $B:$2 The ROW,COL inside SUMPRODUCT() gets the full range.

Otherwise, the returned result will be an array of numbers rather than the desired scalar. At least it was for me in Excel365. (perhaps it worked differently in 2010 when this post was made)

Don't forget to use absolute addresses ($) in most cases for dataRange because the table is fixed in place and shouldn't be relative to the different places you might copy the formula to.

Aside from that, the overall solution is brilliant, even a decade later.
 
Upvote 0
I found this thread while looking for a solution to a problem (which it helped with) and thought I could add another step to get a delimited list of cell locations that contain the target value (in case it's useful to anyone).
Formula: =TEXTJOIN("; ",TRUE,IF((data=value),CHAR(UNICODE("A")-1+(data=value)*COLUMN(data))&TEXT((data=value)*ROW(data),"0"),""))
Screenshot:

1658455906108.png
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top