Listing numbers conditionally

[Usercode]

I need a formula to separate and list desired numbers in a certain range from the data i have.

For example, if my data is 4,7,15,19,35,41,8,51,56,64,65,71,75,76,42,38 in A2:P2, then if I want to separate and list the numbers between 50 and 59, the desired outcome should be 51,56 in U2:V2. I attached an example file. It would be good to have the numbers in ascending order. Thanks in advance.

Example file: https://1drv.ms/x/s!AoGkZUHlKui9gR44xEi-kQBlTFs1

 

[gfranco]

Hello.
See if the attachment can help you

https://drive.google.com/file/d/1K8DGRnkFWRBjplEADbAq3xZ_XiGr-3FZ/view?usp=sharing

 

[Usercode]

Thanks for your solution, gfranco. I have a small problem. in this example the data row has 16 cells (A:P) , when there are less cells or more, the formula doesn't give any outcome. What should i do about this?

 

[Special-K99]

This works if you can use AGGREGATE

=IFERROR(INDEX($A2:$P$2,1,AGGREGATE(15,6,COLUMN($A2:P2)/(($A2:$P2>=50)*($A2:$P2<=59)),COLUMN(A2))),"")
Array formula, use Ctrl-Shift-Enter
copy across the columns from U2 onwards

 

[Usercode]

I tried your formula, but it didn't give any outcomes. it displays blank cells. I did Ctrl-Shift-Enter. In your case, does it work when there are less data like A to K?

 

[István Hirsch]

Try this. Enter into cell U2, confirm with Ctrl + Shift + Enter, then copy across and down:

=IFERROR(SMALL(IF($A$2:$P$2<(ROWS($A$2:A2)*10)*($A$2:$P$2>(ROWS($A$2:A2)-1)*10),$A$2:$P$2),COLUMNS($U2:U2)),"")

 

[Special-K99]

I tried your formula, but it didn't give any outcomes. it displays blank cells. I did Ctrl-Shift-Enter. In your case, does it work when there are less data like A to K?

My formula works perfectly when entered with Ctrl-Shift-Enter on your example file.

 

[Usercode]

Thanks Istvan. With this formula I got the results for numbers from 1-9. How can I specify the number range in this formula? If i want to numbers from 50 to 59, what should I change here?

 

[Usercode]

My formula works perfectly when entered with Ctrl-Shift-Enter on your example file.

I am sure it works, but I couldn't run it. I don't know why. I have Ms Excel 2007.

 

[István Hirsch]

Ref. post #6 :

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2	4	7	15	19	35	41	8	51	22	64	65	71	75	76	42	38					4	7	8
3																					15	19
4																					22
5																					35	38
6																					41	42
7																					51
8																					64	65
9																					71	75	76
Sheet1