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

J

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...
 
Sort by date Sort by votes
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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]
[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Variable[/TD]
[TD="align: center"]Value[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]Situations[/TD]
[TD="align: center"]Fixed Variable 1[/TD]
[TD="align: center"]Fixed Variable 2[/TD]
[TD="align: center"]Total (MAX 10)[/TD]
[TD="align: center"] Flow [/TD]
[TD="align: center"]Explanation[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]=3+5[/TD]
[TD="align: center"]A-C[/TD]
[TD="align: center"][/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]=3+5+2[/TD]
[TD="align: center"]A-C-D[/TD]
[TD="align: center"]Satisfied, remove A,C and D[/TD]
[TD="align: center"]+[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]=5+2[/TD]
[TD="align: center"]K-L[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]=4+5+2[/TD]
[TD="align: center"]B-K-L[/TD]
[TD="align: center"]Overload, skip this step[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]---[/TD]
[TD="align: center"]---[/TD]
[TD="align: center"]A is removed skip this step [/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]---[/TD]
[TD="align: center"]---[/TD]
[TD="align: center"]C is removed skip this step[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]=2+1[/TD]
[TD="align: center"]E-H[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]=5+2+2+1[/TD]
[TD="align: center"]E-H-K-L[/TD]
[TD="align: center"]Satisfied, remove E,H, K and L[/TD]
[TD="align: center"]+[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]
[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]1001[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]=1+2[/TD]
[TD="align: center"]W-X[/TD]
[TD="align: center"]Satisfied, W and X never removed before[/TD]
[TD="align: center"]+

[/TD]
[/TR]
</tbody>[/TABLE]
 
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
mikerickson said:
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.
Click to expand...

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
You must log in or register to reply here.

Similar threads

D
  • Solved
Moving lines of a 2D array to a second 2D array based on a condition
Replies
3
Views
469
Peter_SSs
Peter_SSs
J
  • Solved
Chance Existing Excel formula to provide positive or negative result
Replies
1
Views
446
iggydarsa
iggydarsa
S
  • Question Question
Big, Multi-Step Macro for Efficiently Cutting Big Pieces Into Small Pieces
Replies
1
Views
491
Solon Aquila
S
S
  • Question Question
Help searching for strings in 2D array and returning multiple results
Replies
8
Views
625
Fluff
Fluff
N
  • Question Question
How to split cell value into multiple bands using array formula?
2
Replies
10
Views
426
RoryA
RoryA

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,637
Latest member
Ezio2866

Share this page

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