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:
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:
//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 Excel
At 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
- Open an Excel spreadsheet containing the data you want to compare.
- Select all cells in both columns that have data to compare.
- How to select one or more cells in a county plan.
- In the Ribbon, on Home tab, locate the Adjusting section and click Search & Select.
- In the drop-down menu, select Go to Special.
- In the Go to special pop-up window, click the row variations, then click OK. Excel selects the differences between the two columns.
- To highlight these variations, leave them selected, then add a bucket icon in the font menu and select the color you want to use. Alternatively, you can right-click and select the full bucket to display the cells.
Microsoft Excel 2007 & 2010
- Open an Excel spreadsheet containing the data you want to compare.
- Select all cells in both columns that have data to compare.
- How to select one or more cells in a county plan.
- In the Ribbon, on Home tab, go to Search & Select, then click Go.
- In Go To Pop-upup window, click the row variations, then click OK. Excel selects the difference between the two columns.
- To highlight these variations, leave them selected, then add a bucket icon in the font menu and select the color you want to use. Alternatively, you can right-click and select the full bucket to display the cells.
Microsoft Excel 2003
- Open an Excel spreadsheet containing the data you want to compare.
- Select all the cells in both columns that have data in them to compare.
- How to select one or more cells in a county plan.
- In the file list at the top of the settings window, click Edit and select Go.
- In the Go to special pop-up window, click the row variations, then click OK. Excel selects the difference between the two columns.
- To highlight these variations, leave them selected, then add a bucket icon in the font menu and select the color you want to use. Alternatively, you can right-click and select the full bucket to display the cells.
Faq
In 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 note
I 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))
Note: array formula's need to be confirmed by hitting Ctrl+Shift+Enter. When done correctly the formula will start and end with curly brackets {}.
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.
-
Start Excel.
-
In a new worksheet, enter the following data as an example (leave column B empty):
A
B
C
1
1
3
2
2
5
3
3
8
4
4
2
5
5
0
-
Type the following formula in cell B1:
=IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1)
-
Select cell B1 to B5.
-
In Excel 2007 and later versions of Excel, select Fill in the Editing group, and then select Down.
The duplicate numbers are displayed in column B, as in the following example:
A
B
C
1
1
3
2
2
2
5
3
3
3
8
4
4
2
5
5
5
0
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:
-
Start Excel.
-
Press ALT+F11 to start the Visual Basic editor.
-
On the Insert menu, select Module.
-
Enter the following code in a module sheet:
Sub Find_Matches() Dim CompareRange As Variant, x As Variant, y As Variant ' Set CompareRange equal to the range to which you will ' compare the selection. Set CompareRange = Range("C1:C5") ' NOTE: If the compare range is located on another workbook ' or worksheet, use the following syntax. ' Set CompareRange = Workbooks("Book2"). _ ' Worksheets("Sheet2").Range("C1:C5") ' ' Loop through each cell in the selection and compare it to ' each cell in CompareRange. For Each x In Selection For Each y In CompareRange If x = y Then x.Offset(0, 1) = x Next y Next x End Sub -
Press ALT+F11 to return to Excel.
-
Enter the following data as an example (leave column B empty):
A
B
C
1
1
3
2
2
5
3
3
8
4
4
2
5
5
0
-
-
Select cell A1 to A5.
-
In Excel 2007 and later versions of Excel, select the Developer tab, and then select Macros in the Code group.
Note: If you don't see the Developer tab, you may have to turn it on. To do this, select File > Options > Customize Ribbon, and then select the Developer tab in the customization box on the right-side.
-
Click Find_Matches, and then click Run.
The duplicate numbers are displayed in column B. The matching numbers will be put next to the first column, as illustrated here:
A
B
C
1
1
3
2
2
2
5
3
3
3
8
4
4
2
5
5
5
0