Find which of multiple Unique Text Strings are present within a Longer Text String

[britz]

Im using Excel 2007 & Windows 7. I do not have any programing or VBA experience.

I have managed to be able to find when the "Unique Text String" is present in a "Longer Text String" by using the function =SEARCH(TRIM(D3),TRIM(A3)).
The "Longer Text String" varies in length & sometimes contains extra spaces, hence the need for the Trim operation.

The issue I have, is that I now want to be able to search each "Longer Text String" & identify which of the "Unique Text Strings" is present, then tell me the corresponding related No.
If it were only 5 "Unique Text Strings", then it would be easy to create 5 columns, one for each of the "Unique Text Strings" to the right & customise the formula for each "Unique Test String" in each column.

The tricky bit is there are over 60 "Unique Text Strings" & thousands of "Longer Text Strings". So I would like to do this in a lot less than 60 separate functions.
I hope you can give me some ideas?


[TABLE="width: 500"]
<tbody>[TR]
[TD]Longer Text Strings
[/TD]
[TD][/TD]
[TD]No.
[/TD]
[TD]Unique Test String
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]The Brown Fox in the box 1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]Brown Fox
[/TD]
[/TR]
[TR]
[TD]The Black Cat on the mat
[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD]Black Cat
[/TD]
[/TR]
[TR]
[TD]The Bird in the Tree
[/TD]
[TD][/TD]
[TD]3
[/TD]
[TD]Tree
[/TD]
[/TR]
[TR]
[TD]The Snake on the Grass
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]Snake
[/TD]
[/TR]
[TR]
[TD]The man on the moon
[/TD]
[TD][/TD]
[TD]5
[/TD]
[TD]Moon
[/TD]
[/TR]
[TR]
[TD]The Brown Owl on the Barn
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]The Dog fighting the Cat
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]The Small Tree in the Garden
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]The Brown Fox in the box 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[hiker95]

britz,

Welcome to the MrExcel forum.

How about a very fast macro solution using arrays in memory? The macro compares lower case Unique Test String's against lower case Longer Text Strings.

Sample raw data:

Excel 2007
	A	B	C	D	E
1	Longer Text Strings		No.	Unique Test String
2	The Brown Fox in the box 1		1	Brown Fox
3	The Black Cat on the mat		2	Black Cat
4	The Bird in the Tree		3	Tree
5	The Snake on the Grass		4	Snake
6	The man on the moon		5	Moon
7	The Brown Owl on the Barn
8	The Dog fighting the Cat
9	The Small Tree in the Garden
10	The Brown Fox in the box 2
11
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E
1	Longer Text Strings		No.	Unique Test String
2	The Brown Fox in the box 1		1	Brown Fox	2
3	The Black Cat on the mat		2	Black Cat	1
4	The Bird in the Tree		3	Tree	2
5	The Snake on the Grass		4	Snake	1
6	The man on the moon		5	Moon	1
7	The Brown Owl on the Barn
8	The Dog fighting the Cat
9	The Small Tree in the Garden
10	The Brown Fox in the box 2
11
Sheet1

If you added another Unique Test String:

Excel 2007
	A	B	C	D	E
1	Longer Text Strings		No.	Unique Test String
2	The Brown Fox in the box 1		1	Brown Fox	2
3	The Black Cat on the mat		2	Black Cat	1
4	The Bird in the Tree		3	Tree	2
5	The Snake on the Grass		4	Snake	1
6	The man on the moon		5	Moon	1
7	The Brown Owl on the Barn		6	on
8	The Dog fighting the Cat
9	The Small Tree in the Garden
10	The Brown Fox in the box 2
11
Sheet1

And, ran the macro again:

Excel 2007
	A	B	C	D	E
