Need Column Heading if cell in row = Y or F

0

I have a chart of medical symptoms on Y [vertical] axis (1st column) and Disease conditions along X [horizontal] axis (1st row). Each individual cell in chart is either blank (no data), or contains text "Y" if yes for symptom (row) and disease (column) or "F" if fluctuating (meaning variable levels of a certain hormone can make symptom (row) sometimes true. NOTE: "F" does not mean false, blank cell does.

My request is this, is there a function that will look at each row of symptoms (eg, high blood pressure, trouble sleeping, etc) and return a list of the various column headings that have a "Y" or a "F" in that row (based upon the specific disease/hormone state (eg, thyroid low, cortisol high, etc)? One row might have two, three or more columns (diseases) that have a "Y" in one of the row's cells. Another might have only one cell in the entire row with a "Y", etc.

Example: Low blood pressure (row) may have "Y" in cells under "Thyroid low"(column title), "Cortisol low"(column title), etc. I need search of "Low blood pressure" row to return "Thyroid low, Cortisol low" (column titles in columns with "Y" in them) in search results 

Answer
Discuss

Discussion

Given that this is for medical issues, I request you edit your question and attach a sample file so that you can get, for sure, the correct formula; use dummy-data of course. (Last thing I want is to give you a formula that only seems to work.)
don (rep: 1705) Oct 23, '18 at 1:08 pm
I have a modification request to the original question – is there a way to select multiple rows of symptoms (ex. Headache, Fatigue, Depression) and have a list of Hormone conditions (column headings; ex. Low Cortisol, Low Thyroid, etc.) generated in some sort of hierarchy listing BASED on the frequency (number) of "Y" (or "F" -- fluctuating; not false) returned PER COLUMN? For example, if a Hormone Condition (let's call it X for now) has a "Y" in rows: Headache, Fatigue, & Depression and then Hormone Condition Z has a "F" in row Headache and a "Y" in Fatigue (but not Depression), then what I am looking for is something that would give a 'weight' (greater importance) to Condition X (because it had 3 'hits' in the selected symptom rows) compared to Condition Z (having only 2 'hits' in the same selected symptom rows). It would be good to show a result of Condition X and Condition Z but in a hierarchal order based on number (strength) of 'hits' (data in cells, either Y or F).

It would also be nice to assign a value to strength of hits in selected conditions. For example, in the example above perhaps a value of 100% could be assigned to Condition X as it had 3 'hits' out of 3 selected row symptoms. Maybe a value of 66% could them be assigned to Condition Z as it only had 2 'hits' out of 3 selected symptoms. Just curious if Excel could ever do this or is there a better flowchart, decision tree, etc. that could handle this sort of query better?
hawkeyelar (rep: 2) Jan 27, '19 at 4:47 pm
Add to Discussion

Answers

0
Selected Answer

Unfortunately, Excel doesn't have a function like the one you describe. But it isn't difficult to create one. In Excel terminology it is called a "User Defined Function", in short UDF. It works just like any of Excel's built-in functions but you are in full control of the code. Here is the code.

Function Summary(Crit As String, _
                 CritRow As Long, _
                 CapsRng As Range) As String
    ' 29 Oct 2018

    Dim Fun() As String
    Dim Target As Range
    Dim Crits As Variant
    Dim Caps As Variant
    Dim C As Long
    Dim n As Integer

    Application.Volatile
    With CapsRng
        Set Target = Range(Cells(CritRow, .Column), _
                           Cells(CritRow, .Column + .Columns.Count - 1))
        Caps = .Value
    End With
    Crits = Target.Value

    ReDim Fun(UBound(Caps, 2))
    For C = LBound(Crits, 2) To UBound(Crits, 2)
        If StrComp(Crits(1, C), Crit, vbTextCompare) = 0 Then
            Fun(n) = Caps(1, C)
            n = n + 1
        End If
    Next C

    If n Then
        ReDim Preserve Fun(n - 1)
        Summary = Join(Fun, ", ")
    Else
        Summary = "N/A"
    End If
End Function

This code must be installed in a standard code module. Note that every workbook comes with a number of special (class) modules but no standard module. You will have to create it. It's easy. Press Alt+F11 to open the VB Editor. Find your workbook in the Project Explorer window on the left of the Editor's screen and right-click on it. In the drop-down that opens select Module. Excel will insert the new, blank module on the right of your screen. It will replace another module - one of the built-in ones - which is also blank. To see the difference look at the module's name in the window's title. You will see [Module1 (Code)] appended to the workbook's name. Paste the above code in that code module. Save the workbook in XLSM format.

That's all it takes. The UDF is now available anywhere in the workbook. Its name is Summary, and this is how you call it, for example:-

=Summary("F", ROW(),$B$1:$I$1)
  1. "F" stands for the criterium you want to summarise. You can use "F" or "Y". The criteria aren't case sensitive.
  2. ROW() is a requirement. It just tells the function which row to look at.
  3. $B$1:$I$1 is the address of the column captions you want to use in the summary. The range can have any number of cells but they must be contiguous.

You can have two formulas next to each other (in different columns), one for "Y" and the other for "F". You can concatenate the formula with other text, for example,

="Fluctuating: " & Summary("F", ROW(),$B$1:$I$1)

Copy the formula down as you would with any  built-in function.

Discuss

Discussion

Variatus, I appreciate your skilled answer and prompt response to my question. I was out of pocket and unable to try your formula until this weekend. I also appreciated the info on how to add a macro (for this newbie). However, it seems that the formula works properly when there are only one or two 'hits' to the query (ie. if there is a "Y" in only one or two columns in a certain row). However, if there are three or more 'column titles' that have a "Y" in a certain row, the formula returns: #VALUE!  Is there a way to tweek your original code so that ALL column headings in one row that match the "Y" (or "F") are returned?
hawkeyelar (rep: 2) Oct 28, '18 at 8:30 pm
You are right. There was an error in my code. My apologies! The error has been eliminated in my above reply. Please use the corrected code and try again. It should now work as advertised.
Variatus (rep: 2938) Oct 29, '18 at 3:05 pm
Replaced old code with new one and it works GREAT! I really appreciate the help.
hawkeyelar (rep: 2) Oct 29, '18 at 8:31 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login