Formula Help: If X is True, then display all of row 1?

[Rick-O-Shay]

Hi,

I have a workbook example attached.

I am trying to make a formula in A1 (on Sheet 2) that says something like: If (all of this data is in Sheet 1) column D is False, then copy all of that row into sheet 2. If D1 is True, nothing should be copied, skip that data row and move on.

Maybe a formula is the incorrect method here, as I image I need to write formula in each cell, but I want to avoid large gaps of empty rows.

Basically, what I am trying to achieve is I have a large log file dumped into excel, in a column that says True or False, I want to basically filter any data that is False and I need to review all data in that entire row. Similar to filtering.

[GlennUK]

Explain why you cannot just filter?

[dosydos]

Explain why you cannot just filter?

i'm sure he wants a filter so that something is automated and less work in the future

[dosydos]

Put this formula in cell A1.
This is an array formula so you must use CTRL + SHIFT + ENTER when entering this formula. If done so correctly { } will automatically be inserted around the formula.

As of right now i have the range set to row 50. Please update the range to fit your needs. I have highlighted this in RED below.

=IFERROR(INDEX(Sheet1!A$1:A$50,SMALL(IF(ISBLANK(Sheet1!$D$1:$D$50),"",IF(Sheet1!$D$1:$D$50=FALSE,ROW(Sheet1!$D$1:$D$50),"")),ROW(1:1))),"")


Once range is updated then drag across to column J and drag down as far as you want. This formula will show errors as blanks so you can drag the formula down 1,000 rows and if there is no data to show from your first sheet then it will still be blank.

[Rick-O-Shay]

Wow @dosydos, very impressive. I had no idea about an array formula. Thank you very much! Very informative!

@GlennUK, the reason I want to pre-filter it is because I am making a tool for other people with much less excel knowledge and I don't want to have to explain in my instructions how to filter when it can just automatically be done.

Thank you so much!