VBA Code to return Column header based on Criteria

[nr6281]

HI I am looking for some help, I have an excel with AND formulas from Column to Column AQ through AZ which returns True or False.

Now for example IF AQ = FALSE and AR = TRUE " The lookup should come up with header of Column AR since the check is TRUE.

If any criteria below is a match or multiple it should come up with the header example AN and AP is a match it should return as AN, AP something like this is this even possible
Below are the criteria.

AN	"FALSE"
AO	"TRUE"
AP	"FALSE"
AQ	"TRUE"
AR	"TRUE"
AS	"TRUE"
AT	"TRUE"
AU	"TRUE"
AV	"TRUE"
AW	"TRUE"
AX	"TRUE"
AY	"TRUE"
AZ	"TRUE"

<tbody>
</tbody>

 

[DanteAmor]

DanteAmor,

I used the data exposed by you, in post # 5, exactly in that location (AN1: AZ3) - that's why I made reference to post # 5.
The AO2 cell is not seen as having "TRUE / FALSE" and the result in BA2 is only "C", not "B, C".

Please check.

You must explain the criteria.


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:36.12px;" /><col style="width:132.12px;" /><col style="width:40.87px;" /><col style="width:36.12px;" /><col style="width:36.12px;" /><col style="width:40.87px;" /><col style="width:36.12px;" /><col style="width:40.87px;" /><col style="width:36.12px;" /><col style="width:36.12px;" /><col style="width:40.87px;" /><col style="width:36.12px;" /><col style="width:36.12px;" /><col style="width:96.95px;" /><col style="width:189.15px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >AN</td><td >AO</td><td >AP</td><td >AQ</td><td >AR</td><td >AS</td><td >AT</td><td >AU</td><td >AV</td><td >AW</td><td >AX</td><td >AY</td><td >AZ</td><td >BA</td><td >BB</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td style="background-color:#ffff00; ">FALSE</td><td ><--- What to check?</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >TRUE</td><td style="background-color:#ffc000; ">TRUE / FALSE</td><td style="background-color:#ffc000; ">FALSE</td><td >TRUE</td><td >TRUE</td><td >TRUE</td><td >TRUE</td><td >TRUE</td><td >TRUE</td><td >TRUE</td><td >TRUE</td><td >TRUE</td><td >TRUE</td><td >B, C</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >TRUE</td><td style="background-color:#ffc000; ">TRUE / FALSE</td><td style="background-color:#ffc000; ">FALSE</td><td >TRUE</td><td >TRUE</td><td style="background-color:#ffc000; ">FALSE</td><td >TRUE</td><td style="background-color:#ffc000; ">FALSE</td><td >TRUE</td><td >TRUE</td><td style="background-color:#ffc000; ">FALSE</td><td >TRUE</td><td >TRUE</td><td >B, C, F, H, K</td><td > </td></tr></table>

If BA1 = "False",

If AO2 = true / false then head B.
If AP2 = false then head C? Why shouldn't C appear?

I also put in AO3, TRUE / FALSE and in BA3 no header B appeared.

You must run the macro again.

 

[Tom.Jones]

If BA1 = "False",

If AO2 = true / false then head B.
If AP2 = false then head C? Why shouldn't C appear?

If AO2 = true / false then head B. ---> Yes, normally in BA2 should be B ad C, BUT appear only C (B is not there...)

If AP2 = false then head C? Why shouldn't C appear? --> I don't say that

I answer

The AO2 cell is not seen as having "TRUE / FALSE" and the result in BA2 is only "C", not "B, C".

AN AZ BA

A

B

C

D

E

F

G

H

I

J

K

L

M

FALSE

TRUE

TRUE/FALSE

FALSE

TRUE

TRUE

TRUE

TRUE

TRUE

TRUE

TRUE

TRUE

TRUE

TRUE

C

TRUE

TRUE/FALSE

FALSE

TRUE

TRUE

FALSE

TRUE

FALSE

TRUE

TRUE

FALSE

TRUE

TRUE

C, F, H, K

<tbody>
</tbody>

 

[DanteAmor]

This is very confusing for me.


In post #4 , @nr6281 wrote:

For example 1:
in column AN (header: A) has only "TRUE",
Column AO (Header B) has both "TRUE" and false
Column AP (Header C) has only "False" .

SO in Column BA (Header: What to check?) should return with header names i:e B and C.

My macro in post# 5 returned B, C.

But now "Tom.Jones" wants only C.

That is not clear. Sorry.


-----------------------------------

It would be convenient if Tom.Jones and nr6281 reach an agreement. They set the rules and publish several examples using those rules.

 

