Hi friends,
I have one input file which consists 15000+ transaction also I have one template file which consists country with keywords. So I need to check Input column of each transaction to check any keywords from template file exist in input file. If keyword found then It will return Country name of matched keyword in Country1 /Country2 column as per match.
Note- If keyword found in Input transaction then it should be seperate word not substring of any word. E.g. (In given transaction xyz , sender- NaviMumbai)
So If searching keyword is Mumbai then…It will be false match.
Input File-
Input | Country1 | Country2 | Country3 | Country4 |
---|---|---|---|---|
Please check transaction xyz from Sender-Mumbai | India | |||
Please check transaction xyz from London and Received by Surat | England | India | ||
Please check transaction xyz from Sender-USA | ||||
Please check transaction xyz from Sender-wuhan | China | |||
Please check transaction xyz from Sender-USA | ||||
Please check transaction xyz from Sender-HK | ||||
Please check transaction xyz from Sender-Dhaka | ||||
Please check transaction xyz from Sender-USA | ||||
Please check transaction xyz from Sender-Dubai | ||||
Please check transaction xyz from Sender-USA | ||||
Please check transaction xyz from Sender-Berlin | Germany | |||
Please check transaction xyz from Sender-USA | ||||
Please check transaction xyz from Sender-Japan | ||||
Please check transaction xyz from Sender-USA | ||||
Please check transaction xyz from Sender-Islamabad | Pakistan | |||
Please check transaction xyz from Sender-USA | ||||
Please check transaction xyz from Sender-Dubai | ||||
Please check transaction xyz from Sender-USA | ||||
Please check transaction xyz from Sender-USA | ||||
Please check transaction xyz from Sender-Egypt |
Template file
Country | Keywords |
---|---|
India | IN,Mumbai,Delhi,Mysore,Surat,Hyderabad,Kolkata,Bangalore |
Iraq | IQ,BAGHDAD,BASRA,NAFJAF,KIRKUK,KARBALA,NASIRIYAS,AMARA |
China | CN,SHANGHAI,beijing,chongqing,guangzhou,tianjin,shenzhen,hangzhou,wuhan |
France | FRA,Paris,Lyon,Nice,Nantes,Reims,Lille |
England | UK,London,Manchester,Bristol,Liverpool,Southampton |
Russia | Moscow,Kazan,Omsk,Volgograd,Krasnoyarsk,Samara |
Afghanistan | AF,Kabul,kandahar,jalalabad,kandahar,herat,ghazni,khanabad |
Zimbabwe | Harare, Bulawayo, Manicaland, Midlands, Mashonaland West, Masvingo |
Germany | Berlin,Munich,Hamburg,stuttgart,essen,Leipzig |
Morocco | MA,Casablanca,Rabat,fez,Tangier,Agadir,kenitra,oujda,meknes |
Pakistan | Karachi,Lahore,Islamabad,Faisalabad,Peshawar,Multan,Bahawalpur,Sargodha |
Jamaica | JM,Kingston,Portmore,Montego,saint catherine,mandeville,maypen,holdharbour |
Mali | bamako,gao,sikasso,kalabancoro,koutiala,segou,kayes,kati,mopti,niono |
Iran | Hamadan, Ilam,Karaj, Tehran, East Azerbaijan, Semnan, Birjand, Bushehr |
Spain | Madrid,Barcelona,Seville,bilbao.valencia,cadiz,Girona |