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

S

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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
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
Aladin Akyurek said:
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))),"")
Click to expand...

Ah yeah nice catch with the ISNUMBER($A$3:$A$12). I didn't think of that.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Table refresh removes Data Validation value from previous last row and pastes it to newly imported last row
Replies
0
Views
307
bdbilotta
B
E
  • Solved
Json Excel Square Brackets within Square Brackets Run-Time Error '1004'
Replies
3
Views
873
Domenic
D
H
  • Question Question
Macro to List Folders excluding Data Stamp within E:\Cobian
Replies
0
Views
73
howard
H
M
  • Solved
Dynamically Plot Data Points in Scatterplot
Replies
5
Views
681
felixstraube
F
R
  • Question Question
Spill array that only shows rows that have value in certain cells
Replies
1
Views
154
Alex Blakenburg
A

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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