[Tom.Jones]

But now "Tom.Jones" wants only C.

No. I don't want....

Dante,

I haven't changed anything.
I simply copied the "table" you created in post # 5, I ran the VBA code, and in cell BA2, it appeared ONLY C (B and C should appear because AO2 is TRUE / FALSE).
Then I replaced in AO3 FALSE with TRUE / FALSE and I ran the code again.
In BA3 I got only C, F, H, K - not B
I have run the VBA code dozens of time but I did not get HEADER B if both values are in the cell - (TRUE / FALSE).

 

[DanteAmor]

No. I don't want....

Dante,

I haven't changed anything.
I simply copied the "table" you created in post # 5, I ran the VBA code, and in cell BA2, it appeared ONLY C (B and C should appear because AO2 is TRUE / FALSE).
Then I replaced in AO3 FALSE with TRUE / FALSE and I ran the code again.
In BA3 I got only C, F, H, K - not B
I have run the VBA code dozens of time but I did not get HEADER B if both values are in the cell - (TRUE / FALSE).

Ok. Try this please:

I set the code to read boolean value as text, it should work for all cases.

Sub test1()
  Dim i As Long, j As Long, cad As String
  For i = 2 To Range("AN" & Rows.Count).End(xlUp).Row
    cad = ""
    For j = Columns("AN").Column To Columns("AZ").Column
      If Cells(i, j)[B][COLOR=#0000ff].Text[/COLOR][/B] Like "*" & Range("BA1")[B][COLOR=#0000ff].Text[/COLOR][/B] & "*" Then
        cad = cad & Cells(1, j) & ", "
      End If
    Next
    If cad <> "" Then Cells(i, "BA") = Left(cad, Len(cad) - 2)
  Next
End Sub

 

[Tom.Jones]

Now it works perfectly. Thank you.

In post #8 I asked...

PS I don't use formula to get that TRUE and FALSE (possible that is?)

 

[DanteAmor]

Now it works perfectly. Thank you.

I'm glad to help you. Thanks for the feedback.

 

[nr6281]

Hi,

1. Not a valid file is the error.

2. I have put the "what to look for above the data" just for the template in the original file it is not present. I have a reference material in my mail (But In my final version I will be placing it above the data in row 2)

3. Row 2 or Column R are dont have the formulas. (The formulas are in the data itself I have not placed the formula as this was a template created)

4. I was thinking VBA could make the sorting simpler, I use excel 2013

Thank you

 

[DanteAmor]

Other options
https://www.mrexcel.com/forum/excel-questions/1076773-multiple-text-cells-one-cell.html

https://www.mrexcel.com/forum/excel-questions/1046946-ignore-blank-cells-formula-join-text.html

https://www.mrexcel.com/forum/excel-questions/1104621-adding-comma-between-single-digits-cell.html

 

[Peter_SSs]

1. Not a valid file is the error.

Hmm, I'm not sure how that would come about.

2. ... the "what to look for... (But In my final version I will be placing it above the data in row 2)

4. I was thinking VBA could make the sorting simpler, I use excel 2013

In that case you could try this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function MatchingColumns(rHdrRws As Range, rRowVals As Range) As String
  Dim c As Long
  
  For c = 1 To rHdrRws.Columns.Count
    If rHdrRws.Cells(2, c).Value = rRowVals.Cells(c).Value Then MatchingColumns = MatchingColumns & ", " & rHdrRws.Cells(1, c).Value
  Next c
  MatchingColumns = Mid(MatchingColumns, 3)
End Function

In the "What to look for" in row 2, do not place quote marks around each TRUE or FALSE, just type the word into the cell. Since you main data is formulas returning a logical TRUE/FALSE not a text "TRUE"/"FALSE" row 2 should be in the same format.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Column Header	A	B	C	D	E	F	G	H	I	J	K	L	M	Final Check
2	Check for?	FALSE	TRUE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
3		FALSE	FALSE	TRUE	FALSE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	A, E, F
4		FALSE	FALSE	TRUE	FALSE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	A, E, F
5		FALSE	FALSE	DISABLED	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	A
6		FALSE	FALSE	DISABLED	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	A
7		FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE	TRUE	A, C, E, F, H, J, L, M
8		TRUE	TRUE	TRUE	FALSE	TRUE	TRUE	TRUE	FALSE	TRUE	FALSE	FALSE	TRUE	TRUE	B, E, F, G, I, L, M
9		TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	B, C
10		FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	A, D, E, F
11		FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	A, C, E, F, K
Sheet2