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

suttonutd

Board Regular
Joined
Oct 5, 2010
Messages
68
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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?
 
Upvote 0
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?
 
Upvote 0
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.
[TABLE="width: 769"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]FX Realised[/TD]
[TD]WBS element[/TD]
[TD]Amount in doc. curr.[/TD]
[TD]Text[/TD]
[TD]Document Date[/TD]
[TD]Company Code[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]- 1.73[/TD]
[TD]912D00012818-22598[/TD]
[TD="align: right"]65[/TD]
[TD]658D00012818-22598//HDE01405139[/TD]
[TD="align: right"]25/07/2017[/TD]
[TD]912D[/TD]
[/TR]
[TR]
[TD]- 27.57[/TD]
[TD]912D00012891-22704[/TD]
[TD="align: right"]900[/TD]
[TD]658D00012891-22704//HDE01650281[/TD]
[TD="align: right"]28/07/2018[/TD]
[TD]912D[/TD]
[/TR]
[TR]
[TD]- 4.14[/TD]
[TD]912D00012891-22704[/TD]
[TD="align: right"]135[/TD]
[TD]658D00012891-22704//HDE01650281[/TD]
[TD="align: right"]28/07/2018[/TD]
[TD]912D[/TD]
[/TR]
[TR]
[TD] 10.20[/TD]
[TD]912D00011792-08561[/TD]
[TD="align: right"]3300[/TD]
[TD]658D00011792-08561//HDE01650281[/TD]
[TD="align: right"]28/07/2018[/TD]
[TD]912D[/TD]
[/TR]
[TR]
[TD] 50.20[/TD]
[TD]912D00011792-08561[/TD]
[TD="align: right"]405[/TD]
[TD]658D00011792-08561//HDE01650281[/TD]
[TD="align: right"]28/07/2018[/TD]
[TD]912D[/TD]
[/TR]
[TR]
[TD]- 27.57[/TD]
[TD]912D00012635-08282[/TD]
[TD="align: right"]900[/TD]
[TD]658D00012635-08282//HDE01650281[/TD]
[TD="align: right"]28/07/2018[/TD]
[TD]912D[/TD]
[/TR]
[TR]
[TD] 5.20[/TD]
[TD]912D00012635-08282[/TD]
[TD="align: right"]135[/TD]
[TD]658D00012635-08282//HDE01650281[/TD]
[TD="align: right"]28/07/2018[/TD]
[TD]912D[/TD]
[/TR]
[TR]
[TD]- 266.54[/TD]
[TD]912D00012186-22541[/TD]
[TD="align: right"]8700[/TD]
[TD]658D00012186-22541//HDE01650289[/TD]
[TD="align: right"]28/07/2018[/TD]
[TD]912D[/TD]
[/TR]
[TR]
[TD]- 33.70[/TD]
[TD]912D00012819-22599[/TD]
[TD="align: right"]1100[/TD]
[TD]658D00012819-22599//HDE01650281[/TD]
[TD="align: right"]28/07/2018[/TD]
[TD]912D[/TD]
[/TR]
[TR]
[TD] 4.14[/TD]
[TD]912D00012819-22599[/TD]
[TD="align: right"]135[/TD]
[TD]658D00012819-22599//HDE01650281[/TD]
[TD="align: right"]28/07/2018[/TD]
[TD]912D[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]Desired Output (Only selecting positive values)[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]WBS element[/TD]
[TD]Amount in doc. curr.[/TD]
[TD]Text[/TD]
[TD]Company Code[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]912D00011792-08561[/TD]
[TD="align: right"]3300[/TD]
[TD]658D00011792-08561//HDE01650281[/TD]
[TD]912D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]912D00011792-08561[/TD]
[TD="align: right"]405[/TD]
[TD]658D00011792-08561//HDE01650281[/TD]
[TD]912D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]912D00012635-08282[/TD]
[TD="align: right"]135[/TD]
[TD]658D00012635-08282//HDE01650281[/TD]
[TD]912D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]912D00012819-22599[/TD]
[TD="align: right"]135[/TD]
[TD]658D00012819-22599//HDE01650281[/TD]
[TD]912D[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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.
 
Last edited:
Upvote 0
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))),"")
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top