1	Longer Text Strings		No.	Unique Test String
2	The Brown Fox in the box 1		1	Brown Fox	2
3	The Black Cat on the mat		2	Black Cat	1
4	The Bird in the Tree		3	Tree	2
5	The Snake on the Grass		4	Snake	1
6	The man on the moon		5	Moon	1
7	The Brown Owl on the Barn		6	on	4
8	The Dog fighting the Cat
9	The Small Tree in the Garden
10	The Brown Fox in the box 2
11
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub CountUniques()
' hiker95, 04/10/2013
' http://www.mrexcel.com/forum/excel-questions/696092-find-multiple-unique-text-strings-present-within-longer-text-string.html
Dim a As Variant, d As Variant
Dim lr As Long, i As Long, ii As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
a = Range("A2:A" & lr)
lr = Cells(Rows.Count, 4).End(xlUp).Row
Range("E2:E" & lr).ClearContents
d = Range("D2:E" & lr)
For i = 1 To UBound(a, 1)
  For ii = 1 To UBound(d, 1)
    If InStr(LCase(a(i, 1)), LCase(d(ii, 1))) > 0 Then
      d(ii, 2) = d(ii, 2) + 1
    End If
  Next ii
Next i
Range("D2").Resize(UBound(d, 1), UBound(d, 2)) = d
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CountUniques macro.

 

[britz]

Hi hiker95,

Thank you for taking time to read & answer my query.

My error in not clarifying. I would like the result in Column B for the Search in each "Longer Text String" in Column A.


Thank you & Regards,

Britz

 

[Akashwani]

Hi Britz and welcome to MrExcel.

Is this what you require?...

Sample data...

Excel Workbook
	A	B	C	D	E
1	Longer Text Strings	Results	No.	Unique Test String	*
2	The Brown Fox in the box 1	1	1	Brown Fox	*
3	The Black Cat on the mat	3	2	Black Cat	*
4	The Bird in the Tree	1	3	Tree	*
5	The Snake on the Grass	2	4	Snake	*
6	The man on the moon	2	5	Moon	*
7	The Brown Owl on the Barn	1	6	Cat	*
8	The Dog fighting the Cat	1	7	On	*
9	The Small Tree in the Garden	1	*	*	*
10	The Brown Fox in the box 2	1	*	*	*
11	*	*	*	*	*
Sheet2

The formula in B2 needs to be copied down.
UniqString is a Named Range based on the data in Column D
To create a Named Range...
Highlight your range, right click, click Name a Range, type in a name like UniqString into the white box, click OK.

I hope this helps.

Ak

 

[Sandeep Warrier]

Also how about

Excel 2010
	A	B	C	D	E
1	Longer Text Strings	Required Result		No.	Unique Test String
2	The Brown Fox in the box 1	1		1	Brown Fox
3	The Black Cat on the mat	2		2	Black Cat
4	The Bird in the Tree	3		3	Tree
5	The Snake on the Grass	4		4	Snake
6	The man on the moon	5		5	Moon
7	The Brown Owl on the Barn	7		6	Dog
8	The Dog fighting the Cat	6		7	Owl
9	The Small Tree in the Garden	3
10	The Brown Fox in the box 2	1
Sheet1

Cell Formulas
Range	Formula
B2	=LOOKUP(9.99E+307,SEARCH($E$2:$E$8,A2),$D$2:$D$8)

 

[hiker95]

britz,

My error in not clarifying. I would like the result in Column B for the Search in each "Longer Text String" in Column A.

Thank you.

Sample raw data:

Excel 2007
	A	B	C	D
1	Longer Text Strings		No.	Unique Test String
2	The Brown Fox in the box 1		1	Brown Fox
3	The Black Cat on the mat		2	Black Cat
4	The Bird in the Tree		3	Tree
5	The Snake on the Grass		4	Snake
6	The man on the moon		5	Moon
7	The Brown Owl on the Barn
8	The Dog fighting the Cat
9	The Small Tree in the Garden
10	The Brown Fox in the box 2
11
Sheet1

After the macro:

Excel 2007
	A	B	C	D
1	Longer Text Strings		No.	Unique Test String
2	The Brown Fox in the box 1	1	1	Brown Fox
3	The Black Cat on the mat	2	2	Black Cat
4	The Bird in the Tree	3	3	Tree
5	The Snake on the Grass	4	4	Snake
6	The man on the moon	5	5	Moon
7	The Brown Owl on the Barn
8	The Dog fighting the Cat
9	The Small Tree in the Garden	3
10	The Brown Fox in the box 2	1
11
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub CountUniquesV2()
' hiker95, 04/11/2013
' http://www.mrexcel.com/forum/excel-questions/696092-find-multiple-unique-text-strings-present-within-longer-text-string.html
Dim a As Variant, b As Variant, d As Variant
Dim lr As Long, i As Long, ii As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
a = Range("A2:A" & lr)
lr = Cells(Rows.Count, 4).End(xlUp).Row
Range("E2:E" & lr).ClearContents
d = Range("C2:D" & lr)
ReDim b(1 To UBound(a, 1), 1 To 1)
For i = 1 To UBound(a, 1)
  For ii = 1 To UBound(d, 1)
    If InStr(LCase(a(i, 1)), LCase(d(ii, 2))) > 0 Then
      b(i, 1) = d(ii, 1)
    End If
  Next ii
Next i
Range("B2").Resize(UBound(b, 1)) = b
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the CountUniquesV2 macro.

 

[hiker95]

britz,

If we started with this:

Excel 2007
	A	B	C	D
1	Longer Text Strings		No.	Unique Test String
2	The Brown Fox in the box 1		1	Brown Fox
3	The Black Cat on the mat		2	Black Cat
4	The Bird in the Tree		3	Tree
5	The Snake on the Grass		4	Snake
6	The man on the moon		5	Moon
7	The Brown Owl on the Barn
8	The Dog fighting the Cat
9	The Small Tree in the Garden
10	The Brown Fox in the box 2
11
Sheet1

And ran the below newest macro we would get this:

Excel 2007
	A	B	C	D
1	Longer Text Strings		No.	Unique Test String
2	The Brown Fox in the box 1	1	1	Brown Fox
3	The Black Cat on the mat	2	2	Black Cat
4	The Bird in the Tree	3	3	Tree
5	The Snake on the Grass	4	4	Snake
6	The man on the moon	5	5	Moon
7	The Brown Owl on the Barn
8	The Dog fighting the Cat
9	The Small Tree in the Garden	3
10	The Brown Fox in the box 2	1
11
Sheet1

If you added #6, on to the list like this:

Excel 2007
	A	B	C	D
1	Longer Text Strings		No.	Unique Test String
2	The Brown Fox in the box 1	1	1	Brown Fox
3	The Black Cat on the mat	2	2	Black Cat
4	The Bird in the Tree	3	3	Tree
5	The Snake on the Grass	4	4	Snake
6	The man on the moon	5	5	Moon
7	The Brown Owl on the Barn		6	on
8	The Dog fighting the Cat
9	The Small Tree in the Garden	3
10	The Brown Fox in the box 2	1
11
Sheet1

And, ran the macro again, you would get this:

Excel 2007
	A	B	C	D
1	Longer Text Strings		No.	Unique Test String
2	The Brown Fox in the box 1	1	1	Brown Fox
3	The Black Cat on the mat	2,6	2	Black Cat
4	The Bird in the Tree	3	3	Tree
5	The Snake on the Grass	4,6	4	Snake
6	The man on the moon	5,6	5	Moon
7	The Brown Owl on the Barn	6	6	on
8	The Dog fighting the Cat
9	The Small Tree in the Garden	3
10	The Brown Fox in the box 2	1
11
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub CountUniquesV3()
' hiker95, 04/11/2013
' http://www.mrexcel.com/forum/excel-questions/696092-find-multiple-unique-text-strings-present-within-longer-text-string.html
Dim a As Variant, b As Variant, d As Variant
Dim lr As Long, i As Long, ii As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
a = Range("A2:A" & lr)
lr = Cells(Rows.Count, 4).End(xlUp).Row
Range("E2:E" & lr).ClearContents
d = Range("C2:D" & lr)
ReDim b(1 To UBound(a, 1), 1 To 1)
For i = 1 To UBound(a, 1)
  For ii = 1 To UBound(d, 1)
    If InStr(LCase(a(i, 1)), LCase(d(ii, 2))) > 0 Then
      If b(i, 1) = "" Then
        b(i, 1) = d(ii, 1)
      Else
        b(i, 1) = b(i, 1) & "," & d(ii, 1)
      End If
    End If
  Next ii
