Extract Data From Table vertically according to tow criterias

[Excel777]

hello,

i want to get data from first table vertically depending on two criterias value between 90,100
wish find non array formula
results coulmn M
taking in consinderation if i have two values on the same date the criteria applied in it it extract them.
thanks in advance for help
Sheet1

*

A

B

C

D

E

F

G

H

I

J

K

L

M

Group1

Group2

Group3

Group4

Group5

Group6

Group7

Group8

Group9

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Date[/TD]

[TD="align: center"]Date[/TD]
[TD="align: center"]Extracted Values[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]1/1/2016[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]71[/TD]
[TD="align: center"]58[/TD]
[TD="align: center"]68[/TD]
[TD="align: center"]77[/TD]
[TD="align: center"]48[/TD]

[TD="align: center"]1/1/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]1/2/2016[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]71[/TD]
[TD="align: center"]84[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]64[/TD]
[TD="align: center"]66[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]24[/TD]

[TD="align: center"]1/2/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]1/3/2016[/TD]
[TD="align: center"]67[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]52[/TD]
[TD="align: center"]58[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"]72[/TD]

[TD="align: center"]1/3/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]1/4/2016[/TD]
[TD="align: center"]59[/TD]
[TD="bgcolor: #a9d08e, align: center"]95[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]81[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]1/4/2016[/TD]
[TD="bgcolor: #a9d08e, align: center"]95[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]1/5/2016[/TD]
[TD="bgcolor: #a9d08e, align: center"]90[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]63[/TD]
[TD="align: center"]82[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]89[/TD]

