Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
comparing two columns for partial values
Hello every, I have a question and would really appreciate if someone could
reply me ASAP. I have two columns in excel Coulmn A contains 500 cells for example Jane Doe, SOme Last name, Hellp there, Coulmn B contain 70 cells only with example Doe J, Lastname S, There H I only have 70 matches, but i don't know how to match those 70 cells to the 500 cells. I am looking for a partial match to check in cell A2 (for exmple) if there is DOE in cell B2 and return the value Yes in cell C2 May you please help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
comparing two columns for partial values
Try the FIND or SEARCH functions, FIND is case sensitive, SEARCH is not. eg
=IF(ISERROR(FIND("Doe",A1)),"No","Yes") will return 'Yes' for 'Doe', but not 'doe' =IF(ISERROR(SEARCH("Doe",A1)),"No","Yes") will return 'Yes' for 'Doe' or 'doe' Both will return 'No' if there is no match. Regards, Alan. "Trumpet" wrote in message ... Hello every, I have a question and would really appreciate if someone could reply me ASAP. I have two columns in excel Coulmn A contains 500 cells for example Jane Doe, SOme Last name, Hellp there, Coulmn B contain 70 cells only with example Doe J, Lastname S, There H I only have 70 matches, but i don't know how to match those 70 cells to the 500 cells. I am looking for a partial match to check in cell A2 (for exmple) if there is DOE in cell B2 and return the value Yes in cell C2 May you please help |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
comparing two columns for partial values
I don't want to type DOE in every cell, otherwise I would just search for
everyone and highlight them manually. I though there should be a search function which automatically search all the terms in one cell and compare it to the array in another coulmn. "Alan" wrote: Try the FIND or SEARCH functions, FIND is case sensitive, SEARCH is not. eg =IF(ISERROR(FIND("Doe",A1)),"No","Yes") will return 'Yes' for 'Doe', but not 'doe' =IF(ISERROR(SEARCH("Doe",A1)),"No","Yes") will return 'Yes' for 'Doe' or 'doe' Both will return 'No' if there is no match. Regards, Alan. "Trumpet" wrote in message ... Hello every, I have a question and would really appreciate if someone could reply me ASAP. I have two columns in excel Coulmn A contains 500 cells for example Jane Doe, SOme Last name, Hellp there, Coulmn B contain 70 cells only with example Doe J, Lastname S, There H I only have 70 matches, but i don't know how to match those 70 cells to the 500 cells. I am looking for a partial match to check in cell A2 (for exmple) if there is DOE in cell B2 and return the value Yes in cell C2 May you please help |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
comparing two columns for partial values
I'm not that sure what you mean. You used 'Doe' as an example so that's what
I used. The formulas will return 'Yes' if any cell contains 'Doe'. Change 'Doe' to whatever you want to search for, change the cell references to suit your lists. What do you mean by 'automatically search all the terms in one cell'? Please be more specific. Why you think you need to type 'Doe' into every cell I really don't know. Regards, Alan. "Trumpet" wrote in message ... I don't want to type DOE in every cell, otherwise I would just search for everyone and highlight them manually. I though there should be a search function which automatically search all the terms in one cell and compare it to the array in another coulmn. "Alan" wrote: Try the FIND or SEARCH functions, FIND is case sensitive, SEARCH is not. eg =IF(ISERROR(FIND("Doe",A1)),"No","Yes") will return 'Yes' for 'Doe', but not 'doe' =IF(ISERROR(SEARCH("Doe",A1)),"No","Yes") will return 'Yes' for 'Doe' or 'doe' Both will return 'No' if there is no match. Regards, Alan. "Trumpet" wrote in message ... Hello every, I have a question and would really appreciate if someone could reply me ASAP. I have two columns in excel Coulmn A contains 500 cells for example Jane Doe, SOme Last name, Hellp there, Coulmn B contain 70 cells only with example Doe J, Lastname S, There H I only have 70 matches, but i don't know how to match those 70 cells to the 500 cells. I am looking for a partial match to check in cell A2 (for exmple) if there is DOE in cell B2 and return the value Yes in cell C2 May you please help |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
comparing two columns for partial values
What I want is that I create one formula and paste it in all the 500 cells.
the formula will search in every cell of column A to find a partial match in column B |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
comparing two columns for partial values
The formulas I sent you will do that. Put one into the appropriate cell,
change the criteria to what you want to search for, adjust the cell references to suit and drag the formula down to the end of your list. "Trumpet" wrote in message ... What I want is that I create one formula and paste it in all the 500 cells. the formula will search in every cell of column A to find a partial match in column B |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
comparing two columns for partial values
no, I have to put in every cell what ever name is there that I want to search
for. May be I can not express well my request, but your answer is definitely not what I am looking for. Thanks anyway |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
comparing two columns for partial values
Life just isn't fair, is it Trumpet. To get quick, accurate solutions, all
you have to do is ask a clear question. Alan answered the question you asked. If it doesn't do what you want, you need to say why, and what you are looking for instead. Regards, Fred. "Trumpet" wrote in message ... no, I have to put in every cell what ever name is there that I want to search for. May be I can not express well my request, but your answer is definitely not what I am looking for. Thanks anyway |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing two columns of text values | Excel Discussion (Misc queries) | |||
comparing text values in two columns | Excel Discussion (Misc queries) | |||
Match Values in Rows with Partial Values in Columns | Excel Worksheet Functions | |||
Comparing values in two columns | Excel Discussion (Misc queries) | |||
Comparing values in columns | Excel Worksheet Functions |