Oct 12 2019 05:36 AM
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
Oct 12 2019 11:21 AM
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.
Oct 12 2019 01:01 PM
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)
Oct 14 2019 02:44 AM
ok got it formula working fine
Thanks a lot.))
Oct 14 2019 02:45 AM
Oct 14 2019 02:50 AM
You're welcome.