thanks for your help. I will think about the first question. "Gary''s Student" wrote: The answer to all three questions is "yes" The first question depends on what you want the count to be for specific values. For example: the cat and the dog compared with: the mouse and the flea and the rabbit There are two "the"s in the first string and three in the second. There is one "and" in the first string and two in the second. What should the function return??? The second question is easy; we can just substitute a space in-place-of a dash. The third question does not even need VBA. Assuming a single space between words, the count of words is the count of spaces plus one: =LEN(E13)-LEN(SUBSTITUTE(E13," ",""))+1 -- Gary''s Student - gsnu200904 "K" wrote: Show Thank you very much for your help. 1) Is there a way to count the same word just once? 2) Is there a way to remove all "-" in a string of words and keep all spaces? ex. aaa-bbb-ccc-ddd after the method would be aaa bbb ccc ddd 3) Is there a way to count how many words in a cell? ex. today is Tuesday, would have a result of 3 thanks, K "Gary''s Student" wrote: Here is a very small UDF: Public Function matchwords(r1 As Range, r2 As Range) As Integer v1 = r1.Value v2 = r2.Value s1 = Split(v1, " ") s2 = Split(v2, " ") matchwords = 0 For i = 0 To UBound(s1) vv1 = s1(i) For j = 0 To UBound(s2) vv2 = s2(j) If vv1 = vv2 Then matchwords = matchwords + 1 End If Next Next End Function So if A1 contains: Now is the time for all and B1 contains: time is on our side the formula: =matchwords(A1,B1) will display 2 NOTE: This is only a demo and not a full solution. For example, you need to be concerned with multiple matches: the compared to the the the Also if you want upeer/lower case to afffect the match. UDFs are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the UDF will be saved with it. To use the UDF from the normal Excel window, just enter it like a normal Excel Function To remove the UDF: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To learn more about UDFs, see: http://www.cpearson.com/excel/Writin...ionsInVBA.aspx -- Gary''s Student - gsnu200904 "K" wrote: Hi all, If I have name such as Fidelity Funds - Global Technology Fund in column A and name Technology Fund in column B. is there a way to present the result of total number of matching words in column C? In this case, column C would have 2 because of word Technology and Fund in Column A and B. I need to apply that method to 8000 records. Thanks,K
Hello Geeky, so today we are focusing on How To Compare Two Columns in Excel. So please read this tutorial carefully so you may comprehend it in a better helpful way. Guide: How To Compare Two Columns in ExcelAt some point when working with information in Excel, sometimes you should think about information. This can be a two-part view or even information in many books / worksheets. In this Excel tutorial, I will show you several recipes to think about two sections in Excel and find matches or variations. There are different ways to do this in Excel and in this tutorial exercise I will show you a section (such as comparing using the VLOOKUP formula or on the other hand IF recipe or location format). Microsoft Excel for Office 365
Microsoft Excel 2007 & 2010
Microsoft Excel 2003
FaqIn this guide, we told you about the How To Compare Two Columns in Excel; please read all steps above so that you understand How To Compare Two Columns in Excel in case if you need any assistance from us, then contact us. So in this guide, we discuss the How To Compare Two Columns in Excel, which undoubtedly benefits you. Final noteI hope you like the guide How To Compare Two Columns in Excel. In case if you have any queries regards this article/tutorial you may ask us. Also, please share your love by sharing this article with your friends and family.
Hi Sana, Try the following array formula: =SUM(($A$1:$A$5=A1)*($B$1:$B$5=B1)+($C$1:$C$5=A1)*($D$1:$D$5=B1)) Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Office for business Excel 2010 Excel 2007 More...Less You can use the following methods to compare data in two Microsoft Excel worksheet columns and find duplicate entries.
Warning: Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. To use a Visual Basic macro to compare the data in two columns, use the steps in the following example:
|