Pull Values based on 4 CRITERIAS

[hsandeep]

Data Range is B2:M4 in 12 ‘continuous’ columns AND these values in a row are EITHER INCREASING OR DECREASING from B to M

Output required in 12 ‘continuous’ columns T2:AE4

4 Criteria’s are:
1. O2 is All values in T2:AE2 MUST BE LESS THAN O2
2. P2 is ‘this much’ number of cells must be considered from LEFT HAND SIDE to RIGHT
3. Q2 is ‘this much’ number of cells must be considered from RIGHT HAND SIDE to LEFT. Q2 is always > P2
4. R2 is cell ‘number’ in the Data Range B:M which MUST BE CONSIDERED (HIGHEST PRIORITY)
5. Else remaining / all cells SHOULD BE 0

How to accomplish? This is very difficult for me…..hence….
Thanks in advance
I am using Excel 2007

Sheet1

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

Min Value

Min

Max

Compulsory

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 6px"><col style="WIDTH: 70px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 81px"><col style="WIDTH: 6px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #ffcc00, align: center"]1[/TD]
[TD="bgcolor: #ffcc00, align: center"]2[/TD]
[TD="bgcolor: #ffcc00, align: center"]3[/TD]
[TD="bgcolor: #ffcc00, align: center"]4[/TD]
[TD="bgcolor: #ffcc00, align: center"]5[/TD]
[TD="bgcolor: #ffcc00, align: center"]6[/TD]
[TD="bgcolor: #ffcc00, align: center"]7[/TD]
[TD="bgcolor: #ffcc00, align: center"]8[/TD]
[TD="bgcolor: #ffcc00, align: center"]9[/TD]
[TD="bgcolor: #ffcc00, align: center"]10[/TD]
[TD="bgcolor: #ffcc00, align: center"]11[/TD]
[TD="bgcolor: #ffcc00, align: center"]12[/TD]

[TD="bgcolor: #ffcc00, align: center"]1[/TD]
[TD="bgcolor: #ffcc00, align: center"]2[/TD]
[TD="bgcolor: #ffcc00, align: center"]3[/TD]
[TD="bgcolor: #ffcc00, align: center"]4[/TD]
[TD="bgcolor: #ffcc00, align: center"]5[/TD]
[TD="bgcolor: #ffcc00, align: center"]6[/TD]
[TD="bgcolor: #ffcc00, align: center"]7[/TD]
[TD="bgcolor: #ffcc00, align: center"]8[/TD]
[TD="bgcolor: #ffcc00, align: center"]9[/TD]
[TD="bgcolor: #ffcc00, align: center"]10[/TD]
[TD="bgcolor: #ffcc00, align: center"]11[/TD]
[TD="bgcolor: #ffcc00, align: center"]12[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]888[/TD]
[TD="align: right"]999[/TD]
[TD="align: right"]1000[/TD]

