LinkBack URL
About LinkBacks
All,
I am new to this forum and I need your assistance.
I have attached an excel file (2 sheets) which explains everything about the problem.
Could you please suggest me some formula to solve it.
Regards
Velu
All,
I am new to this forum and I need your assistance.
I have attached an excel file (2 sheets) which explains everything about the problem.
Could you please suggest me some formula to solve it.
Regards
Velu
Hi,
in Sheet2 Newyork is misspelled, better New york
A possible formula in C2 (Sheet2) to be copied down
=--(COUNTIF(Sheet1!$A$2:$A$100,"*"&A2&"*")>0)
Hope it helps
Edit: marketcity in A9 Sheet2 could create wrong results
Last edited by canapone; 05-26-2014 at 04:24 AM.
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
Hi,
Need one more help.
What if I want to display '5' instead of '0' in result.
Hi,
not sure to have understood, it's an attempt
Regards
=IF(COUNTIF(Sheet1!$A$2:$A$100,"*"&A2&"*"),1,5)
Hi,
Many thanks for your quick reply. It works very well
Thank you once again.
But this logic is not working at all cells.
Regards
Velu
Hi,
thanks for feebdack. Maybe it could help a little file and some examples of expected results.
Regards
Hi,
I have attached the same file with some wrong example.
On the other hand is it possible to use 'keyword' functionality for the same problem..
Regards
Velu
Hi,
in the example you attached you could try
=IF(COUNTIF(Sheet1!$A$2:$A$100,"*"&SUBSTITUTE(A2," ","*")&"*"),1,5)
Hope that helps
Thank you for your outstanding work.
As my project database is huge I would like you to tweak the formula further as per my attached example.
In sheet 2 name if any word matches with the database then it should display the value as '1'
Once again thank you very much..
Hi,
thanks for your feedback.
I'm afraid of not being able to help you.
If i've have understood you are looking to match any single substring in A2:A5
In A2 "New's york" cannot match New york project.
I hope you'll get better answers than mine.
regards
By the way, New york is incorrect: it should be New York (both upper case).
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
canapone's countif formula works great for your latest example.
Formula:
=IF(COUNTIF(Sheet1!$A$2:$A$100,"*"&A2&"*"),1,5)
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Thank you for your reply Fotis & AliGW.
May be I should have explained it in a better way.
In sheet 1 I have the database for important projects (100s of project).
In Sheet 2 daily I use to make entry for different projects from different countries. In case if any of the project name in sheet 2 matches with the project database in sheet 1 means then the formula result has to be '1' else '5'
Mostly sheet 2 project name slighly varies with the project name in sheet 1. This is the main problem. I have given some examples in my previous thread.
Can you now suggest me some formula?
I make 1 more try and ask you.
What is that which is not working in my example sheet?
I have attached some examples in your file.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks