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
1	Date	Group1	Group2	Group3	Group4	Group5	Group6	Group7	Group8	Group9	*	Date	Extracted Values
2	1/1/2016	15	31	40	28	71	58	68	77	48	*	1/1/2016	*
3	1/2/2016	16	6	71	84	7	64	66	28	24	*	1/2/2016	*
4	1/3/2016	67	47	44	4	52	58	14	44	72	*	1/3/2016	*
5	1/4/2016	59	95	21	0	31	9	20	81	0	*	1/4/2016	95
6	1/5/2016	90	50	25	63	82	8	20	16	89	*	1/5/2016	90
7	1/6/2016	70	4	60	51	46	800	400	6	29	*	1/6/2016	*
8	1/7/2016	14	78	21	18	12	48	59	17	33	*	1/7/2016	*
9	1/8/2016	59	51	82	98	0	20	120	17	0	*	1/8/2016	98
10	1/9/2016	70	23	46	2	54	85	36	53	58	*	1/9/2016	*
11	1/10/2016	1	9	18	68	35	63	82	68	899	*	1/10/2016	*
12	1/11/2016	30	32	95	76	2	7	59	57	38	*	1/11/2016	95
13	1/12/2016	15	33	7	94	28	89	1	19	21	*	1/12/2016	94
14	1/13/2016	68	4	3	36	9	91	46	45	47	*	1/13/2016	91
15	1/14/2016	15	36	41	71	23	15	94	9	64	*	1/14/2016	94
16	1/15/2016	79	89	27	4	25	25	40	95	32	*	1/15/2016	95
17	1/16/2016	61	21	62	58	17	32	26	69	7	*	1/16/2016	*
18	1/17/2016	55	77	82	20	21	55	66	33	71	*	1/17/2016	*
19	1/18/2016	3	71	68	35	6	62	76	3	27	*	1/18/2016	*
20	1/19/2016	3	43	3	98	75	69	48	78	3	*	1/19/2016	98
21	1/20/2016	3	61	28	35	83	72	96	46	16	*	1/20/2016	96
22	1/21/2016	4	39	28	25	24	91	3	30	49	*	1/21/2016	91

<tbody>
</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

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

<tbody>
</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