Extract Conditional Number List

[dunmore83]

Hi,

In a worksheet called 'Master-Cows' I have the following data;

Column B
- Unique list of numbers (no duplicates)
Column E
- list of numbers which contains duplicates

In worksheet 'Heifers-Steers-By-Age' I need a formula to extract the numbers from column B above.... however I need to list only the numbers from Column B that contain a 0 value in column E above. Any numbers in column B which have a value greater than 0 in column need to be ignored.

Any helpers ??

 

[Peter_SSs]

You didn't say where this is to go in 'Heifers-Steers-By-Age' so I have assumed cell A2 and below. If that is incorrect, change the red part of the formula to match the top cell of your list in 'Heifers-Steers-By-Age'

You will also need to edit the other ranges to match where your data extends to in 'Master-Cows'. I just used rows 2:10 in my sample.

=IFERROR(INDEX('Master-Cows'!B$2:B$10,AGGREGATE(15,6,(ROW('Master-Cows'!E$2:E$10)-ROW('Master-Cows'!E$2)+1)/('Master-Cows'!E$2:E$10=0),ROWS(A$2:A2))),"")

 

[dunmore83]

That's brilliant. Thanks!!

Only tweak is the formula is returning a 0 number value at the end of the list - can we make it return a blank (or "") ??

I'm not sure why it isn't currently as the IFERROR "" should be returning a blank but it is returning a 0

 

[Peter_SSs]

Only tweak is the formula is returning a 0 number value at the end of the list - can we make it return a blank (or "") ??

I'm not sure why it isn't currently as the IFERROR "" should be returning a blank but it is returning a 0

In the range of 'Master-Cows' that you provided for the formula, is there a row or rows (probably at the bottom) that is blank (or zero values) in column B and column E?
If so can your range be edited to exclude that row?
If that is not feasible, the formula can be modified.

 

[dunmore83]

Thanks.

I need the formula to be open ended as the range for column B & column E will vary.

Any suggestions ?

 

[Peter_SSs]

I need the formula to be open ended as the range for column B & column E will vary.

Try adding in the blue bit (match the range rows to the earlier part of the formula)

=IFERROR(INDEX('Master-Cows'!B$2:B$20,AGGREGATE(15,6,(ROW('Master-Cows'!E$2:E$20)-ROW('Master-Cows'!E$2)+1)/('Master-Cows'!E$2:E$20=0)/('Master-Cows'!B$2:B$20<>""),ROWS(A$2:A2))),"")