Remove values with data missing

[freeriding]

Hallo, i want to extract the columns E and O from the sheet 'work' only for the cells that the corresponding Y column cell is blank.

(The formula that i have in sheet1, puts the correct cells one after another.)

To remove the cells that are blank in Y column AND are blank also in E column, i was thinking to replace the small function with this: SMALL(IF(AND(WORK!$Y$13:$Y$45="";WORK!$E$13:$E$45<>"")))

This doesn't seem to work.

Any help?

[shukla.ankur281190]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[davsth]

adjusting your formula perhaps
=IFERROR(INDEX(WORK!$E$13:$E$45,SMALL(IF(WORK!$Y$13:$Y$45="",IF(WORK!$E$13:$E$45<>"",ROW(WORK!$E$13:$E$45)-ROW(WORK!$E$13)+1)),ROWS(WORK!$E$13:$E13))),"") as an array

[Glenn Kennedy]

How can you bear to look at that colour scheme! It makes my head hurt....

=IFERROR(INDEX(WORK!E:E,AGGREGATE(15,6,ROW(WORK!$E$13:$E$43)/(WORK!$Y$15:$Y$43=""),ROWS(C$4:C4))),"")

and

=IFERROR(INDEX(WORK!O:O,AGGREGATE(15,6,ROW(WORK!$E$13:$E$43)/(WORK!$Y$15:$Y$43=""),ROWS(C$4:C4))),"")

entered normally. However there are still a few blank values as Column Y=blank is not sufficient, on its own, to remove the non-values in the area between rows 22-25

[freeriding]

Guys i really can't think of a better title, cause my problem is very delicate and detailed.

[Glenn Kennedy]

=IFERROR(INDEX(WORK!E:E,AGGREGATE(15,6,ROW(WORK!$E$13:$E$43)/((WORK!$Y$15:$Y$43="")*(WORK!$E$13:$E$43<>"")),ROWS(C$4:C4))),"")

and

=IFERROR(INDEX(WORK!O:O,AGGREGATE(15,6,ROW(WORK!$E$13:$E$43)/((WORK!$Y$15:$Y$43="")*(WORK!$E$13:$E$43<>"")),ROWS(D$4:D4))),"")

in C4 and d4, respectively.

[Glenn Kennedy]

I have changed the title for you, on this occasion.

[freeriding]

I changed the title, hope it is ok.

[Glenn Kennedy]

See post 6 and forget about the title. It's not THAT important...

[freeriding]

Thank you very much!
Is there a way to include in the formula this situation:
if the E column cell contains data, and the O column cell is blank, not to transfer it in sheet1?

[Glenn Kennedy]

Confused again.... Isn't that what the formulae at post 6 are doing????

[Glenn Kennedy]

Sorry. Confusion is in my head. Too many double negatives...

If E is blank and O is non-blank and Y is???? copy it over. Or what????

Tell me the criteria FOR copying it over.

[freeriding]

See cell D8 in sheet1.

[freeriding]

If E isn't blank, Y is blank and O is blank, then don't transfer in sheet1.

[Glenn Kennedy]

That does NOT make sense. the formula in your sheet in D is copying over non-blanks in E. But you say if e is non blank don't copy.

PLEASE check and tell me what you DO want copied over. do not tell me what you DO NOT want copied over. Otherwisde we are going to get VERY confused. You have done that to me already.

[freeriding]

Thank you, all good, i solved it.
=IFERROR(INDEX(WORK!E:E;AGGREGATE(15;6;ROW(WORK!$E$13:$E$43)/((WORK!$Y$13:$Y$43="")*(WORK!$E$13:$E$43<>"")*(WORK!$O$13:$O$43<>""));ROWS(C$4:C4)));"")