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.

[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]

 

[offthelip]

This sounds like it should be a very simple bit of code, but nobody has chosen to answer it because your requirement is not clear.

 

[nr6281]

Okay let me try that once again.

I have a spread sheet with =AND formulas that return TRUE OR FALSE based on the need.

From Column AN to AZ are either TRUE or FALSE, I would like to see if there can be a code for Column BA to return with the name of header of the column based on the criteria.

In column AN (header named as A) I check for "False", Column AO (header named as B) I check for "TRUE" Column AP (header named as C) I check for "False". What I check for changes based on the formula,

Example what the code should return

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.
Reason: Header A I check for false but there is no False (So the code should return only header B and C)

For example 2: in column AN (header: A) "TRUE", Column AO (Header B) has only "False" Column AP (Header C) has "False" . SO in Column BA (Header: What to check?) should return with header names i:e C
The reason Column AN header should not come is because I am supposed to check for False and since there are no false it should not return the header in column BA.

I am not sure if this is somewhat doable! Or if I am clear enough

 

[Peter_SSs]

Or if I am clear enough

Not for me. Instead of just trying to explain in words, any chance you could post 5-10 rows of sample data and the expected outputs & explain again in relation to that sample data?
My signature block below has a link with help on how to do that.

 

[DanteAmor]

If the following example is correct

<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;" /></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></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></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></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; ">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></tr></table>

Then you can apply the following macro:

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).Value Like "*" & Range("BA1") & "*" Then
        cad = cad & Cells(1, j) & ", "
      End If
    Next
    If cad <> "" Then Cells(i, "BA") = Left(cad, Len(cad) - 2)
  Next
End Sub

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

If the example is not correct, it may help you clarify how you have the data and the result you expect, that way we can help you with a formula or with a simpler macro.

 

[nr6281]

https://www.excelforum.com/excel-pr...umn-header-based-on-criteria.html#post5175377

I am not able to install the addins for some reason.

 

[Peter_SSs]

I am not able to install the addins for some reason.

1. What happened (eg error message, Excel crashing, ..) to indicate that you cannot install them?

2. The "what to search for" seems to be repeated in your sample data: once in row 2 above the other data and once in column R. Is that the case in your real file too? If not, what is the location - in a row above the data or in a column elsewhere?

3. You said your main data was produced by AND() functions (which return TRUE or FALSE. However, your "what to search for" values (row 2 and column R) are given as TEXT values "TRUE" and "FALSE". Is that the case in your actual file too? Need to know as TRUE is not the same as "TRUE"

4. Do you really need a macro as this can be achieved with a formula if you have Excel through Excel 365 or you have Excel 2019?

 

[Tom.Jones]

If the following example is correct

AN

AO

AP

AQ

AR

AS

AT

AU

AV

AW

AX

AY

AZ

BA

A

B

C

D

E

F

G

H

I

J

K

L

M

TRUE

TRUE

TRUE

TRUE

TRUE

TRUE

TRUE

TRUE

TRUE

TRUE

TRUE

B, C

TRUE

TRUE

TRUE

TRUE

TRUE

TRUE

TRUE

TRUE

B, C, F, H, K

<tbody>
[TD="align: center"]1
[/TD]

[TD="bgcolor: #FFFF00"]FALSE
[/TD]

[TD="align: center"]2
[/TD]

[TD="bgcolor: #FFC000"]TRUE / FALSE
[/TD]
[TD="bgcolor: #FFC000"]FALSE
[/TD]

[TD="align: center"]3
[/TD]

[TD="bgcolor: #FFC000"]FALSE
[/TD]
[TD="bgcolor: #FFC000"]FALSE
[/TD]

[TD="bgcolor: #FFC000"]FALSE
[/TD]

[TD="bgcolor: #FFC000"]FALSE
[/TD]

[TD="bgcolor: #FFC000"]FALSE
[/TD]

</tbody>

Then you can apply the following macro:

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).Value Like "*" & Range("BA1") & "*" Then
        cad = cad & Cells(1, j) & ", "
      End If
    Next
    If cad <> "" Then Cells(i, "BA") = Left(cad, Len(cad) - 2)
  Next
End Sub

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

If the example is not correct, it may help you clarify how you have the data and the result you expect, that way we can help you with a formula or with a simpler macro.

DanteAmor,

I tested your code, and it does not list the header where it is TRUE and FALSE, the one with header B (TRUE / FALSE).
I put in AO3, TRUE / FALSE and in BA3 no header B appeared.
Can you please check?

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

Thanks

 

[DanteAmor]

DanteAmor,

I tested your code, and it does not list the header where it is TRUE and FALSE, the one with header B (TRUE / FALSE).
I put in AO3, TRUE / FALSE and in BA3 no header B appeared.
Can you please check?

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

Thanks

Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

 

[Tom.Jones]

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".
I also put in AO3, TRUE / FALSE and in BA3 no header B appeared.
Please check.