VBA Code to return Column header based on Criteria

N

nr6281

New Member
Joined
Jun 19, 2019
Messages
37
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.

[TABLE="class: cms_table, width: 170"]
<tbody>[TR]
[TD]AN[/TD]
[TD]"FALSE"[/TD]
[/TR]
[TR]
[TD]AO[/TD]
[TD]"TRUE"[/TD]
[/TR]
[TR]
[TD]AP[/TD]
[TD]"FALSE"[/TD]
[/TR]
[TR]
[TD]AQ[/TD]
[TD]"TRUE"[/TD]
[/TR]
[TR]
[TD]AR[/TD]
[TD]"TRUE"[/TD]
[/TR]
[TR]
[TD]AS[/TD]
[TD]"TRUE"[/TD]
[/TR]
[TR]
[TD]AT[/TD]
[TD]"TRUE"[/TD]
[/TR]
[TR]
[TD]AU[/TD]
[TD]"TRUE"[/TD]
[/TR]
[TR]
[TD]AV[/TD]
[TD]"TRUE"[/TD]
[/TR]
[TR]
[TD]AW[/TD]
[TD]"TRUE"[/TD]
[/TR]
[TR]
[TD]AX[/TD]
[TD]"TRUE"[/TD]
[/TR]
[TR]
[TD]AY[/TD]
[TD]"TRUE"[/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]"TRUE"
[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Tom.Jones said:
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.
Click to expand...

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.
Click to expand...

You must run the macro again.
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
If BA1 = "False",

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


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".
Click to expand...

AN AZ BA
[TABLE="width: 712"]
<tbody>[TR]
[TD="width: 64, bgcolor: white"]A
[/TD]
[TD="width: 96, bgcolor: white"]B
[/TD]
[TD="width: 64, bgcolor: white"]C
[/TD]
[TD="width: 64, bgcolor: white"]D
[/TD]
[TD="width: 64, bgcolor: white"]E
[/TD]
[TD="width: 64, bgcolor: white"]F
[/TD]
[TD="width: 64, bgcolor: white"]G
[/TD]
[TD="width: 64, bgcolor: white"]H
[/TD]
[TD="width: 64, bgcolor: white"]I
[/TD]
[TD="width: 64, bgcolor: white"]J
[/TD]
[TD="width: 64, bgcolor: white"]K
[/TD]
[TD="width: 64, bgcolor: white"]L
[/TD]
[TD="width: 64, bgcolor: white"]M
[/TD]
[TD="width: 86, bgcolor: yellow, align: center"]FALSE

[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white, align: center"]TRUE
[/TD]
[TD="width: 96"]TRUE/FALSE

[/TD]
[TD="width: 64, align: center"]FALSE
[/TD]
[TD="width: 64, bgcolor: white, align: center"]TRUE
[/TD]
[TD="width: 64, bgcolor: white, align: center"]TRUE
[/TD]
[TD="width: 64, bgcolor: white, align: center"]TRUE
[/TD]
[TD="width: 64, bgcolor: white, align: center"]TRUE
[/TD]
[TD="width: 64, bgcolor: white, align: center"]TRUE
[/TD]
[TD="width: 64, bgcolor: white, align: center"]TRUE
[/TD]
[TD="width: 64, bgcolor: white, align: center"]TRUE
[/TD]
[TD="width: 64, bgcolor: white, align: center"]TRUE
[/TD]
[TD="width: 64, bgcolor: white, align: center"]TRUE
[/TD]
[TD="width: 64, bgcolor: white, align: center"]TRUE
[/TD]
[TD="width: 86, bgcolor: white"]C

[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white, align: center"]TRUE
[/TD]
[TD="width: 96"]TRUE/FALSE

[/TD]
[TD="width: 64, align: center"]FALSE
[/TD]
[TD="width: 64, bgcolor: white, align: center"]TRUE
[/TD]
[TD="width: 64, bgcolor: white, align: center"]TRUE
[/TD]
[TD="width: 64, align: center"]FALSE
[/TD]
[TD="width: 64, bgcolor: white, align: center"]TRUE
[/TD]
[TD="width: 64, align: center"]FALSE
[/TD]
[TD="width: 64, bgcolor: white, align: center"]TRUE
[/TD]
[TD="width: 64, bgcolor: white, align: center"]TRUE
[/TD]
[TD="width: 64, align: center"]FALSE
[/TD]
[TD="width: 64, bgcolor: white, align: center"]TRUE
[/TD]
[TD="width: 64, bgcolor: white, align: center"]TRUE
[/TD]
[TD="width: 86, bgcolor: white"]C, F, H, K
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
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.
Click to expand...

My macro in post# 5 returned B, C.

135e831581f5ddcb4ddc2efcb5824127.jpg




But now "Tom.Jones" wants only C.

0fa59c8d69344d548f1553c81f9eedbe.jpg





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.
 
Upvote 0
But now "Tom.Jones" wants only C.
Click to expand...

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).
 
Last edited:
Upvote 0
Tom.Jones said:
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).
Click to expand...

Ok. Try this please:

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

Code: 
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
 
Upvote 0
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
 
Upvote 0
nr6281 said:
1. Not a valid file is the error.
Click to expand...
Hmm, I'm not sure how that would come about. :huh:


nr6281 said:
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
Click to expand...
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)

Code: 
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
ABCDEFGHIJKLMNO
1Column HeaderABCDEFGHIJKLMFinal Check
2Check for?FALSETRUEFALSETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUE
3FALSEFALSETRUEFALSETRUETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEA, E, F
4FALSEFALSETRUEFALSETRUETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEA, E, F
5FALSEFALSEDISABLEDFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEA
6FALSEFALSEDISABLEDFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEA
7FALSEFALSEFALSEFALSETRUETRUEFALSETRUEFALSETRUEFALSETRUETRUEA, C, E, F, H, J, L, M
8TRUETRUETRUEFALSETRUETRUETRUEFALSETRUEFALSEFALSETRUETRUEB, E, F, G, I, L, M
9TRUETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEB, C
10FALSEFALSETRUETRUETRUETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEA, D, E, F
11FALSEFALSEFALSEFALSETRUETRUEFALSEFALSEFALSEFALSETRUEFALSEFALSEA, C, E, F, K
Sheet2
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Hide Buttons Selectively
Replies
3
Views
78
dmt32
D
N
  • Question Question
AutoFilter Field Macro Returns a Header Row only
2
Replies
11
Views
102
MARK858
MARK858
D
  • Question Question
Lookup value based on two column criteria
Replies
3
Views
460
Cubist
Cubist
X
  • Question Question
Excel VBA - Return row value & Colum header value
Replies
0
Views
325
Xenon72
X
M
  • Solved
Data Advanced filter problem
Replies
2
Views
419
mikemcbain
M

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top