CODEWITHSUNDEEP
excel
Alicia C
Alicia C

Reputation: 13

Copy columns to another cell if 'TRUE' and skipping blanks

On sheet 'Klient' I got column B (7-58) with text information and column R (7-58) with 'TRUE' & 'FALSE'. If column R states 'TRUE' I want information in same row, under column B to copy to sheet 'Summ' from A3 and downwards.

Using 

=IF(Klient!R7=TRUE;Klient!B7;"")

in sheet 'Summ' and dragging it down from A3, it copies correctly BUT I get blanks cells where R7 in sheet 'Klient' was 'FALSE'. I do not want the blank cells, but the copied cells in A3 and downwards to neatly line up without 'spaces' of empty cells.

I've been googling around but genuinely can't seem to find any indication as to how to skip blanks in an IF-formula. Yes, I have found VBA-codes for it but do not want to use it as I have 0 experience with it and will not be able to apply/recreate it if need be. And simply filtering will not be an option as it will disturb data in later columns. I've been considering INDEX but unsure if that would be the right track.

To summarize,
What addition (or new formula) do I need to work with for an IF-formula to ignore/skip blank output and only copy and output IF=TRUE.

Image of current output to wished output.

Upvotes: 1

Views: 1558

Answers (2)

Terry W
Terry W

Reputation: 3267

Try use Power Query.

Please refer to this article to find out how to use Power Query on your version of Excel. It is available in Excel 2010 Professional Plus and later versions. My demonstration is using Excel 2016.

Steps are:

  1. Load the data/table on your Klient sheet to the Power Query Editor;
  2. Filter Column R to show TRUE only (filter the column as you would normally do in excel worksheet);
  3. Close & Load the filtered table to a new worksheet (by default), or you can change your Query Option to avoid loading the result to a new worksheet but create a connection to the query which can be load to a specific location in your workbook, which might be the Summ sheet in your case.

Let me know if you have any questions. Cheers :)

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152605

You will need an array type formula:

=INDEX(Klient!B:B,AGGREGATE(15,7,ROW(Klient!$R$7:$R$58)/(Klient!$R$7:$R$58),ROW($A1)))

You will probably need to replace the , with ; for your local settings.

When Microsoft releases FILTER() to Office 365 Excel it would be as simple as:

=FILTER(Klient!$B$7:$D$58,Klient!$R$7:$R$58)

Upvotes: 1

Related Questions