trying to get data out of a table based on multiple variables

[aslawson]

Hello all,

I am trying to write a program to extract data from a table based on a series of parameters any help would be greatly appreciated as it is prooving to be beyond my excel knowledge.

ie

from the first column i want to identify rows that add up to a value that I input into a cell ie "x" that also correspond to the lowest value of the sum of the same rows in fourth column and the highest value of the same rows in the third column.

I also want to ensure that the identified rows add up to a value in column two that is either in a range that I can set, equal to, more than or less than. "y"

ie if x = 100,000 and y > 45,000


[TABLE="width: 308"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD="align: right"]70000[/TD]
[TD="align: right"]35000[/TD]
[TD="align: right"]105000[/TD]
[TD] $ 2,065.00[/TD]
[/TR]
[TR]
[TD="align: right"]71000[/TD]
[TD="align: right"]35000[/TD]
[TD="align: right"]106000[/TD]
[TD] $ 2,094.50[/TD]
[/TR]
[TR]
[TD="align: right"]72000[/TD]
[TD="align: right"]35000[/TD]
[TD="align: right"]107000[/TD]
[TD] $ 2,124.00[/TD]
[/TR]
[TR]
[TD="align: right"]73000[/TD]
[TD="align: right"]35000[/TD]
[TD="align: right"]108000[/TD]
[TD] $ 2,153.50[/TD]
[/TR]
[TR]
[TD="align: right"]74000[/TD]
[TD="align: right"]35000[/TD]
[TD="align: right"]109000[/TD]
[TD] $ 2,183.00[/TD]
[/TR]
[TR]
[TD="align: right"]75000[/TD]
[TD="align: right"]35000[/TD]
[TD="align: right"]110000[/TD]
[TD] $ 2,212.50[/TD]
[/TR]
[TR]
[TD="align: right"]76000[/TD]
[TD="align: right"]35000[/TD]
[TD="align: right"]111000[/TD]
[TD] $ 2,242.00[/TD]
[/TR]
[TR]
[TD="align: right"]77000[/TD]
[TD="align: right"]35000[/TD]
[TD="align: right"]112000[/TD]
[TD] $ 2,271.50[/TD]
[/TR]
[TR]
[TD="align: right"]50000[/TD]
[TD="align: right"]22500[/TD]
[TD="align: right"]72500[/TD]
[TD] $ 1,475.00[/TD]
[/TR]
[TR]
[TD="align: right"]78000[/TD]
[TD="align: right"]35000[/TD]
[TD="align: right"]113000[/TD]
[TD] $ 2,301.00[/TD]
[/TR]
[TR]
[TD="align: right"]79000[/TD]
[TD="align: right"]35000[/TD]
[TD="align: right"]114000[/TD]
[TD] $ 2,330.50[/TD]
[/TR]
[TR]
[TD="align: right"]51000[/TD]
[TD="align: right"]22500[/TD]
[TD="align: right"]73500[/TD]
[TD] $ 1,504.50[/TD]
[/TR]
[TR]
[TD="align: right"]80000[/TD]
[TD="align: right"]35000[/TD]
[TD="align: right"]115000[/TD]
[TD] $ 2,360.00[/TD]
[/TR]
[TR]
[TD="align: right"]52000[/TD]
[TD="align: right"]22500[/TD]
[TD="align: right"]74500[/TD]
[TD] $ 1,534.00[/TD]
[/TR]
[TR]
[TD="align: right"]53000[/TD]
[TD="align: right"]22500[/TD]
[TD="align: right"]75500[/TD]
[TD] $ 1,563.50[/TD]
[/TR]
[TR]
[TD="align: right"]54000[/TD]
[TD="align: right"]22500[/TD]
[TD="align: right"]76500[/TD]
[TD] $ 1,593.00[/TD]
[/TR]
[TR]
[TD="align: right"]55000[/TD]
[TD="align: right"]22500[/TD]
[TD="align: right"]77500[/TD]
[TD] $ 1,622.50[/TD]
[/TR]
[TR]
[TD="align: right"]56000[/TD]
[TD="align: right"]22500[/TD]
[TD="align: right"]78500[/TD]
[TD] $ 1,652.00[/TD]
[/TR]
[TR]
[TD="align: right"]25000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]35000[/TD]
[TD] $ 737.50[/TD]
[/TR]
[TR]
[TD="align: right"]57000[/TD]
[TD="align: right"]22500[/TD]
[TD="align: right"]79500[/TD]
[TD] $ 1,681.50[/TD]
[/TR]
[TR]
[TD="align: right"]58000[/TD]
[TD="align: right"]22500[/TD]
[TD="align: right"]80500[/TD]
[TD] $ 1,711.00[/TD]
[/TR]
[TR]
[TD="align: right"]26000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]36000[/TD]
[TD] $ 767.00[/TD]
[/TR]
[TR]
[TD="align: right"]59000[/TD]
[TD="align: right"]22500[/TD]
[TD="align: right"]81500[/TD]
[TD] $ 1,740.50[/TD]
[/TR]
[TR]
[TD="align: right"]60000[/TD]
[TD="align: right"]22500[/TD]
[TD="align: right"]82500[/TD]
[TD] $ 1,770.00[/TD]
[/TR]
[TR]
[TD="align: right"]27000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]37000[/TD]
[TD] $ 796.50[/TD]
[/TR]
[TR]
[TD="align: right"]61000[/TD]
[TD="align: right"]22500[/TD]
[TD="align: right"]83500[/TD]
[TD] $ 1,799.50[/TD]
[/TR]
[TR]
[TD="align: right"]62000[/TD]
[TD="align: right"]22500[/TD]
[TD="align: right"]84500[/TD]
[TD] $ 1,829.00[/TD]
[/TR]
[TR]
[TD="align: right"]63000[/TD]
[TD="align: right"]22500[/TD]
[TD="align: right"]85500[/TD]
[TD] $ 1,858.50[/TD]
[/TR]
[TR]
[TD="align: right"]28000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]38000[/TD]
[TD] $ 826.00[/TD]
[/TR]
[TR]
[TD="align: right"]64000[/TD]
[TD="align: right"]22500[/TD]
[TD="align: right"]86500[/TD]
[TD] $ 1,888.00[/TD]
[/TR]
[TR]
[TD="align: right"]65000[/TD]
[TD="align: right"]22500[/TD]
[TD="align: right"]87500[/TD]
[TD] $ 1,917.50[/TD]
[/TR]
[TR]
[TD="align: right"]29000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]39000[/TD]
[TD] $ 855.50[/TD]
[/TR]
[TR]
[TD="align: right"]66000[/TD]
[TD="align: right"]22500[/TD]
[TD="align: right"]88500[/TD]
[TD] $ 1,947.00[/TD]
[/TR]
[TR]
[TD="align: right"]67000[/TD]
[TD="align: right"]22500[/TD]
[TD="align: right"]89500[/TD]
[TD] $ 1,976.50[/TD]
[/TR]
[TR]
[TD="align: right"]30000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]40000[/TD]
[TD] $ 885.00[/TD]
[/TR]
[TR]
[TD="align: right"]15000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]20000[/TD]
[TD] $ 442.50[/TD]
[/TR]
[TR]
[TD="align: right"]68000[/TD]
[TD="align: right"]22500[/TD]
[TD="align: right"]90500[/TD]
[TD] $ 2,006.00[/TD]
[/TR]
[TR]
[TD="align: right"]69000[/TD]
[TD="align: right"]22500[/TD]
[TD="align: right"]91500[/TD]
[TD] $ 2,035.50[/TD]
[/TR]
[TR]
[TD="align: right"]31000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]41000[/TD]
[TD] $ 914.50[/TD]
[/TR]
[TR]
[TD="align: right"]32000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]42000[/TD]
[TD] $ 944.00[/TD]
[/TR]
[TR]
[TD="align: right"]16000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]21000[/TD]
[TD] $ 472.00[/TD]
[/TR]
[TR]
[TD="align: right"]33000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]43000[/TD]
[TD] $ 973.50[/TD]
[/TR]
[TR]
[TD="align: right"]34000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]44000[/TD]
[TD] $ 1,003.00[/TD]
[/TR]
[TR]
[TD="align: right"]17000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]22000[/TD]
[TD] $ 501.50[/TD]
[/TR]
[TR]
[TD="align: right"]35000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]45000[/TD]
[TD] $ 1,032.50[/TD]
[/TR]
[TR]
[TD="align: right"]36000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]46000[/TD]
[TD] $ 1,062.00[/TD]
[/TR]
[TR]
[TD="align: right"]18000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]23000[/TD]
[TD] $ 531.00[/TD]
[/TR]
[TR]
[TD="align: right"]37000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]47000[/TD]
[TD] $ 1,091.50[/TD]
[/TR]
[TR]
[TD="align: right"]38000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]48000[/TD]
[TD] $ 1,121.00[/TD]
[/TR]
[TR]
[TD="align: right"]19000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]24000[/TD]
[TD] $ 560.50[/TD]
[/TR]
[TR]
[TD="align: right"]39000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]49000[/TD]
[TD] $ 1,150.50[/TD]
[/TR]
[TR]
[TD="align: right"]40000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]50000[/TD]
[TD] $ 1,180.00[/TD]
[/TR]
[TR]
[TD="align: right"]20000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]25000[/TD]
[TD] $ 590.00[/TD]
[/TR]
[TR]
[TD="align: right"]41000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]51000[/TD]
[TD] $ 1,209.50[/TD]
[/TR]
[TR]
[TD="align: right"]42000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]52000[/TD]
[TD] $ 1,239.00[/TD]
[/TR]
[TR]
[TD="align: right"]21000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]26000[/TD]
[TD] $ 619.50[/TD]
[/TR]
[TR]
[TD="align: right"]43000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]53000[/TD]
[TD] $ 1,268.50[/TD]
[/TR]
[TR]
[TD="align: right"]44000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]54000[/TD]
[TD] $ 1,298.00[/TD]
[/TR]
[TR]
[TD="align: right"]22000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]27000[/TD]
[TD] $ 649.00[/TD]
[/TR]
[TR]
[TD="align: right"]45000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]55000[/TD]
[TD] $ 1,327.50[/TD]
[/TR]
[TR]
[TD="align: right"]46000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]56000[/TD]
[TD] $ 1,357.00[/TD]
[/TR]
[TR]
[TD="align: right"]23000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]28000[/TD]
[TD] $ 678.50[/TD]
[/TR]
[TR]
[TD="align: right"]47000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]57000[/TD]
[TD] $ 1,386.50[/TD]
[/TR]
[TR]
[TD="align: right"]48000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]58000[/TD]
[TD] $ 1,416.00[/TD]
[/TR]
[TR]
[TD="align: right"]24000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]29000[/TD]
[TD] $ 708.00[/TD]
[/TR]
[TR]
[TD="align: right"]49000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]59000[/TD]
[TD] $ 1,445.50[/TD]
[/TR]
[TR]
[TD="align: right"]5000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]6000[/TD]
[TD] $ 147.50[/TD]
[/TR]
[TR]
[TD="align: right"]6000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]7000[/TD]
[TD] $ 177.00[/TD]
[/TR]
[TR]
[TD="align: right"]7000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]8000[/TD]
[TD] $ 206.50[/TD]
[/TR]
[TR]
[TD="align: right"]8000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]9000[/TD]
[TD] $ 236.00[/TD]
[/TR]
[TR]
[TD="align: right"]9000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]10000[/TD]
[TD] $ 265.50[/TD]
[/TR]
[TR]
[TD="align: right"]10000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]11000[/TD]
[TD] $ 295.00[/TD]
[/TR]
[TR]
[TD="align: right"]11000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]12000[/TD]
[TD] $ 324.50[/TD]
[/TR]
[TR]
[TD="align: right"]12000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]13000[/TD]
[TD] $ 354.00[/TD]
[/TR]
[TR]
[TD="align: right"]13000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]14000[/TD]
[TD] $ 383.50[/TD]
[/TR]
[TR]
[TD="align: right"]14000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]15000[/TD]
[TD] $ 413.00[/TD]
[/TR]
[TR]
[TD="align: right"]4000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4000[/TD]
[TD] $ 118.00[/TD]
[/TR]
[TR]
[TD="align: right"]3000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3000[/TD]
[TD] $ 88.50[/TD]
[/TR]
[TR]
[TD="align: right"]2000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2000[/TD]
[TD] $ 59.00[/TD]
[/TR]
[TR]
[TD="align: right"]1000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1000[/TD]
[TD] $ 29.50[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 462"]
<colgroup><col span="6"></colgroup><tbody>[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thanks

Andrew

 

[oldbrewer]

please restate the question with only 5 rows and 2 digit numbers

can you have any number of numbers in first column adding to the total
if so it is a lot of combinations

 

[aslawson]

Hello oldbrewer,

Please see a table below with 5 rows and max of 2 digit numbers.

As to the first column unfortunately it can have any number of combinations however typically there is only one solution that matches all the criteria and this is typically dependent upon the 4th column.


x=80
y < 20

10 2 12 3
20 3 23 6
30 4 34 9
40 5 45 12
50 10 60 15

 

[oldbrewer]

so 40,30,10 50,30 50,20,10 all sum to 80

so col 2 11 - 14 - 15

fourth column is 24, 24, 24

third column is 91, 94, 95

please restate your criteria in these terms

 

[aslawson]

So with this number set there were three possible options in the 1st column that summed to 80 as you identified.

Of these three possibilities I was then looking for the lowest value in column 4. (This scenario they are all the same)

At the same time look at column 3 for the highest value which happens to be 95.

therefore seeing that 95 is the highest identified then the option identifying rows 50 20 10 would be the solution to this problem

so 40,30,10 50,30 50,20,10 all sum to 80

so col 2 11 - 14 - 15

fourth column is 24, 24, 24

third column is 91, 94, 95

please restate your criteria in these terms

 

[oldbrewer]

what if column 4 was 24,24,23 and fourth column 91,94,95

does a single lowest value in column 4 take precedence over a single highest value in column ?