Pluck out positive values and it's respective data within an array

[suttonutd]

Hello excel guru's
So from row 18 to Row 24 I have a transaction listing going across many columns and within ColB on those rows, I have values like this:
-7.73
-27.57
5.52
12.40
-4.14
266.54
-42.40

From Row 60, I would like to populate bits of data from these transactions but only from the rows which have positive values within ColB. So with the example above, only want to extract bits of data from the rows which have 5.52 / 12.40 / 266.54. I can obviously do this by sorting the data first and doing vlookups but wondered if there was a more advanced way with some clever formulas.

Please advise! Thanks Steve

 

[Aladin Akyurek]

Specifications "from row 18 to Row 24" and "From Row 60" plus "across many columns" and "within ColB on those rows" are hard to understand. Are you not just after the column B numbers which are > 0?

 

[suttonutd]

I'll try again! I have a list of transactions on Row 18 to Row 27. Each transaction has around 20 different fields.

From Row 60, I want to populate only a certain number of those fields for any rows in the above range which have a positive value within ColB.

Make sense?

 

[Aladin Akyurek]

try to post a small sample along with the desired output for that sample.

 

[suttonutd]

Not sure if this makes any more sense. First table is the data, 2nd table is the output. I'm trying to pluck out data on rows where FX realised is a positive value. This is only sample, the data has around 20 fields and I'll need to pull out a lot more data.

FX Realised	WBS element	Amount in doc. curr.	Text	Document Date	Company Code
- 1.73	912D00012818-22598	65	658D00012818-22598//HDE01405139	25/07/2017	912D
- 27.57	912D00012891-22704	900	658D00012891-22704//HDE01650281	28/07/2018	912D
- 4.14	912D00012891-22704	135	658D00012891-22704//HDE01650281	28/07/2018	912D
10.20	912D00011792-08561	3300	658D00011792-08561//HDE01650281	28/07/2018	912D
50.20	912D00011792-08561	405	658D00011792-08561//HDE01650281	28/07/2018	912D
- 27.57	912D00012635-08282	900	658D00012635-08282//HDE01650281	28/07/2018	912D
5.20	912D00012635-08282	135	658D00012635-08282//HDE01650281	28/07/2018	912D
- 266.54	912D00012186-22541	8700	658D00012186-22541//HDE01650289	28/07/2018	912D
- 33.70	912D00012819-22599	1100	658D00012819-22599//HDE01650281	28/07/2018	912D
4.14	912D00012819-22599	135	658D00012819-22599//HDE01650281	28/07/2018	912D
	Desired Output (Only selecting positive values)
	WBS element	Amount in doc. curr.	Text	Company Code
	912D00011792-08561	3300	658D00011792-08561//HDE01650281	912D
	912D00011792-08561	405	658D00011792-08561//HDE01650281	912D
	912D00012635-08282	135	658D00012635-08282//HDE01650281	912D
	912D00012819-22599	135	658D00012819-22599//HDE01650281	912D

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Finalfight40]

Was hard for me to understand the layout and how large your table was so you will have to adjust the formula. I just copied the table above and pasted into A1.

The formula can be copied across and down and was entered into the first WBS Element desired result cell:

=IFERROR(INDEX(B$3:B$12, SMALL(IF($A$3:$A$12>0, ROW($A$3:$A$12)-2, ""), ROWS($B$17:B17))), "")

This is an array formula so will need to be entered with control + shift + enter.

 

[suttonutd]

Amazing thank you, a true guru

 

[Finalfight40]

You're Welcome

 

[Aladin Akyurek]

It should be rather:

=IFERROR(INDEX(B$3:B$12,SMALL(IF(ISNUMBER($A$3:$A$12),IF($A$3:$A$12>0,ROW($A$3:$A$12)-ROW($A$3)+1)),ROWS($B$17:B17))),"")

 

[Finalfight40]

It should be rather:

=IFERROR(INDEX(B$3:B$12,SMALL(IF(ISNUMBER($A$3:$A$12),IF($A$3:$A$12>0,ROW($A$3:$A$12)-ROW($A$3)+1)),ROWS($B$17:B17))),"")

Ah yeah nice catch with the ISNUMBER($A$3:$A$12). I didn't think of that.