Next i
Range("B2").Resize(UBound(b, 1)) = b
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CountUniquesV3 macro.

 

[britz]

Hi AK,

Thanks for this.
Ive tried this on my real data set & not the sample set in this post, so im not sure if there is a hitch with my data set?
It appears to pick-up some of the Unique Test Strings, but all others result in Number ones in Column B, when they should be otherwise.

Thanks for your time & interest.

Regards,
Britz

 

[britz]

Hi Sandeep,

Thanks for this one. Wow, It works correctly on my real data set. I dint think I was going to get a working solution.
Only thing is that the result is the actual "Unique Text String". How would I return the Adjacent Number as per Column D in the above example?

Thank you & Kind Regards

Britz

 

[hiker95]

britz,

How would I return the Adjacent Number as per Column D in the above example?

Sample raw data (per the above quote):

Excel 2007
	A	B	C	D	E
1	Longer Text Strings			No.	Unique Test String
2	The Brown Fox in the box 1			1	Brown Fox
3	The Black Cat on the mat			2	Black Cat
4	The Bird in the Tree			3	Tree
5	The Snake on the Grass			4	Snake
6	The man on the moon			5	Moon
7	The Brown Owl on the Barn
8	The Dog fighting the Cat
9	The Small Tree in the Garden
10	The Brown Fox in the box 2
11
Sheet1

After the below posted macro:

Excel 2007
	A	B	C	D	E
1	Longer Text Strings			No.	Unique Test String
2	The Brown Fox in the box 1	1		1	Brown Fox
3	The Black Cat on the mat	2		2	Black Cat
4	The Bird in the Tree	3		3	Tree
5	The Snake on the Grass	4		4	Snake
6	The man on the moon	5		5	Moon
7	The Brown Owl on the Barn
8	The Dog fighting the Cat
9	The Small Tree in the Garden	3
10	The Brown Fox in the box 2	1
11
Sheet1

If you added item #6, like this:

Excel 2007
	A	B	C	D	E
1	Longer Text Strings			No.	Unique Test String
2	The Brown Fox in the box 1	1		1	Brown Fox
3	The Black Cat on the mat	2		2	Black Cat
4	The Bird in the Tree	3		3	Tree
5	The Snake on the Grass	4		4	Snake
6	The man on the moon	5		5	Moon
7	The Brown Owl on the Barn			6	on
8	The Dog fighting the Cat
9	The Small Tree in the Garden	3
10	The Brown Fox in the box 2	1
11
Sheet1

And, ran the macro again, you would get this:

Excel 2007
	A	B	C	D	E
1	Longer Text Strings			No.	Unique Test String
2	The Brown Fox in the box 1	1		1	Brown Fox
3	The Black Cat on the mat	2,6		2	Black Cat
4	The Bird in the Tree	3		3	Tree
5	The Snake on the Grass	4,6		4	Snake
6	The man on the moon	5,6		5	Moon
7	The Brown Owl on the Barn	6		6	on
8	The Dog fighting the Cat
9	The Small Tree in the Garden	3
10	The Brown Fox in the box 2	1
11
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub CountUniquesV4()
' hiker95, 04/11/2013
' http://www.mrexcel.com/forum/excel-questions/696092-find-multiple-unique-text-strings-present-within-longer-text-string.html
Dim a As Variant, b As Variant, d As Variant
Dim lr As Long, i As Long, ii As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
a = Range("A2:A" & lr)
Range("B2:B" & lr).ClearContents
lr = Cells(Rows.Count, 4).End(xlUp).Row
d = Range("D2:E" & lr)
ReDim b(1 To UBound(a, 1), 1 To 1)
For i = 1 To UBound(a, 1)
  For ii = 1 To UBound(d, 1)
    If InStr(LCase(a(i, 1)), LCase(d(ii, 2))) > 0 Then
      If b(i, 1) = "" Then
        b(i, 1) = d(ii, 1)
      Else
        b(i, 1) = b(i, 1) & "," & d(ii, 1)
      End If
    End If
  Next ii
Next i
Range("B2").Resize(UBound(b, 1)) = b
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CountUniquesV4 macro.