Formula question

[edorminey]

Hi, I'm still learning Excel and need help with a formula.

I have a column T
I have positive numbers scattered out in 5 cells in the range T2:T100
(I have a formula in range T2:T100 that returns "" if the result is = 0 so most of the cells are blank except for the hidden formula)

I need to find the cells that are > 0 in range T2:T100 and put them in T105:T110 and also give me the values in column S in S105:S110.

Hope this is clear and thanks for your help.

 

[edorminey]

Should be perfect. I'm headed to bed, but will check in the morning. Thanks for all your help again.

 

[edorminey]

Well, that almost works perfect.

When I use that formula, it gives me the correct answers, but it skips rows between the answers. How do I give my result without skipped rows?

instead of :
4


5

2

I would like to have
4
5
2

 

[Aladin Akyurek]

Well, that almost works perfect.

When I use that formula, it gives me the correct answers, but it skips rows between the answers. How do I give my result without skipped rows?

instead of :
4


5

2

I would like to have
4
5
2

P2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($R$2:$R$100,
  SMALL(IF(ISNA($T$2:$T$100),IF($R$2:$R$100<>"",ROW($T$2:$T$100)-ROW($T$2)+1)),
  ROWS($P$2:P2))),"")

 

[edorminey]

That works perfect.

Is there a way to automatically sort the array or do you have to manually sort it.

 

[Aladin Akyurek]

That works perfect.

Is there a way to automatically sort the array or do you have to manually sort it.

I wouldn't bother if the output is not numeric.

 

[edorminey]

This first part is part of my actual data. Basically, you have me where I can pull the number in the second column out if the number in the fourth column is = #N/A

The numbers in the third column signify the correct order when they are sorted as in the second example.

When I get the numbers from the second column I would like to be able to sort them, but since they aren't sortable names, I was going to use the third column to sort them. Is that as clear as mud?

How do I pull out the second column result, (such as A1 and put that in one column and then pull out the matching numbers in column three and then sort on column three. Any way to do that?

7834458	A9	7
11179099	A4	2
11305063	A1	0.5	#N/A
11305877	A15	9	#N/A
11305905	D1	33
11305918	A6	4
11306014	B9	16
11306033	C4	23	#N/A

<tbody>
</tbody>

11305063	A1	0.5	#N/A
11179099	A4	2
11305918	A6	4
7834458	A9	7
11305877	A15	9	#N/A
11306014	B9	16
11306033	C4	23	#N/A
11305905	D1	33

<tbody>
</tbody>