Excel formula to return whats common in two text strings.

J

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.

M T
M T W
T W H

The answer is T

Any help would be appreciated




<colgroup><col width="237" style="width:178pt"> </colgroup><tbody>
</tbody>
 
Sort by date Sort by votes

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Jamel said:
So that vb code is referenced in the =common formula?
Click to expand...
Yes, that is correct.


Coding4Fun said:
Interesting, little on the complex side but interesting.

Thank you for posting this.
Click to expand...
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.

Schoollnamefnamestd no daysbegendclass descsection noteacher
School 1doe1jane1111111F10:00 AM10:45 AMARTART0teacher 1
School 1doe1jane1111111H2:15 PM3:00 PMTechnology ASA1CMS0teacher 1
School 1doe1jane1111111M1:20 PM2:05 PM1st Grade Music ASAMUS0teacher 1
School 1doe1jane1111111M T2:15 PM3:00 PMPhysical EducationGYM0teacher 1
School 1doe1jane1111111T F1:05 PM1:35 PMSpanishSPN0teacher 1
Jane Doe1 has three passing times I need to capture. On (M) Monday 1st Grade Music end time 2:00 p.m
to Physical Education beg time 2:15 pm is a passing that of 15 minutes. I can claim this for funding
On (T) Tuesday there is a passing time from Spanish class end time of 1:35 p.m. to Physical Ed beg
time of 2:15 p.m. end that totals 40 minutes. I'm not allowed to include this for funding because
the passing time exceeds 30 minutes.
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
Im not allowed to include this for funding because the passing time exceeds 30 minutes.
School 2doe2jane1111112M T8:52 AM9:32 AMPhysical EducationGYM0teacher 2
School 2doe2jane1111112M T10:17 AM10:57 AMSpanishSPN0teacher 2
Jane Doe2 has one passing times I need to capture on two days. On (M) Monday and
(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.
I'm not allowed to include this for funding because the passing time exceeds 30 minutes.
School 3doe3jane1111113F10:00 AM10:45 AMARTART0teacher 3
School 3doe3jane1111113H2:15 PM3:00 PMTechnology ASA1CMS0teacher 3
School 3doe3jane1111113M1:20 PM2:05 PM1st Grade Music ASAMUS0teacher 3
School 3doe3jane1111113M T2:15 PM3:00 PMPhysical EducationGYM0teacher 3
School 3doe3jane1111113T F1:05 PM1:35 PMSpanishSPN0teacher 3
would calculate passing time on M, T, and F. Can only include passing time at 30 minutes or under
School 4doe4jane1111114M9:35 AM10:15 AMKinder Music ASAMUS0teacher 4
School 4doe4jane1111114T H8:53 AM9:32 AMPhysical EducationGYM0teacher 4
School 4doe4jane1111114W F1:54 PM2:34 PMSpanishSPN0teacher 4
Has no days that qualify to calculate passing time.
School 5doe5jane1111115M1:20 PM2:05 PM1st Grade Music ASAMUS0teacher 5
School 5doe5jane1111115F10:00 AM10:45 AMARTART0teacher 5
School 5doe5jane1111115H2:15 PM3:00 PMTechnology ASA1CMS0teacher 5
School 5doe5jane1111115M T2:15 PM3:00 PMPhysical EducationGYM0teacher 5
School 5doe5jane1111115T F1:05 PM1:35 PMSpanishSPN0teacher 5
would calculate passing time on M, T, and F. Can only include passing time at 30 minutes or under

<tbody>
</tbody>

<tbody>
</tbody>


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
Jamel said:
.....you are simply amazing!!!!

I'm speechless and forever greatful.

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

Jamel said:
Took a bit of time to run on all 23,000 records ...
Click to expand...
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
You must log in or register to reply here.

Similar threads

C
  • Question
if " value in field name" is blank or under 100 return 0 otherwise return 1 - how do I get this formula to work
Replies
7
Views
172
Scott Huish
Scott Huish
N
  • Question
trying to use only 1 formula to populate the information in various columns which are not adjacent to each other.
Replies
3
Views
83
noaman79
N
F
  • Question
How to search strings larger than lookup strings?
Replies
2
Views
360
Fractalis
F
SaraWitch
  • Solved
Join text and return dates where there is one but ignore if empty cells
2
Replies
12
Views
543
SaraWitch
SaraWitch
J
  • Question
Complex Move data from two spreadsheets with similar data in different columns to a specific column in an existing spreadsheet(Sheet 3)
Replies
0
Views
110
JMC57
J

Forum statistics

Threads
1,214,905
Messages
6,122,172
Members
449,071
Latest member
cdnMech

Share this page

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