Excel - Return all column headers if columns do not contain TRUE

Question

I want to return all columns headers of hundreds of columns in which "TRUE" is not under - meaning ones that only have "FALSE" values. Could someone help recommend an easy way of doing this? I've tried various methods but found that they are still very manual.

I am unfamiliar with VBA - just tried to use Pivots and some VLOOKUP and HLOOKUPS and haven't come near the solution I want.

Answer 1

please click. they wont let me embed this image yet

Step 1: Find which column has all the False values

In cell B1, equation:

=IFNA(MATCH(TRUE,B3:B8,0),TRUE)

copy across all headers needed to be check (this will modify B3:B8)

Step 2: Transpose the Header and Check from Step1

equation:

=TRANSPOSE(B1:J2)

Modify (B1:J2) as needed

Step 3: Filter out columns that are TRUE

Use usual table filter or equation: =FILTER(M:M,L:L=TRUE)

Advance: Combine step 2 & 3

equation:

=TRANSPOSE(FILTER(B2:J2,B1:J1=TRUE))

where,

B1:J1 is the Step1 equation check and

B2:J2 is the header of data of interest

Answer 2

Assuming you want the headers that match in a single row, and assuming you want one matching header per cell:

1. Add three rows above the existing header row.

2. In cell A3, put the formula

   =AND(A5:A####)

   and copy it across all the columns used. (Replace #### with the last row #.)

3. In cell A2, put the array formula

   =INDEX($A$4:$E$4,SMALL(IF($A$3:$E$3,COLUMN($A$3:$E$3)),COLUMN(A:A)))

   (Type it in, press Ctrl-Shift-Enter.) Replace E with the last column used. Copy it across all columns used.

4. In cell A1, put the formula

   =IFERROR(A2,"")

   and copy it across all columns used.

You should see all the valid headers in row 1 followed by blank cells.

I broke the formula in two rows (1, 2) so you wouldn't have to repeat the INDEX expression twice in the IF, but you could combine them.

Here is an alternative that doesn't need array formulas:

1. Add three rows above the existing header row.

2. In cell A3, put the formula

   =IF(AND(A5:A####),1)

   Replace #### with the last row #.

3. In cell B3, put in the formula

   =IF(AND(B5:B####),COUNTIF($A$3:A3,"<>FALSE")+1)

   Replace #### with the last row #. Copy it across to all remaining used columns.

4. In cell A2, put the formula

   =HLOOKUP(COLUMN(A:A),$A$3:$E$4,2,FALSE)

   Copy it across to all used columns.

5. In cell A1, put the formula

   =IFNA(A2,"")

   and copy it across to all used columns.

Answer 3

Here's a solution.

1) Insert a row above your data. You can hide later if you want. Or you can write this formula across the corresponding columns in another tab (see step 5).

2) cell A1 formula = =IF(ISNA(MATCH(TRUE,A3:A5000,0)),1,0)

3) cell B1 formula = =IF(ISNA(MATCH(TRUE,B3:B5000,0)),MAX($A$1:A1)+1,0).

4) drag over for as many columns as needed and adjust 5000 row count as needed

5) On another tab in A1 write the formula: =IFERROR(HLOOKUP(ROW(),data!$1:$2,2,0),"") where data is the sheet with data.

6) drag down for as many rows as needed.