• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search data in Column

A

Abhijeet

Active Member
I have data in Column E i want Search that data in Column A if that match then put Yes in front in Column B how to do this please suggest me
 

Attachments

  • Search.xlsx
    9 KB · Views: 6
Attached your sheet with the formula. If not matched, I have put "No". In case you do not want "no" then replace it with "" in the formula.

Raju
 

Attachments

  • Search.xlsx
    9.4 KB · Views: 8
No its not work what i want this work if same number found in same row if that number in next row or that column E then also result i want Yes
 
Hi Abhijeet,

Try using in B2 and copy down this array formula.

=IF(ISNUMBER(MATCH(1,IF(ISNUMBER(SEARCH(E2,$A$2:$A$7)),1,0),0)),"YES","NO")

Enter with Ctrl+Shift+Enter and not just Enter.

Regards,
 
Hi Abhijeet ,

Try this array formula , entered using CTRL SHIFT ENTER :

=IF(OR(ISNUMBER(SEARCH($E$2:$E$7&" ",$A2&" "))),"Yes","No")

The above formula will not work if the search string / number is followed by a period "." in the text which is being searched in column A.

Narayan
 
Hi Narayan your given formula is work fine but if i have data like Word then this formula not work this formula working for Search the number.Please tell me for search the word or Number what ever in Column E
 
Narayan If this kind of Sentence 01.02.2014 fffg Batch S001.tif in Column E Search Batch S then not work.If i search with Batch then work
 
Try in B2 and copy down,

=IF(ISNUMBER(SEARCH("Batch S",A2)),"Salary",IF(ISNUMBER(SEARCH("Batch T",A2)),"Travel","NA"))

And one question, does you column A data will always be in this format?

Regards,
 
In B2,
=LOOKUP(MID(A2,SEARCH("Batch ?",A2)+6,1),{"S","Salary";"T","Travel"})

In C2,
=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),SEARCH("Batch",SUBSTITUTE(A2," ",REPT(" ",99)))-198,100))*1
 
I
Debraj said:
In B2,
=LOOKUP(MID(A2,SEARCH("Batch ?",A2)+6,1),{"S","Salary";"T","Travel"})

In C2,
=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),SEARCH("Batch",SUBSTITUTE(A2," ",REPT(" ",99)))-198,100))*1
Click to expand...
If position is not fix then this formula not work
 
* From "Yes" output > Now you required "Salary / Travel".
* From Matching a number, > now you required Number before Batch.
* from a provide sample > Now your Format is not always same.
* base of search "BATCH", its position is now not fixed..

are you already started playing HOLI..
Is it working in your PROVIDED REQUIRED OUTPUT...!!!!
 
@Abhijeet

Abhijeet said:
If position is not fix then this formula not work
Click to expand...

Whose position are you talking about?

Try these formula:

in B2: =IF(ISNUMBER(SEARCH("Batch S",A2)),"Salary",IF(ISNUMBER(SEARCH("Batch T",A2)),"Travel","NA"))

in C2: =TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),SEARCH("Batch",SUBSTITUTE(A2," ",REPT(" ",LEN(A2))))-(2*LEN(A2)),2*LEN(A2)))+0

Just advise if any issue.

Regards,
 
NARAYANK991 said:
Hi Abhijeet ,

Try this array formula , entered using CTRL SHIFT ENTER :

=IF(OR(ISNUMBER(SEARCH($E$2:$E$7&" ",$A2&" "))),"Yes","No")

The above formula will not work if the search string / number is followed by a period "." in the text which is being searched in column A.

Narayan
Click to expand...
Hi Narayan This formula not work when more than one Number is in Statement If i search 5002 & statement 191 Z afgfg 5002 hjgh.xls then this formula not work
 
Hi Abhijeet ,

If your requirements are clear , can you upload a sample workbook including samples of all the possible data variations. Instead of pointing out the shortcomings of each formula one by one , if you upload your requirements first , whoever is posting a formula can first test it on all your samples.

If you cannot provide sample data , please specify the data characteristics :

1. How long will the data , search text be ?

2. Will either or both be only numeric , only alphabetic , or a mixture of the two ?

3. What should the output be if the searched for item is present in the data ?

4. Any other detail that you can provide.

Narayan
 
You must log in or register to reply here.
Back
Top