Find and count all cells with same date that contain some/most of the same text string

Patty3-4

New Member
Joined
Feb 5, 2013
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]DATE
[/TD]
[TD]FREE TEXT ENTRY
[/TD]
[TD]FORMULALEFT
[/TD]
[TD]FORMULAMID
[/TD]
[TD]FORMULARIGHT
[/TD]
[TD]FORMULAMID
[/TD]
[/TR]
[TR]
[TD]09/01/18
[/TD]
[TD]234 park st northwest pl1235
[/TD]
[TD]=LEFT(B3,10)
[/TD]
[TD]=MID(B3,5,11)
[/TD]
[TD]=RIGHT(B3,10)
[/TD]
[TD]=MID(B3,6,8)
[/TD]
[/TR]
[TR]
[TD]09/01/18
[/TD]
[TD]pl1235 park st nw 234
[/TD]
[TD]=LEFT(B4,10)
[/TD]
[TD]=MID(B4,5,11)
[/TD]
[TD]=RIGHT(B4,10)
[/TD]
[TD]=MID(B4,6,8)
[/TD]
[/TR]
[TR]
[TD]09/02/18
[/TD]
[TD]park st 234 85 leafing rd
[/TD]
[TD]=LEFT(B5,10)
[/TD]
[TD]=MID(B5,5,11)
[/TD]
[TD]=RIGHT(B5,10)
[/TD]
[TD]=MID(B5,6,8)
[/TD]
[/TR]
[TR]
[TD]09/02/18
[/TD]
[TD]cycle 20 beard park st
[/TD]
[TD]=LEFT(B6,10)
[/TD]
[TD]=MID(B6,5,11)
[/TD]
[TD]=RIGHT(B6,10)
[/TD]
[TD]=MID(B6,6,8)
[/TD]
[/TR]
[TR]
[TD]09/03/18
[/TD]
[TD]123 red cup blvd pl9090
[/TD]
[TD]=LEFT(B7,10)
[/TD]
[TD]=MID(B7,5,11)
[/TD]
[TD]=RIGHT(B7,10)
[/TD]
[TD]=MID(B7,6,8)
[/TD]
[/TR]
[TR]
[TD]09/03/18
[/TD]
[TD]left 123 blue avenue pl902
[/TD]
[TD]=LEFT(B8,10)
[/TD]
[TD]=MID(B8,5,11)
[/TD]
[TD]=RIGHT(B8,10)
[/TD]
[TD]=MID(B8,6,8)
[/TD]
[/TR]
[TR]
[TD]09/03/18
[/TD]
[TD]red cup blvd 123 pl9090
[/TD]
[TD]=LEFT(B9,10)
[/TD]
[TD]=MID(B9,5,11)
[/TD]
[TD]=RIGHT(B9,10)
[/TD]
[TD]=MID(B9,6,8)
[/TD]
[/TR]
[TR]
[TD]09/04/18
[/TD]
[TD]89 e san juan pl northeast
[/TD]
[TD]=LEFT(B10,10)
[/TD]
[TD]=MID(B10,5,11)
[/TD]
[TD]=RIGHT(B10,10)
[/TD]
[TD]=MID(B10,6,8)
[/TD]
[/TR]
</tbody>[/TABLE]
Good morning,

I have a table which contains a:

COLUMN A Date column
COLUMN B text string column
COLUMN C a column with a LEFT formula that extracts 10 characters
COLUMN D a column with a MID formula that extracts 11 characters, after 5 spaces
COLUMN E a column with a RIGHT formula that extracts 10 characters
COLUMN F another column with a MID formula that extracts 8 characters, after 6 spaces

The problem and target is COLUMN B. I want to find all entries that match most or all of the words in this cell but this is a free text field and 100 employees have completed the entries in various order. Want to count all of those with the same entry as 1 but only if it has the same date.

I created COLUMNS C through COLUMN F in a effort to cut up the text string and then search for it throughout the table but I'm not doing a good job of it NOR have I been able to ONLY count if has the same date.

For example the table below shows:
2 for 09/01/2018 of those with "234" "park" "st"
1 for 09/02/2018 of those with "park" "st" "234" "85" "leafing" "rd"
1 for 09/02/2018 of those with "cycle" "20" "beard" "park" "st"
1 for 09/03/2018 of those with "123" "red" "cup" "blvd"
1 for 09/03/2018 of those with "left" "123" "blue"
1 for 09/03/2018 of those with "red" "cup" "123"
1 for 09/04/2018 of those with "89 e san juan pl"

I know I cannot use all of the words/contents located in the cell but would like to find most. I would appreciate your help immensely...I have 3 years of data like this.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
i explained that horribly didn't I? sorry, in other words, I want to
find and count all
text strings in an array
that equal text in a referenced cell
....please help....
 
Upvote 0
I want to find all entries that match most or all of the words in this cell ..
Not too sure just how you would define the underlined part above, but see if this user-defined function gets you anywhere near what you want. The final argument in the function sets the minimum number of 'words' that must match to add to the count. In my sample I have set that to 3 but you can experiment with that number.

To implement the UDF ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Code:
Function CountMatches(rData As Range, dDate As Date, FreeText As String, minMatches As Long) As Long
  Static RX As Object
  Dim a As Variant
  Dim i As Long
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
  End If
  a = rData.Value
  RX.Pattern = "\b(" & Replace(FreeText, " ", "|") & ")\b"
  For i = 1 To UBound(a)
    If a(i, 1) = dDate Then
      If RX.Execute(a(i, 2)).Count >= minMatches Then CountMatches = CountMatches + 1
    End If
  Next i
End Function

Excel Workbook
ABC
1DATEFREE TEXT ENTRYCOUNT
201-Sep-18234 park st northwest pl12352
301-Sep-18pl1235 park st nw 2342
402-Sep-18park st 234 85 leafing rd1
502-Sep-18cycle 20 beard park st1
603-Sep-18123 red cup blvd pl90902
703-Sep-18left 123 blue avenue pl9021
803-Sep-18red cup blvd 123 pl90902
904-Sep-1889 e san juan pl northeast1
Sheet1
 
Upvote 0
Good morning! thank you for replying! I entered both the code, formula and saved it as a macro enabled workbook but am getting a "#Name?" error. Any ideas?
 
Upvote 0
Good morning! thank you for replying! I entered both the code, formula and saved it as a macro enabled workbook but am getting a "#Name?" error. Any ideas?
That error would normally indicate that the function name in the vba code and the function name that you used in the worksheet are not the same. For example, it might be CountMatches in the code and you might have used Count Matches or ContMatches in the worksheet. Check spelling and spaces carefully.

What version of Excel & what operating system are you using?
 
Upvote 0
That error would normally indicate that the function name in the vba code and the function name that you used in the worksheet are not the same. For example, it might be CountMatches in the code and you might have used Count Matches or ContMatches in the worksheet. Check spelling and spaces carefully.

What version of Excel & what operating system are you using?

I copied and pasted both the code and formula just to make sure but still get the #Name error. I'm using Windows 7 Professional and have Excel 2013. One this I noticed from this formula though is that I still wouldn't be able to count them individually as 1 count (as unique) because the next row also states 2. Is there a way to have it to provide a total count as the first instance it comes across for the date and all subsequent would not have a "1" for count?
 
Upvote 0
I'm sorry, I haven't been able to spend time on the forum for a while and that will continue for a while longer.
 
Upvote 0

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top