[TD="align: center"]1/5/2016[/TD]
[TD="bgcolor: #a9d08e, align: center"]90[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: center"]1/6/2016[/TD]
[TD="align: center"]70[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]51[/TD]
[TD="align: center"]46[/TD]
[TD="align: center"]800[/TD]
[TD="align: center"]400[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]29[/TD]

[TD="align: center"]1/6/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: center"]1/7/2016[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]78[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]48[/TD]
[TD="align: center"]59[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]33[/TD]

[TD="align: center"]1/7/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: center"]1/8/2016[/TD]
[TD="align: center"]59[/TD]
[TD="align: center"]51[/TD]
[TD="align: center"]82[/TD]
[TD="bgcolor: #a9d08e, align: center"]98[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]120[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]1/8/2016[/TD]
[TD="bgcolor: #a9d08e, align: center"]98[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: center"]1/9/2016[/TD]
[TD="align: center"]70[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]46[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]54[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"]53[/TD]
[TD="align: center"]58[/TD]

[TD="align: center"]1/9/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: center"]1/10/2016[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]68[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]63[/TD]
[TD="align: center"]82[/TD]
[TD="align: center"]68[/TD]
[TD="align: center"]899[/TD]

[TD="align: center"]1/10/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: center"]1/11/2016[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]32[/TD]
[TD="bgcolor: #a9d08e, align: center"]95[/TD]
[TD="align: center"]76[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]59[/TD]
[TD="align: center"]57[/TD]
[TD="align: center"]38[/TD]

[TD="align: center"]1/11/2016[/TD]
[TD="bgcolor: #a9d08e, align: center"]95[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: center"]1/12/2016[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"]7[/TD]
[TD="bgcolor: #a9d08e, align: center"]94[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]89[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]21[/TD]

[TD="align: center"]1/12/2016[/TD]
[TD="bgcolor: #a9d08e, align: center"]94[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: center"]1/13/2016[/TD]
[TD="align: center"]68[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"]9[/TD]
[TD="bgcolor: #a9d08e, align: center"]91[/TD]
[TD="align: center"]46[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]47[/TD]

[TD="align: center"]1/13/2016[/TD]
[TD="bgcolor: #a9d08e, align: center"]91[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: center"]1/14/2016[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"]41[/TD]
[TD="align: center"]71[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]15[/TD]
[TD="bgcolor: #a9d08e, align: center"]94[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]64[/TD]

[TD="align: center"]1/14/2016[/TD]
[TD="bgcolor: #a9d08e, align: center"]94[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: center"]1/15/2016[/TD]
[TD="align: center"]79[/TD]
[TD="align: center"]89[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]40[/TD]
[TD="bgcolor: #a9d08e, align: center"]95[/TD]
[TD="align: center"]32[/TD]

[TD="align: center"]1/15/2016[/TD]
[TD="bgcolor: #a9d08e, align: center"]95[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: center"]1/16/2016[/TD]
[TD="align: center"]61[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]62[/TD]
[TD="align: center"]58[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]69[/TD]
[TD="align: center"]7[/TD]

[TD="align: center"]1/16/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: center"]1/17/2016[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]77[/TD]
[TD="align: center"]82[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]66[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"]71[/TD]

[TD="align: center"]1/17/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: center"]1/18/2016[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]71[/TD]
[TD="align: center"]68[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]62[/TD]
[TD="align: center"]76[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]27[/TD]

[TD="align: center"]1/18/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: center"]1/19/2016[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]43[/TD]
[TD="align: center"]3[/TD]
[TD="bgcolor: #a9d08e, align: center"]98[/TD]
[TD="align: center"]75[/TD]
[TD="align: center"]69[/TD]
[TD="align: center"]48[/TD]
[TD="align: center"]78[/TD]
[TD="align: center"]3[/TD]

[TD="align: center"]1/19/2016[/TD]
[TD="bgcolor: #a9d08e, align: center"]98[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD="align: center"]1/20/2016[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]61[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]83[/TD]
[TD="align: center"]72[/TD]
[TD="bgcolor: #a9d08e, align: center"]96[/TD]
[TD="align: center"]46[/TD]
[TD="align: center"]16[/TD]

[TD="align: center"]1/20/2016[/TD]
[TD="bgcolor: #a9d08e, align: center"]96[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD="align: center"]1/21/2016[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]39[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]24[/TD]
[TD="bgcolor: #a9d08e, align: center"]91[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]49[/TD]

[TD="align: center"]1/21/2016[/TD]
[TD="bgcolor: #a9d08e, align: center"]91[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Peter_SSs]

You were wanting to do this without array formulas, though I'm not sure why. The following much shorter formula is really an array formula but does not require the Ctrl+Shift+Enter confirmation, though it does require Excel 2010 or later:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Date	Group1	Group2	Group3	Group4	Group5	Group6	Group7	Group8	Group9		Date	Values
2	1/1/2016	40	93	50	100	98	14	70	19	50		1/1/2016	40	50	50	93	98	100
Check values (3)

 

[Excel777]

Million of thanks for your apnswer your short last formula will be very helpful for me coz I'll add more criteria till 9 criteria is the last formula accept that

 

[Peter_SSs]

Million of thanks for your apnswer your short last formula will be very helpful for me coz I'll add more criteria till 9 criteria is the last formula accept that

Cheers.
You can also write it a bit shorter like this, where the 45 and 95 are the exact mid-point of each range of interest and the 5s are the distance from that mid-point to either end-point of the range.
Note that there is no particular advantage in doing this, just choose the one you understand the best & can modify yourself the easiest.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Date	Group1	Group2	Group3	Group4	Group5	Group6	Group7	Group8	Group9		Date	Values
2	1/1/2016	40	93	50	100	98	14	70	19	50		1/1/2016	40	50	50	93	98	100
Check values (4)

 

[Excel777]

Actually it's 100 apart
First criteria between 100,110
Second one between 200,210
And so on till 1000,1010
Can you program this for me.

 

[Peter_SSs]

Actually it's 100 apart
First criteria between 100,110
Second one between 200,210
And so on till 1000,1010
Can you program this for me.

For a regular pattern like that, it is much simpler:

=IFERROR(AGGREGATE(15,6,$B2:$J2/(MOD($B2:$J2,100)<=10),COLUMNS($M2:M2)),"")


or depending on how big/small the actual values can be, you might need

=IFERROR(AGGREGATE(15,6,$B2:$J2/((MOD($B2:$J2,100)<=10)*($B2:$J2>10)*($B2:$J2<1100)),COLUMNS($M2:M2)),"")

 

[Excel777]

Dear Mr Peter Million Of Thanks For Your Smart Answer You Are Really MR EXCEL ,
Your Answers Helped Me Alot
God Bless,

 

[Peter_SSs]

Thank you very much for your generous comments.

 

[Excel777]

hello dear i need a help if possible
need to ignore the arrange small to large or large to small in the criteria range in other words get the values according to their occurrence in the criteria range

in our example the results will be start at m2



40 , 50 , 50 , 93 , 100 , 98

note that 100 occur before 98 in it's criteria range so it should be placed first

the quoted formula is very helpful so need the modification in it coz it allows me to add more criterias easilys

You were wanting to do this without array formulas, though I'm not sure why. The following much shorter formula is really an array formula but does not require the Ctrl+Shift+Enter confirmation, though it does require Excel 2010 or later:

Check values (3)

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]Date[/TD]
[TD="align: right"]Group1[/TD]
[TD="align: right"]Group2[/TD]
[TD="align: right"]Group3[/TD]
[TD="align: right"]Group4[/TD]
[TD="align: right"]Group5[/TD]
[TD="align: right"]Group6[/TD]
[TD="align: right"]Group7[/TD]
[TD="align: right"]Group8[/TD]
[TD="align: right"]Group9[/TD]

[TD="align: right"]Date[/TD]
[TD="align: center"]Values[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]1/1/2016[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]50[/TD]

[TD="align: right"]1/1/2016[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]93[/TD]
[TD="align: center"]98[/TD]
[TD="align: center"]100[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula M2 =IFERROR(AGGREGATE(15,6,$B2:$J2/(($B2:$J2>=40)*($B2:$J2<=50)+($B2:$J2>=90)*($B2:$J2<=100)),COLUMNS($M2:M2)),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Peter_SSs]

40 , 50 , 50 , 93 , 100, 98

What is your actual criteria?
- Is it 40-50 and 90-100 like the above example appears, and as your earlier posts requested?
- Or is it as below?

Actually it's 100 apart
First criteria between 100,110
Second one between 200,210
And so on till 1000,1010

Please decide what your final requirement is (which numbers and what order) and explain that clearly so we don't have keep going back and changing the formula each time you decide on something different.

 

[Excel777]

Sorry for that,

I need the final results in that order 40,50,50,93,100,98 by using aggregate function.... this is the final request