Home

formula for getting different sets of data entered

%3CLINGO-SUB%20id%3D%22lingo-sub-910556%22%20slang%3D%22en-US%22%3Eformula%20for%20getting%20different%20sets%20of%20data%20entered%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-910556%22%20slang%3D%22en-US%22%3E%3CP%3Ecan%20anyone%20help%20me%20in%20this%3CBR%20%2F%3Ecol.1%7C%20col.2%7C%20col.3%3CBR%20%2F%3EA%20%7C%20A%20%7C%20SET1%3CBR%20%2F%3EA%20%7C%20B%20%7C%20SET2%3CBR%20%2F%3EB%20%7C%20A%20%7C%20SET3%3CBR%20%2F%3EA%20%7C%20B%20%7C%20SET2%3CBR%20%2F%3EC%20%7C%20B%20%7C%20SET4%3CBR%20%2F%3EB%20%7C%20A%20%7C%20SET3%3CBR%20%2F%3EC%20%7C%20B%20%7C%20SET4%3CBR%20%2F%3EAnd%20so%20on...%3CBR%20%2F%3Ehow%20can%20i%20get%20get%20different%20sets%20in%20col.3%20according%20to%20data%20in%20col.1%20%26amp%3B%20col.2.%3C%2FP%3E%3CP%3Ethanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-910556%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-910813%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20for%20getting%20different%20sets%20of%20data%20entered%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-910813%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F424911%22%20target%3D%22_blank%22%3E%40yogesh147ks%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20this%20%3CSTRONG%3EArray%20Formula%3C%2FSTRONG%3E%20which%20requires%20confirmation%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%3C%2FSTRONG%3E%20instead%20of%20Enter%20alone.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20C2%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(COUNTIFS(A%242%3AA2%2CA2%2CB%242%3AB2%2CB2)%3D1%2C%0A%22SET%22%26amp%3BSUM(--(FREQUENCY(MATCH(A%242%3AA2%26amp%3BB%242%3AB2%2CINDEX(A%242%3AA2%26amp%3BB%242%3AB2%2C)%2C0)%2CROW(A%242%3AA2)-ROW(%24A%242)%2B1)%26gt%3B0))%2C%0AINDEX(C%241%3AC1%2CMATCH(A2%26amp%3BB2%2CINDEX(A%241%3AA1%26amp%3BB%241%3AB1%2C)%2C0)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EConfirm%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%3C%2FSTRONG%3E%20and%20then%20copy%20it%20down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-910865%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20for%20getting%20different%20sets%20of%20data%20entered%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-910865%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F424911%22%20target%3D%22_blank%22%3E%40yogesh147ks%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20version%20of%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%22s%20solution%20file%2C%20the%20array%20formula%20(confirmed%20with%20Ctrl%2BShift%2BEnter)%20in%20D2%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(COUNTIFS(A%242%3AA2%2CA2%2CB%242%3AB2%2CB2)-1%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ELOOKUP(2%2C1%2F(A%241%3AA1%26amp%3BB%241%3AB1%3DA2%26amp%3BB2)%2CD%241%3AD1)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%22SET%22%26amp%3BMAX(IFERROR(--SUBSTITUTE(D%241%3AD1%2C%22SET%22%2C%22%22)%2C0))%2B1)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-911797%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20for%20getting%20different%20sets%20of%20data%20entered%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-911797%22%20slang%3D%22en-US%22%3E%3CP%3Eok%20got%20it%20formula%20working%20fine%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20lot.))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-911798%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20for%20getting%20different%20sets%20of%20data%20entered%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-911798%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eok%20got%20it%20formula%20working%20fine%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20lot.))%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-911800%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20for%20getting%20different%20sets%20of%20data%20entered%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-911800%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F424911%22%20target%3D%22_blank%22%3E%40yogesh147ks%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome.%3C%2FP%3E%3C%2FLINGO-BODY%3E
yogesh147ks
New Contributor

can anyone help me in this
col.1| col.2| col.3
A | A | SET1
A | B | SET2
B | A | SET3
A | B | SET2
C | B | SET4
B | A | SET3
C | B | SET4
And so on...
how can i get get different sets in col.3 according to data in col.1 & col.2.

thanks

5 Replies

@yogesh147ks 

Please try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

 

In C2

=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1,
"SET"&SUM(--(FREQUENCY(MATCH(A$2:A2&B$2:B2,INDEX(A$2:A2&B$2:B2,),0),ROW(A$2:A2)-ROW($A$2)+1)>0)),
INDEX(C$1:C1,MATCH(A2&B2,INDEX(A$1:A1&B$1:B1,),0)))

Confirm with Ctrl+Shift+Enter and then copy it down.

 

 

@yogesh147ks 

In the attached version of @Subodh_Tiwari_sktneer"s solution file, the array formula (confirmed with Ctrl+Shift+Enter) in D2 is: 

=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)-1,
LOOKUP(2,1/(A$1:A1&B$1:B1=A2&B2),D$1:D1),
"SET"&MAX(IFERROR(--SUBSTITUTE(D$1:D1,"SET",""),0))+1)

ok got it formula working fine 

 

Thanks a lot.))

 

ok got it formula working fine 

 

Thanks a lot.))

@Twifoo 

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