Forum Discussion
Common Values in 3 Columns
Dear Experts,
I have a data like below, in Col-A/B/C and in Col-E , I want the common rnti's from A&B&C Col,
Thanks in Advance,
Br,
Anupam
Couple of more with similar logic from here How to find common values in 3 columns in Excel? and here Finding common values in 3 columns | MrExcel Message Board
=IFNA( INDEX( $A$2:$A$19, MATCH(0, COUNTIF($H$1:H1, $A$2:$A$19) + IF( IF( COUNTIF($B$2:$B$19, $A$2:$A$19)>0,1,0) + IF(COUNTIF($C$2:$C$8, $A$2:$A$19)>0,1,0)=2,0,1 ), 0 ) ), "" )and
=IFERROR( INDEX( $A$1:$A$19, SMALL( IF( ISNUMBER( MATCH( IF( ISNUMBER( MATCH($A$1:$A$19,$B$1:$B$19,0)),$A$1:$A$19), $C$1:$C$8, 0 ) ), ROW($A$1:$A$19) ), ROWS(E$1:E1)) ), "")
13 Replies
Hi everyone!
One option with "Legacy Formula" could be this:
=IFERROR(AGGREGATE(15,6,A$2:A$19/(MMULT(COUNTIF(OFFSET(A$2:A$19,,{1,2}),A$2:A$19),{1;1})=2),ROWS(E$2:E2)),"")This formula assumes that the numbers in each column are unique, just like in the example. Blessings!
=唯一(过滤器(A2:A20, (COUNTIF(B2:B20, A2:A20) * COUNTIF(C2:C20, A2:A20) >= 1)))试试这样是不是也可以
=唯一(过滤器(A2:A20, (COUNTIF(B2:B20, A2:A20) * COUNTIF(C2:C20, A2:A20) >= 1)))Couple of more with similar logic from here How to find common values in 3 columns in Excel? and here Finding common values in 3 columns | MrExcel Message Board
=IFNA( INDEX( $A$2:$A$19, MATCH(0, COUNTIF($H$1:H1, $A$2:$A$19) + IF( IF( COUNTIF($B$2:$B$19, $A$2:$A$19)>0,1,0) + IF(COUNTIF($C$2:$C$8, $A$2:$A$19)>0,1,0)=2,0,1 ), 0 ) ), "" )and
=IFERROR( INDEX( $A$1:$A$19, SMALL( IF( ISNUMBER( MATCH( IF( ISNUMBER( MATCH($A$1:$A$19,$B$1:$B$19,0)),$A$1:$A$19), $C$1:$C$8, 0 ) ), ROW($A$1:$A$19) ), ROWS(E$1:E1)) ), "")Thanks Everyone, Can I request for some Legacy formula , not using LET/LAMBDA..?
Br,
Anupam
Try this legacy formula.
=IF(COUNTIFS($A$2:$A$19,A2)*COUNTIFS($B$2:$B$19,A2)*COUNTIFS($C$2:$C$19,A2),A2,"")
Hi everyone!
One option could be:
=TOCOL(MAP(UNIQUE(TOCOL(A2:C19,1)),LAMBDA(x,x/AND(BYCOL(x=A2:C19,OR)))),2)Blessings!
Another approach using MMULT() function.
=LET(arr,A2:C19,u,UNIQUE(TOCOL(arr,1)),x,MAP(u,LAMBDA(r,AND(MMULT(SEQUENCE(1,ROWS(arr),,0),--(arr=r))))),FILTER(u,x))Try the following formula-
=LET(x,UNIQUE(TOCOL(A2:C19,1)),y,XMATCH(x,A2:A19)+XMATCH(x,B2:B19)+XMATCH(x,C2:C19),FILTER(x,ISNUMBER(y)))You could use a number of SCAN or REDUCE type of LAMBDA functions to loop through each list but here is what I think is a little more general option. I also assumed that each list could have duplicates themselves:
=LET(in_1, O1:O15, in_2, P1:P15, in_3, Q1:Q15, ustack, VSTACK(UNIQUE(in_1),UNIQUE(in_2),UNIQUE(in_3)), Duplicates, LAMBDA(array,[number], LET(num, IF(ISOMITTED(number),1,number), counts, MMULT(--(array=TRANSPOSE(array)),SEQUENCE(ROWS(array),,1,0)), UNIQUE(FILTER(array,(array<>"")*(counts>=num),"no dups")) )), Duplicates(ustack,3) )and you just need to have row 1 and row 2 match the actual input columns (both in range reference and number of input columns) and then on row 8 that number 3 matches the number of columns.
Here it is in action:
Can you explain in details what you want exactly?
Example:- if 792 is common in all Columns, the populate 792 in E etc.. So, list all the values which are there present in all columns,
