Excel formula to return whats common in two text strings.

Jamel

Board Regular
Joined
Mar 2, 2010
Messages
55
Hello,

I'm looking for a way (excel formula) to compare two text strings and return what's in common between the two.

All the information is in one column so i will apply the formula in the column next to it and copy the formula down to the other rows.

[TABLE="width: 237"]
<colgroup><col width="237" style="width:178pt"> </colgroup><tbody>[TR]
[TD="width: 237"]M T[/TD]
[/TR]
[TR]
[TD]M T W[/TD]
[/TR]
[TR]
[TD]T W H

The answer is T

Any help would be appreciated



[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
So that vb code is referenced in the =common formula?
Yes, that is correct.


Interesting, little on the complex side but interesting.

Thank you for posting this.
You're welcome. I agree that regular expressions seem complex (& often are) as the "Pattern" often tends to look like gibberish & hence many people try to avoid them. However, sometimes they can do things much more directly than other methods so I try to keep practising where I can. :)
 
Upvote 0
Hello,
I decided to try to share the entire task IÂ’m attempting to solve vs just showing one step. I have a spreadsheet containing student schedules for varies schools. IÂ’ve created a pivot to sort the data per school per student to give an isolated look at each studentÂ’s schedule. IÂ’ve dedicated a column to show the days of the week the student attends a class in the following manner. EX:
M T W H F - every day
M H - Monday and Thursday only
T W - Tuesdays and Wednesdays only

Each class has listed beginning and ending times the class meets. What IÂ’m attempting to capture are the class passing time (which is the time a student has to move to the next class based on the end time of the previous class to the next class beginning time on the same meeting days and that the passing times are less than 30 minutes. So first I needed to capture classes that meets on the same days, which is the step I was first asking help with. Then I need to isolate occurrences when the passing time doesnÂ’t exceed 30 minutes.

[TABLE="width: 0"]
<tbody>[TR]
[TD][TABLE="width: 0"]
<tbody>[TR]
[TD]School[/TD]
[TD]lname[/TD]
[TD]fname[/TD]
[TD]std no[/TD]
[TD] days[/TD]
[TD]beg[/TD]
[TD]end[/TD]
[TD]class desc[/TD]
[TD]section no[/TD]
[TD]teacher[/TD]
[/TR]
[TR]
[TD]School 1[/TD]
[TD]doe1[/TD]
[TD]jane[/TD]
[TD]1111111[/TD]
[TD]F[/TD]
[TD]10:00 AM[/TD]
[TD]10:45 AM[/TD]
[TD]ART[/TD]
[TD]ART0[/TD]
[TD]teacher 1[/TD]
[/TR]
[TR]
[TD]School 1[/TD]
[TD]doe1[/TD]
[TD]jane[/TD]
[TD]1111111[/TD]
[TD]H[/TD]
[TD]2:15 PM[/TD]
[TD]3:00 PM[/TD]
[TD]Technology ASA1[/TD]
[TD]CMS0[/TD]
[TD]teacher 1[/TD]
[/TR]
[TR]
[TD]School 1[/TD]
[TD]doe1[/TD]
[TD]jane[/TD]
[TD]1111111[/TD]
[TD]M[/TD]
[TD]1:20 PM[/TD]
[TD]2:05 PM[/TD]
[TD]1st Grade Music ASA[/TD]
[TD]MUS0[/TD]
[TD]teacher 1[/TD]
[/TR]
[TR]
[TD]School 1[/TD]
[TD]doe1[/TD]
[TD]jane[/TD]
[TD]1111111[/TD]
[TD]M T[/TD]
[TD]2:15 PM[/TD]
[TD]3:00 PM[/TD]
[TD]Physical Education[/TD]
[TD]GYM0[/TD]
[TD]teacher 1[/TD]
[/TR]
[TR]
[TD]School 1[/TD]
[TD]doe1[/TD]
[TD]jane[/TD]
[TD]1111111[/TD]
[TD]T F[/TD]
[TD]1:05 PM[/TD]
[TD]1:35 PM[/TD]
[TD]Spanish[/TD]
[TD]SPN0[/TD]
[TD]teacher 1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 8"]Jane Doe1 has three passing times I need to capture. On (M) Monday 1st Grade Music end time 2:00 p.m[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 8"]to Physical Education beg time 2:15 pm is a passing that of 15 minutes. I can claim this for funding[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 8"]On (T) Tuesday there is a passing time from Spanish class end time of 1:35 p.m. to Physical Ed beg[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 8"]time of 2:15 p.m. end that totals 40 minutes. I'm not allowed to include this for funding because[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]the passing time exceeds 30 minutes.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 8"]On (F) I need to capture Art class end time of 10:45 a.m. and Spanish class beginning time of 1:45 p.m. but[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 7"]Im not allowed to include this for funding because the passing time exceeds 30 minutes.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]School 2[/TD]
[TD]doe2[/TD]
[TD]jane[/TD]
[TD]1111112[/TD]
[TD]M T[/TD]
[TD]8:52 AM[/TD]
[TD]9:32 AM[/TD]
[TD]Physical Education[/TD]
[TD]GYM0[/TD]
[TD]teacher 2[/TD]
[/TR]
[TR]
[TD]School 2[/TD]
[TD]doe2[/TD]
[TD]jane[/TD]
[TD]1111112[/TD]
[TD]M T[/TD]
[TD]10:17 AM[/TD]
[TD]10:57 AM[/TD]
[TD]Spanish[/TD]
[TD]SPN0[/TD]
[TD]teacher 2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 7"]Jane Doe2 has one passing times I need to capture on two days. On (M) Monday and [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 9"](T) Physical ED end time (9:32 a.m.) to Spanish class beg time of 10:17 a.m. which total 45 minutes passing time. [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 7"]I'm not allowed to include this for funding because the passing time exceeds 30 minutes.[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]School 3[/TD]
[TD]doe3[/TD]
[TD]jane[/TD]
[TD]1111113[/TD]
[TD]F[/TD]
[TD]10:00 AM[/TD]
[TD]10:45 AM[/TD]
[TD]ART[/TD]
[TD]ART0[/TD]
[TD]teacher 3[/TD]
[/TR]
[TR]
[TD]School 3[/TD]
[TD]doe3[/TD]
[TD]jane[/TD]
[TD]1111113[/TD]
[TD]H[/TD]
[TD]2:15 PM[/TD]
[TD]3:00 PM[/TD]
[TD]Technology ASA1[/TD]
[TD]CMS0[/TD]
[TD]teacher 3[/TD]
[/TR]
[TR]
[TD]School 3[/TD]
[TD]doe3[/TD]
[TD]jane[/TD]
[TD]1111113[/TD]
[TD]M[/TD]
[TD]1:20 PM[/TD]
[TD]2:05 PM[/TD]
[TD]1st Grade Music ASA[/TD]
[TD]MUS0[/TD]
[TD]teacher 3[/TD]
[/TR]
[TR]
[TD]School 3[/TD]
[TD]doe3[/TD]
[TD]jane[/TD]
[TD]1111113[/TD]
[TD]M T[/TD]
[TD]2:15 PM[/TD]
[TD]3:00 PM[/TD]
[TD]Physical Education[/TD]
[TD]GYM0[/TD]
[TD]teacher 3[/TD]
[/TR]
[TR]
[TD]School 3[/TD]
[TD]doe3[/TD]
[TD]jane[/TD]
[TD]1111113[/TD]
[TD]T F[/TD]
[TD]1:05 PM[/TD]
[TD]1:35 PM[/TD]
[TD]Spanish[/TD]
[TD]SPN0[/TD]
[TD]teacher 3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 8"]would calculate passing time on M, T, and F. Can only include passing time at 30 minutes or under[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]School 4[/TD]
[TD]doe4[/TD]
[TD]jane[/TD]
[TD]1111114[/TD]
[TD]M[/TD]
[TD]9:35 AM[/TD]
[TD]10:15 AM[/TD]
[TD]Kinder Music ASA[/TD]
[TD]MUS0[/TD]
[TD]teacher 4[/TD]
[/TR]
[TR]
[TD]School 4[/TD]
[TD]doe4[/TD]
[TD]jane[/TD]
[TD]1111114[/TD]
[TD]T H[/TD]
[TD]8:53 AM[/TD]
[TD]9:32 AM[/TD]
[TD]Physical Education[/TD]
[TD]GYM0[/TD]
[TD]teacher 4[/TD]
[/TR]
[TR]
[TD]School 4[/TD]
[TD]doe4[/TD]
[TD]jane[/TD]
[TD]1111114[/TD]
[TD]W F[/TD]
[TD]1:54 PM[/TD]
[TD]2:34 PM[/TD]
[TD]Spanish[/TD]
[TD]SPN0[/TD]
[TD]teacher 4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]Has no days that qualify to calculate passing time.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]School 5[/TD]
[TD]doe5[/TD]
[TD]jane[/TD]
[TD]1111115[/TD]
[TD]M[/TD]
[TD]1:20 PM[/TD]
[TD]2:05 PM[/TD]
[TD]1st Grade Music ASA[/TD]
[TD]MUS0[/TD]
[TD]teacher 5[/TD]
[/TR]
[TR]
[TD]School 5[/TD]
[TD]doe5[/TD]
[TD]jane[/TD]
[TD]1111115[/TD]
[TD]F[/TD]
[TD]10:00 AM[/TD]
[TD]10:45 AM[/TD]
[TD]ART[/TD]
[TD]ART0[/TD]
[TD]teacher 5[/TD]
[/TR]
[TR]
[TD]School 5[/TD]
[TD]doe5[/TD]
[TD]jane[/TD]
[TD]1111115[/TD]
[TD]H[/TD]
[TD]2:15 PM[/TD]
[TD]3:00 PM[/TD]
[TD]Technology ASA1[/TD]
[TD]CMS0[/TD]
[TD]teacher 5[/TD]
[/TR]
[TR]
[TD]School 5[/TD]
[TD]doe5[/TD]
[TD]jane[/TD]
[TD]1111115[/TD]
[TD]M T[/TD]
[TD]2:15 PM[/TD]
[TD]3:00 PM[/TD]
[TD]Physical Education[/TD]
[TD]GYM0[/TD]
[TD]teacher 5[/TD]
[/TR]
[TR]
[TD]School 5[/TD]
[TD]doe5[/TD]
[TD]jane[/TD]
[TD]1111115[/TD]
[TD]T F[/TD]
[TD]1:05 PM[/TD]
[TD]1:35 PM[/TD]
[TD]Spanish[/TD]
[TD]SPN0[/TD]
[TD]teacher 5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 8"]would calculate passing time on M, T, and F. Can only include passing time at 30 minutes or under[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I hope I did a better job at explaining things this time. I apologize if I have not.
 
Last edited:
Upvote 0
See how this goes. Test with a copy of your workbook.
For data in columns A:J below, the code produced the results in columns M:V
Each pass time of 30 minutes or less is reported as a pair of lines showing the which day the conflict is on & lists the 2 subject in the order they occur on that day.
Note that I have altered the end time of the last row of the sample data to produce one extra conflict.

Code:
Sub FindShortPassTimes()
  Dim RX As Object
  Dim a As Variant, b As Variant, m As Variant
  Dim i As Long, j As Long, k As Long, x As Long, y As Long
  Dim s1End As Date, s2Start As Date, PassTimeMin As Date
  
  PassTimeMin = TimeSerial(0, 30, 0)
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  a = Range("A1", Range("J" & Rows.Count).End(xlUp).Offset(1)).Value
  ReDim b(1 To UBound(a, 2), 1 To 1)
  k = -2
  For i = 2 To UBound(a) - 1
    If a(i, 4) = a(i + 1, 4) Then
      RX.Pattern = "[" & Replace(a(i, 5), " ", "") & "]"
      For Each m In RX.Execute(a(i + 1, 5))
        If a(i, 6) < a(i + 1, 6) Then
          s1End = a(i, 7): s2Start = a(i + 1, 6)
          x = i: y = i + 1
        Else
          s1End = a(i + 1, 7): s2Start = a(i, 6)
          x = i + 1: y = i
        End If
        If s2Start - s1End <= PassTimeMin Then
          k = k + 3
          ReDim Preserve b(1 To UBound(b), 1 To k + 1)
          For j = 1 To UBound(b)
            b(j, k) = a(x, j): b(j, k + 1) = a(y, j)
          Next j
          b(5, k) = m: b(5, k + 1) = m
        End If
      Next m
    End If
    If k > 0 Then
      With Range("M1:V1")
        .Value = Range("A1:j1").Value
        With .Offset(1).Resize(k + 1)
          .Value = Application.Transpose(b)
          .EntireColumn.AutoFit
          .Columns(6).Resize(, 2).NumberFormat = "h:mm AM/PM"
        End With
      End With
    End If
  Next i
End Sub

Excel Workbook
ABCDEFGHIJKLMNOPQRSTUV
1Schoollnamefnamestd nodaysbegendclass descsection noteacherSchoollnamefnamestd nodaysbegendclass descsection noteacher
2School 1doe1jane1111111F10:00 AM10:45 AMARTART0teacher 1School 1doe1jane1111111M1:20 PM2:05 PM1st Grade Music ASAMUS0teacher 1
3School 1doe1jane1111111H2:15 PM3:00 PMTechnology ASA1CMS0teacher 1School 1doe1jane1111111M2:15 PM3:00 PMPhysical EducationGYM0teacher 1
4School 1doe1jane1111111M1:20 PM2:05 PM1st Grade Music ASAMUS0teacher 1
5School 1doe1jane1111111M T2:15 PM3:00 PMPhysical EducationGYM0teacher 1School 3doe3jane1111113M1:20 PM2:05 PM1st Grade Music ASAMUS0teacher 3
6School 1doe1jane1111111T F1:05 PM1:35 PMSpanishSPN0teacher 1School 3doe3jane1111113M2:15 PM3:00 PMPhysical EducationGYM0teacher 3
7School 2doe2jane1111112M T8:52 AM9:32 AMPhysical EducationGYM0teacher 2
8School 2doe2jane1111112M T10:17 AM10:57 AMSpanishSPN0teacher 2School 5doe5jane1111115T1:05 PM1:55 PMSpanishSPN0teacher 5
9School 3doe3jane1111113F10:00 AM10:45 AMARTART0teacher 3School 5doe5jane1111115T2:15 PM3:00 PMPhysical EducationGYM0teacher 5
10School 3doe3jane1111113H2:15 PM3:00 PMTechnology ASA1CMS0teacher 3
11School 3doe3jane1111113M1:20 PM2:05 PM1st Grade Music ASAMUS0teacher 3
12School 3doe3jane1111113M T2:15 PM3:00 PMPhysical EducationGYM0teacher 3
13School 3doe3jane1111113T F1:05 PM1:35 PMSpanishSPN0teacher 3
14School 4doe4jane1111114M9:35 AM10:15 AMKinder Music ASAMUS0teacher 4
15School 4doe4jane1111114T H8:53 AM9:32 AMPhysical EducationGYM0teacher 4
16School 4doe4jane1111114W F1:54 PM2:34 PMSpanishSPN0teacher 4
17School 5doe5jane1111115M1:20 PM2:05 PM1st Grade Music ASAMUS0teacher 5
18School 5doe5jane1111115F10:00 AM10:45 AMARTART0teacher 5
19School 5doe5jane1111115H2:15 PM3:00 PMTechnology ASA1CMS0teacher 5
20School 5doe5jane1111115M T2:15 PM3:00 PMPhysical EducationGYM0teacher 5
21School 5doe5jane1111115T F1:05 PM1:55 PMSpanishSPN0teacher 5
Sheet3
 
Upvote 0
.....you are simply amazing!!!!

Took a bit of time to run on all 23,000 records so I'm using it as primarily an analytical tool until i run it on the total spreadsheet again.

I'm speechless and forever greatful.

I wish i knew how to do what you did.
Amazing
 
Upvote 0
.....you are simply amazing!!!!

I'm speechless and forever greatful.

I wish i knew how to do what you did.
Amazing
I am glad you are so pleased with it, but the following is my fault. :(

Took a bit of time to run on all 23,000 records ...
While testing, I had the results written to the sheet each time one was found (that is a slow process) instead of just waiting and writing all the results at the end. :oops:
(I also didn't realise that you had so much data. :))

Swapping the position of the 'Next i' line from the red position to the blue should make a massive difference to the run-time of the code.
Rich (BB code):
Sub FindShortPassTimes()
  Dim RX As Object
  Dim a As Variant, b As Variant, m As Variant
  Dim i As Long, j As Long, k As Long, x As Long, y As Long
  Dim s1End As Date, s2Start As Date, PassTimeMin As Date
  
  PassTimeMin = TimeSerial(0, 30, 0)
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  a = Range("A1", Range("J" & Rows.Count).End(xlUp).Offset(1)).Value
  ReDim b(1 To UBound(a, 2), 1 To 1)
  k = -2
  For i = 2 To UBound(a) - 1
    If a(i, 4) = a(i + 1, 4) Then
      RX.Pattern = "[" & Replace(a(i, 5), " ", "") & "]"
      For Each m In RX.Execute(a(i + 1, 5))
        If a(i, 6) < a(i + 1, 6) Then
          s1End = a(i, 7): s2Start = a(i + 1, 6)
          x = i: y = i + 1
        Else
          s1End = a(i + 1, 7): s2Start = a(i, 6)
          x = i + 1: y = i
        End If
        If s2Start - s1End <= PassTimeMin Then
          k = k + 3
          ReDim Preserve b(1 To UBound(b), 1 To k + 1)
          For j = 1 To UBound(b)
            b(j, k) = a(x, j): b(j, k + 1) = a(y, j)
          Next j
          b(5, k) = m: b(5, k + 1) = m
        End If
      Next m
    End If
  Next i
  If k > 0 Then
    With Range("M1:V1")
      .Value = Range("A1:j1").Value
      With .Offset(1).Resize(k + 1)
        .Value = Application.Transpose(b)
        .EntireColumn.AutoFit
        .Columns(6).Resize(, 2).NumberFormat = "h:mm AM/PM"
      End With
    End With
  End If
  <del>Next i</del>
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,826
Messages
6,181,192
Members
453,021
Latest member
pingpong7117

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