• 466K Members
• 9,298 Online
• 563K Conversations

New Contributor

# formula for getting different sets of data entered

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

# Re: formula for getting different sets of data entered

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.

# Re: formula for getting different sets of data entered

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)

# Re: formula for getting different sets of data entered

ok got it formula working fine

Thanks a lot.))

# Re: formula for getting different sets of data entered

ok got it formula working fine

Thanks a lot.))

# Re: formula for getting different sets of data entered

You're welcome.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies