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

[Patty3-4]

A	B	C	D	E	F
DATE	FREE TEXT ENTRY	FORMULALEFT	FORMULAMID	FORMULARIGHT	FORMULAMID
09/01/18	234 park st northwest pl1235	=LEFT(B3,10)	=MID(B3,5,11)	=RIGHT(B3,10)	=MID(B3,6,8)
09/01/18	pl1235 park st nw 234	=LEFT(B4,10)	=MID(B4,5,11)	=RIGHT(B4,10)	=MID(B4,6,8)
09/02/18	park st 234 85 leafing rd	=LEFT(B5,10)	=MID(B5,5,11)	=RIGHT(B5,10)	=MID(B5,6,8)
09/02/18	cycle 20 beard park st	=LEFT(B6,10)	=MID(B6,5,11)	=RIGHT(B6,10)	=MID(B6,6,8)
09/03/18	123 red cup blvd pl9090	=LEFT(B7,10)	=MID(B7,5,11)	=RIGHT(B7,10)	=MID(B7,6,8)
09/03/18	left 123 blue avenue pl902	=LEFT(B8,10)	=MID(B8,5,11)	=RIGHT(B8,10)	=MID(B8,6,8)
09/03/18	red cup blvd 123 pl9090	=LEFT(B9,10)	=MID(B9,5,11)	=RIGHT(B9,10)	=MID(B9,6,8)
09/04/18	89 e san juan pl northeast	=LEFT(B10,10)	=MID(B10,5,11)	=RIGHT(B10,10)	=MID(B10,6,8)

<tbody>
</tbody>

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.

 

[Patty3-4]

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....

 

[Peter_SSs]

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)

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
	A	B	C
1	DATE	FREE TEXT ENTRY	COUNT
2	01-Sep-18	234 park st northwest pl1235	2
3	01-Sep-18	pl1235 park st nw 234	2
4	02-Sep-18	park st 234 85 leafing rd	1
5	02-Sep-18	cycle 20 beard park st	1
6	03-Sep-18	123 red cup blvd pl9090	2
7	03-Sep-18	left 123 blue avenue pl902	1
8	03-Sep-18	red cup blvd 123 pl9090	2
9	04-Sep-18	89 e san juan pl northeast	1
Sheet1

 

[Patty3-4]

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?

 

[Peter_SSs]

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?

 

[Patty3-4]

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?

 

[Peter_SSs]

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.