Home

Grouping maybe? But how?

%3CLINGO-SUB%20id%3D%22lingo-sub-786521%22%20slang%3D%22en-US%22%3EGrouping%20maybe%3F%20But%20how%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786521%22%20slang%3D%22en-US%22%3EI%20have%20a%20database%20that%20has%20a%20column%20(B)%20called%20partners%20with%20seven%20companies%20listed%20sporadically%20throughout%20based%20off%20the%20row%20lines.%20I%20need%20to%20create%20a%20column%20(G)%20for%20Affiliations%20and%20create%20a%20function%20where%20if%20the%20B%20cell%20in%20that%20row%20contains%20one%20of%20four%20names%20it%20automatically%20reads%20Group%20A%2C%20or%20if%20it%20contains%20any%20other%20names%20it%20reads%20Group%20B.%20Is%20there%20an%20easy%20way%20to%20do%20this%3F%20Excel%202016%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-786521%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-786539%22%20slang%3D%22en-US%22%3ERe%3A%20Grouping%20maybe%3F%20But%20how%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786539%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386640%22%20target%3D%22_blank%22%3E%40Sayarborough%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suggest%20creating%20a%20mapping%20table%20with%20two%20columns%2C%20first%20column%20listing%20Partners%2C%20and%20the%20second%20-%20Group%20Name%20they%20belong%20to.%20Assuming%20that%20the%20mapping%20table%20is%20in%20the%20range%20K1%3AL9%20with%20first%20row%20being%20headers%2C%20your%20formula%20in%20G2%20would%20be%26nbsp%3B%3DINDEX(%24L%242%3A%24L%249%2CMATCH(B2%2C%24K%242%3A%24K%249%2C0))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EYury%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-786540%22%20slang%3D%22en-US%22%3ERe%3A%20Grouping%20maybe%3F%20But%20how%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786540%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386640%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3E%40Sayarborough%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suggest%20creating%20a%20mapping%20table%20with%20two%20columns%2C%20first%20column%20listing%20Partners%2C%20and%20the%20second%20-%20Group%20Name%20they%20belong%20to.%20Assuming%20that%20the%20mapping%20table%20is%20in%20the%20range%20K1%3AL9%20with%20first%20row%20being%20headers%2C%20your%20formula%20in%20G2%20would%20be%26nbsp%3B%3DINDEX(%24L%242%3A%24L%249%2CMATCH(B2%2C%24K%242%3A%24K%249%2C0))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EYury%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-786612%22%20slang%3D%22en-US%22%3ERe%3A%20Grouping%20maybe%3F%20But%20how%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786612%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386640%22%20target%3D%22_blank%22%3E%40Sayarborough%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20to%20avoid%20a%20mapping%20table%2C%20although%20I%20discourage%20you%20to%20do%20so%2C%20your%20formula%20in%20G2%2C%20copied%20down%20rows%2C%20would%20be%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%22Group%20%22%26amp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EIF(OR(B2%3D%7B%22Aone%22%2C%22Atwo%22%2C%22Athree%22%2C%22Afour%22%7D)%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%22A%22%2C%22B%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ENote%20that%20the%20names%20in%20the%20logical1%20argument%20of%20OR%20are%20assumed.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Sayarborough
Occasional Visitor
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
2 Replies

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

@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.

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies