Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi I have two columns in two excels...
excel1
---------
COL1 |
WIRE |
DISH |
BLANKET |
BAG |
DO |
HNET |
INTNET |
KITCHEN |
excel2
---------
COL2 |
NOMATCHING |
WIRED |
WIRE TECHNOLOGIES |
DISH NETWORKS |
BAGGAGE |
DOING |
INTERNET |
APPLE |
NETFLIX |
AMAZON |
DELL |
MAC |
CLEAR |
JNJ |
CAT |
JIO |
LEVIS |
Now i need to check if each word from COL1 as a match %like% in COL2 .. result should be like this,,,
COL2 | COL3 |
NOMATCHING | |
WIRED | WIRE |
WIRE TECHNOLOGIES | WIRE |
DISH NETWORKS | DISH |
BAGGAGE | BAG |
DOING | DO |
INTERNET | |
APPLE | |
NETFLIX | |
AMAZON | |
KITCHENWARE | KITCHEN |
MAC | |
CLEAR | |
JNJ | |
CAT | |
JIO | |
HNET TECH | HNET |
appreciate your help.
Wildmatch will be your friend.
Try this,
COL1:
LOAD * INLINE [
COL1
WIRE
DISH
BLANKET
BAG
DO
HNET
INTNET
KITCHEN
];
COL2:
LOAD * INLINE [
COL2
NOMATCHING
WIRED
WIRE TECHNOLOGIES
DISH NETWORKS
BAGGAGE
DOING
INTERNET
GOOGLE
APPLE
NETFLIX
FACEBOOK
AMAZON
DELL
MAC
CLEAR
JNJ
CAT
JIO
LEVIS
];
Dim:
COL2
Exp:
Pick(WildMatch(COL2,$(=Concat('''*'&COL1&'*''',','))),$(=Concat(Chr(39)&COL1&Chr(39),',')))
Output: