Forum Discussion

Sayarborough's avatar
Sayarborough
Copper Contributor
Aug 03, 2019

Grouping maybe? But how?

I have a database that has a column (B) called partners with seven companies listed sporadically throughout based off the row lines. I need to create a column (G) for Affiliations and create a function where if the B cell in that row contains one of four names it automatically reads Group A, or if it contains any other names it reads Group B. Is there an easy way to do this? Excel 2016
excel
Formulas and Functions

2 Replies

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Aug 03, 2019

    Sayarborough 

    If you want to avoid a mapping table, although I discourage you to do so, your formula in G2, copied down rows, would be: 

    ="Group "&

    IF(OR(B2={"Aone","Atwo","Athree","Afour"}),

    "A","B")

    Note that the names in the logical1 argument of OR are assumed.

  • Yury Tokarev's avatar
    Yury Tokarev
    Iron Contributor
    Aug 03, 2019

    Hi @Sayarborough ,

     

    I suggest creating a mapping table with two columns, first column listing Partners, and the second - Group Name they belong to. Assuming that the mapping table is in the range K1:L9 with first row being headers, your formula in G2 would be =INDEX($L$2:$L$9,MATCH(B2,$K$2:$K$9,0))

     

    Thanks

    Yury

Resources