Forum Discussion

huskereurocat's avatar
huskereurocat
Copper Contributor
Jun 21, 2023
Solved

Is an IF statment the best for this situation?

I've been trying to think of a way to write an IF statement, but am having a hard time understanding how to make this work. I have two columns of information so I hope that makes this easier. In the Trait_QBStyle column there are two entries which are either Balanced or Scrambling. In the Player Type column there I need the final product to be one of two entries as well. Either QB_Scrambler or QB_Improviser.  So there should be the following cell values:

Balanced - QB_Improviser

Balanced - QB_Scrambler

Scrambling - QB_Improviser

From the illustration below, I should never see a Scrambling - QB_Scrambler. A Scrambling trait will always need to be QB_Improviser, but Balanced could either be a QB_Scrambler or QB_Improviser. If I can remember this correctly, the IF statement has two outcomes. Either TRUE or FALSE, but I can't figure out how to write the formula to get that product. 

The kicker is that there can be no additional columns in the spreadsheet. The formula needs to be either contained in the Trait-QBStyle or Player Type column. 

Can anyone help me?  

excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Jun 22, 2023

    huskereurocat 

    I think I switched the terms.

    Sub Convert()
    Dim rng As Range
    Dim typ As Range
    Dim adr As String
    Application.ScreenUpdating = False
    Set rng = Range("IO:IO").Find(What:="Scrambling", LookAt:=xlWhole)
    If Not rng Is Nothing Then
        adr = rng.Address
        Do
            Set typ = Range("KA" & rng.Row)
            If typ.Value = "QB_Scrambler" Then
                typ.Value = "QB_Improviser"
            End If
            Set rng = Range("IO:IO").Find(What:="Scrambling", After:=rng, LookAt:=xlWhole)
            If rng Is Nothing Then Exit Do
        Loop Until rng.Address = adr
    End If
    Application.ScreenUpdating = True
End Sub

10 Replies

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Jun 21, 2023

    huskereurocat 

    I put your question to the Bing chatbot. Here is its answer:

     

    It sounds like you're trying to use an IF statement in a spreadsheet program to categorize data based on the values in two columns. You can use a nested IF statement to achieve this. Here's an example formula that you can enter into the Player Type column:

    `=IF(A2="Scrambling", "QB_Improviser", IF(A2="Balanced", "QB_Scrambler", ""))`

    This formula checks if the value in cell A2 (Trait_QBStyle column) is "Scrambling". If it is, the formula returns "QB_Improviser". If it's not, the formula checks if the value is "Balanced". If it is, the formula returns "QB_Scrambler". If it's neither "Scrambling" nor "Balanced", the formula returns an empty string.

    You can then drag this formula down to apply it to all rows in the Player Type column. Does this help?

     

    • huskereurocat's avatar
      huskereurocat
      Copper Contributor
      Jun 22, 2023

      Detlef_Lewin 

       

      Thanks for the research and answer. There seems to be only one problem with this formula. If you look at the snapshot below, you can see that what gets returned is either a Balanced - QB_Scrambler or a Scrambling - QB_Improviser. Nowhere in the formula is there a possibility of a Balanced - QB_Improviser, which there needs to be. 

       

      Somehow we need to have the formula check if there is a Scrambling - QB_Scrambler (highlighted in yellow below) in which case that would need to be a  Scrambling - QB_Improviser.

      So we're left with the three possibilties highlighted in green below. 

       

      Is that even possible with an IF statement? Somehow, I would think, the formula would need to see there is a Scrambling - QB_Scrambler and change it to Scrambling - QB_Improviser. Can that be done? 

      Thanks, 

      Kelly 

       

      • Detlef_Lewin's avatar
        Detlef_Lewin
        Silver Contributor
        Jun 22, 2023

        huskereurocat 

         

        Somehow, I would think, the formula would need to see there is a Scrambling - QB_Scrambler and change it to Scrambling - QB_Improviser. Can that be done? 

         

         

        Now we are getting there.

        This is not possible with a formula but with VBA.

        However you can use conditional formatting to highlight the cells.

        Column A is TRAIT_OBSTYLE and column B is PlayerType.

        =COUNTIFS($A2,"Scrambling",$B2,"QB_Scrambler")