[TD="align: right"]65[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: #ffff00, align: right"]10[/TD]
[TD="bgcolor: #ffff00, align: right"]20[/TD]
[TD="bgcolor: #ffff00, align: right"]30[/TD]
[TD="bgcolor: #ffff00, align: right"]40[/TD]
[TD="bgcolor: #ffff00, align: right"]50[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]

[TD="align: right"]81[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: #ffff00, align: right"]5[/TD]
[TD="bgcolor: #ffff00, align: right"]8[/TD]
[TD="bgcolor: #ffff00, align: right"]12[/TD]
[TD="bgcolor: #ffff00, align: right"]80[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>

 

[Peter_SSs]

Just a few comments/questions.

1. Being a member here for over 10 years with nearly 900 posts, surely you could be giving us sample data in a form that can be copied by helpers to test with instead of an image that we can do nothing with? I certainly don't feel like typing out over 100 cell values to test.

2. You said B:M can be decreasing but you did not include such an example. That probably would have been helpful.

3. I don't understand the requirements.

 

[hsandeep]

Just a few comments/questions.

1. Being a member here for over 10 years with nearly 900 posts, surely you could be giving us sample data in a form that can be copied by helpers to test with instead of an image that we can do nothing with? I certainly don't feel like typing out over 100 cell values to test. Sample data in a form…..I don’t know how to upload it on this site….else I would have done & would do it in future.

2. You said B:M can be decreasing but you did not include such an example. That probably would have been helpful.Yes it would have. Now it can be only visualized

3. I don't understand the requirements.

I’ll try to explain with row3:
1. O3=81 so all values in T3:AE3 MUST BE LESS THAN 81…so all 12 values of B3:M3 ‘qualified’
2. P3=2 so the formula should ‘grab‘ B3 & C3 (2 cells from LEFT HAND SIDE) so T3=5 & U3=8
3. Q3=4 so the formula should FILL MAXIMUM 4 cells in T3:AE3 i.e. ONLY T3:W3 (4 cells) will get values rest (X3:AE3 will be 0)
4. R3=4 means 4^th cell from B3:M3 i.e. E3 MUST GET in the range T3:AE3 provided upper 3 criteria’s are met.

5. So, eventually, first T3=5 & U3=8

6. The formula should FILL 4 cells (in total) so V3 & W3 may get values (other than 0)

7. R3=4 so E3 (12) is COMPULSORY…& it meets the criteria’s

8. Lastly Q3=4 will allow ONLY T3:W3 (4 cells) TO GET values rest (X3:AE3 will be 0)…so after point# 6 there are 2 cells left (T3 & U3 earlier got values) to get values ….so V3 & W3 will get values

9. Due to point# 7: V3=12

10. Point# 1: will allow the NEAREST (to 81) value left in the range i.e. 80 to get FILLED in W3

 

[Peter_SSs]

That is somewhat clearer, thanks.

10. Point# 1: will allow the NEAREST (to 81) value left in the range i.e. 80 to get FILLED in W3

That's true but point 1 would allow any of the values in the left range to be used.

Further, nothing specifies where the compulsory cell must go.

Given the above 2 points, it seems to me that the logic would also allow these results for row 3.

5,8,9,12
5,8,12,15
5,8,12,50
etc

Would a macro solution be acceptable if not feasible with formulas?

(Still no example of decreasing values & no sample data to test with)

 

[hsandeep]

That is somewhat clearer, thanks.

That's true but point 1 would allow any of the values in the left range to be used.

Further, nothing specifies where the compulsory cell must go.

Given the above 2 points, it seems to me that the logic would also allow these results for row 3.

5,8,9,12
5,8,12,15
5,8,12,50
etc

Would a macro solution be acceptable if not feasible with formulas?

(Still no example of decreasing values & no sample data to test with)

Yes BUT it MUST BE LESS THAN column O

Good question:
Please note Values in T2:AE4 must be filled starting LEFT TO RIGHT i.e. T than U than V & so on till AE
Compulsory cell value: is based on fulfillment of 3 criteria’s
In the example: Row# 3

Criteria#4: R3=12 so 12 MUST BE THE VALUE which must ‘find its place’ in the output range T3:AE3, if at all, ELSE all T3:AE3 would be filled with 0….location of 12 in T3:AE3 remains at this moment…

Criteria#2: P3=2 enables FIRST 2 cells B3 & C3 (from B3:M3). Now, B3(5) & C3(8)……& 5< O3(81) satisfies Criteria#1 & 8< O3(81) satisfying Criteria#1

Criteria#3: Q3=4 enables LAST 4 cells i.e. M3, L3, K3 & J3 (from B3:M3)….provided Criteria#1 is satisfied. But at this moment we would find that B3(5) & C3(8) have ALREADY QUALIFIED so left with LAST 2 cells i.e. M3(80, L3(70)

At this moment it looks like T3=5, U3=8, V3=70, X3=80

But Compulsory cell value SHOULD FORCE 12 to ‘find its place’ in the output range T3:AE3

FINALLY, ANSWER would be T3=5, U3=8, V3=12, X3=80

NO 9 does not qualifies Criteria#2 (it STARTS at B3 & STOPS at C3) Neither Criteria#3 (it STARTS at M3 & STOPS at K3)

NO 15 does not qualifies Criteria#2 (it STARTS at B3 & STOPS at C3) Neither Criteria#3 (it STARTS at M3 & STOPS at K3)

NO 50 does not qualifies Criteria#2 (it STARTS at B3 & STOPS at C3) BUT IT QUALIFIES Criteria#3 (it STARTS at M3 & STOPS at K3) but places are already booked since B3(5), C3(8), R3(12) M3(80) would find its place

Macro is not feasible…FORMULA is required……you may ADD ‘helper’ cells / columns as much as you require

I had replied earlier also….Sample data in a form…..I don’t know how to upload it on this site….else I would have done & would do it in future.

 

[Peter_SSs]

My computer or browser must have been acting up the other day as I could not copy your data from post 1. Now I can so I apologise for my comments in this thread about sample data.

However, now that I have been able to copy it, I am still unable to come up with a viable solution to the problem.

 

[hsandeep]

My computer or browser must have been acting up the other day as I could not copy your data from post 1. Now I can so I apologise for my comments in this thread about sample data.
No need to apologize…….How was the lunch with the family

However, now that I have been able to copy it, I am still unable to come up with a viable solution to the problem.

Solution would help me. I am doing every time manually BUT this ‘drains’ my mind & concentration since the values in B2:M4 & Column O changes fast