Nov 09 2022 08:01 PM
Please help on how can we extract all texts that matching in 2 separate columns. Example as below
(1) Column A1(Country list 1): Japan, Korea, Vietnam, China, India, Singapore, Indonesia, Malaysia, UK, Ireland, Thailand
(2) Column A2(Country list 2): Japan, Ireland, UK, Russia, France, Scotland, Brazil, Spain, Mexico, Portugal
(3) Column A3 (All matching): Japan, Ireland, UK,
Nov 10 2022 01:12 AM
=IFERROR(IF(AND(MATCH(B2,$A$2:$A$11,0),MATCH(INDEX($A$2:$A$11,MATCH(B2,$A$2:$A$11,0)),$B$2:$B$11,0)),B2,""),"")
You can try this formula.
Nov 10 2022 02:05 AM
@OliverScheurichthank you for you advise, but my data something like below, have multiple rows, so need to filter the result by rows.....
1st column | 2nd column | matching |
a,b,c | a,c,z | a,c |
c,z.d | c,z | c,z |
d,e,e | e,d,k | d,e |
b,a,c | a,b | a,b |
a,b,c | a,c,z | a,c |
c,z.d | c,z | c,z |
d,e,e | e,d,k | d,e |
b,a,c | a,b | a,b |
a,b,c | a,c,z | a,c |
c,z.d | c,z | c,z |
d,e,e | e,d,k | d,e |
b,a,c | a,b | a,b |
Nov 11 2022 02:16 AM
You are welcome. This is a completely new question for which you can start a new discussion. My suggestion would be VBA code or Power Query like in the attached file.