Return targeted columns from an array of columns

Hi,

I am looking for a formula that will seek through an array of columns and return only those columns that contain a specific key word, in this case "TRUE". See the example workbook below as an example of the columns that I am referring to. I want to return only those columns (yes whole columns) that have "TRUE" in them. Could someone look into this for me?

Thank you kindly ^_^

Book2.xls

And where do you want those columns returned to? Are you saying that you want to delete the columns that have FALSE in row 1?

Pete

I mean I want to return a copy of all the columns containing TRUE to the new worksheet

You could do this using an array formula on Sheet2, but as you have so much data I think it would be better to use a helper row on Sheet1 (e.g. by inserting a new row1 on that sheet and putting a formula in those new cells), and then in Sheet2 you would just have a normally-entered formula using INDEX and MATCH. The helper row can be hidden, so that Sheet1 will look the same as you have now - is that an acceptable approach?

Pete

No I do not want to delete the colums that have FALSE in them. I want to return the specified columns (the ones containing TRUE) to a new worksheet, say Sheet 2

Im not adept (yet) at using MATCH and INDEX functions. Could you provide an example, including an extra 'helper' row in Sheet1 as you mentioned above please?

Thank you

In the attached file I have inserted a new row 1 in Sheet1 and put this formula in B1:

Formula:

=IF(B2,MAX($A1:A1)+1,"")

which is then copied across to cell AY1. It identifies the columns that meet the criteria, and gives each one a unique sequential number. I've coloured this blue to show that it is a helper row (which can be hidden once it is set up).

In Sheet2 I have put 1 in cell A1 and this formula in B1:

Formula:

=IF(COLUMNS($A:B)-1>MAX(Sheet1!1:1),"",MATCH(COLUMNS($A:B)-1,Sheet1!1:1,0))

and again, this is copied across to cell AX1. I've put this formula in B2:

Formula:

=IF(B$1="","",INDEX(Sheet1!$2:$56,ROWS($1:1),B$1))

which can then be copied down to B56 to return the data from the row given in B1. I've applied red to B2 and emboldened B3 to match the look of Sheet1, and then the block of formulae in B2:B56 can be copied across as far as you need them (i.e. to column AX), although I have only copied to column H to keep the file size down. I've also copied B3:B56 into column A, although as these are fixed values you could just copy that from Sheet1.

You will need to copy the formulae across your sheet, and then you can play about with it - if you change any of the TRUE values to FALSE, or any of the FALSE values to TRUE in Sheet1, those changes will immediately be reflected in Sheet2.

Hope this helps.

Pete

That was spot on and solved my case perfectly, thank you Pete! ^_^ I have inserted the formulae as per your instructions and can now compile the columns that I need to work with. Once again, thanks. I've added a reputation star to your reply

Originally Posted by Pete_UK

In the attached file I have inserted a new row 1 in Sheet1 and put this formula in B1:

Formula:

=IF(B2,MAX($A1:A1)+1,"")

which is then copied across to cell AY1. It identifies the columns that meet the criteria, and gives each one a unique sequential number. I've coloured this blue to show that it is a helper row (which can be hidden once it is set up).

In Sheet2 I have put 1 in cell A1 and this formula in B1:

Formula:

=IF(COLUMNS($A:B)-1>MAX(Sheet1!1:1),"",MATCH(COLUMNS($A:B)-1,Sheet1!1:1,0))

and again, this is copied across to cell AX1. I've put this formula in B2:

Formula:

=IF(B$1="","",INDEX(Sheet1!$2:$56,ROWS($1:1),B$1))

which can then be copied down to B56 to return the data from the row given in B1. I've applied red to B2 and emboldened B3 to match the look of Sheet1, and then the block of formulae in B2:B56 can be copied across as far as you need them (i.e. to column AX), although I have only copied to column H to keep the file size down. I've also copied B3:B56 into column A, although as these are fixed values you could just copy that from Sheet1.

You will need to copy the formulae across your sheet, and then you can play about with it - if you change any of the TRUE values to FALSE, or any of the FALSE values to TRUE in Sheet1, those changes will immediately be reflected in Sheet2.

Hope this helps.

Pete

Thank you Pauley, I've tried your formula but it just returned VALUE! in all cells. Thank you anyways, my issue is now solved ^_^

Originally Posted by Pauleyb

Okay, I know this is not the most elegant solution (uses OFFSET and is an array formula for a large spreadsheet), but I thought it was a interesting challenge. This is what worked. Put this in cell A1 of Sheet2:
=OFFSET(Sheet1!$A$1,ROW(),SMALL(--(Sheet1!$A$1:$AX$1)*COLUMN(Sheet1!$A$1:$AX$1),COUNTIF(Sheet1!$B$1:$AX$1,FALSE)+COLUMN()+1)-1)
Enter with Ctrl-Shift-Enter, and then drag across and down the sheet.