Data Search in Excel

Question

I have 2 data tables of 100x100 variables in excel.

I need to have a function that returns all possible sets of variables that give a given target value. What I am considering is some kind of recursive 2-dimensional search function. Can someone point me in the right direction?

Answer 1

This can be done without VBA, quite compact, for example.

Suppose your 100x100 table is in B2: CW101, and we put a list of numbers from 1 to 100 to the left of A2 to A101 and again from 1 to 100 at the top from B1 to CW1

Create a column of cells below it, 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))

"", Ctrl-Shift-Enter Enter, {} .

, . , B103, 999999.

, , Rowx100 +, , MAX , , .. , . ( -).

, 9922, 99, 22, .

, .

Answer 2

, , , 99% .

VBA, , Sub, . , , , , Redim Preserve-d - . , (Control-Shift-Enter).

, , , : Excel .

Answer 3

Solver?

http://office.microsoft.com/en-us/excel/HA011118641033.aspx

Answer 4

, VBA, . , . , , .

:

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

VBA.

Answer 5

, VBA, . Excel . 100x100 10 000 .

, .

- , . N = , . 100 .

NxN. Excel

( INDEX) 1, 2... NxN.

(DATAROW) 1, 2... N, 1, 2... N... , - = MOD (INDEX-1, N) + 1

(DATACOL) 1, 1, 1... 2, 2, 2... ( N ). = INT ((INDEX-1)/N) +1

(VALUE) , - : = OFFSET ($ A $1, DATAROW, DATACOL), $A $1

, .

(LOOKUP) : = MATCH (, OFFSET (VALUERANGE, [LOOKUP-1], 0), 0) + [LOOKUP-1]

[LOOKUP-1] (, F4 F3). 0 ​​ LOOKUP.

VALUERANGE ( $) VALUE.

LOOKUP INDEX, DATAROW DATACOL, .

VALUERANGE, , .

, , , ...