I have a 2 variable 100x100 data table in excel.
I need to have a function that returns all the possible sets of variables that yield a given target value. What I am looking at is some kind of a reursive 2 dimensional lookup function. Can someone point me in the right direction?
I tried this a lot without using VBA but doesn't seem to be possible without it. To solve this issue , I needed to loop through the entire array and found closest values. These values were then derefernced using calls and range properties and the output was generated in a range being incremented at each valid match.
The quick and dirty implementation is as under:
Dim arr As Range
Dim tempval As Range
Dim op As Integer
Set arr = Worksheets("sheet1").Range("b2:ao41")
op = 1
Range("B53:D153").ClearContents
For Each tempval In arr
If Round(tempval.Value, 0) = Round(Range("b50").Value, 0) Then
Range("b52").Offset(op, 0).Value = Range("a" & tempval.Row).Value
Range("b52").Offset(op, 1).Value = Cells(tempval.Column, 1).Value
Range("b52").Offset(op, 2).Value = tempval.Value
op = op + 1
End If
Next
Range("b50").Select
I am still looking for an approach without VBA.
There is no built-in function that will do what you want, I'm 99% sure of that.
A VBA function that returns an array could be built, along the lines of the quick-and-dirty Sub already shown. Create an Variant to hold the output, perhaps Redimmed to the maximum possible number of results and Redim Preserve-d down to the actual number at the end. Then return that as the result of the function which then needs to be called as an array function (Control-Shift-Enter).
One down-side is that you'd have to ensure that the target range was large enough to hold the entire result: Excel won't do that automatically.
I've got a solution that doesn't use VBA, but it's fairly messy. It involves creating a further one-dimensional table in Excel and doing lookups on that. For a 100x100 data table, the new table would need 10,000 rows.
Apologies if this doesn't fit your needs.
A summary is below - let me know if you need more detail. N = the dimension of the data, e.g. 100 in your example.
First, create a new table with five columns and NxN rows. In each case, replace my column names with the appropriate Excel reference
The first column (call it INDEX) simply lists 1, 2... NxN.
The second column (DATAROW) contains a formula to loop through 1, 2... N, 1, 2...N... This can be done using something like =MOD(INDEX-1, N)+1
The third column (DATACOL) contains 1, 1, 1... 2, 2, 2... (N times each). This can be done with =INT((INDEX-1)/N)+1
The fourth column (VALUE) contains the value from your data table, using something like: =OFFSET($A$1, DATAROW, DATACOL), assuming your data table starts at $A$1
We have now got a one-dimensional table holding all your data.
The fifth column (LOOKUP) contains the formula: =MATCH(target, OFFSET(VALUERANGE, [LOOKUP-1], 0),0)+ [LOOKUP-1]
where [LOOKUP-1] refers to the cell immediately above (e.g. in cell F4 this refers to F3). You'll need a 0 above the first cell in the LOOKUP column.
VALUERANGE should be a fixed (named or using $ signs) reference to the entire VALUE column.
The LOOKUP column then holds INDEX numbers which can be used to look up DATAROW and DATACOL to find the position of the match in the data.
This works by searching for matches in VALUERANGE, then searching for matches in an adjusted range starting after the previous match.
It's much easier in a spreadsheet then via the explanation above, but that's the best I can do for the moment...
It can be done without VBA, fairly compactly, like so.
Suppose your 100x100 table is in B2:CW101, and we put a list of numbers 1 to 100 down the left from A2 to A101, and again 1 to 100 across the top from B1 to CW1
Create a column of cells underneath, starting (say) in B104
B104=MAX(($A$2:$A$101*100+$B$1:$CW$1<B103)*($B$2:$CW$101=TargetValue)*($A$2:$A$101*100+$B$1:$CW$1))
This is an "array" formula,so press Ctrl-Shift-Enter instead of Enter, and curly brackets {} should appear around the formula.
Then copy down for as many rows as you might need. You also need to put a large number above your first formula, ie in B103, eg 999999.
What the formula does is to calculate Rowx100+Column, but only for each successful cell, and the MAX function finds the largest result, excluding all previous results found, ie it finds the target results one at a time, starting from bottom right and working up to top left. (With a little effort you could get it to search the other way).
This will give you results like 9922, which is row 99, column 22, and you can easily extract these values from the number.
Hope this makes sense.