Find text in one column = add "#, " to different column?

[Jennifre]

Hello Excel experts... I thank you in advance!

I need to find words in column U, and if found, add comma-space-assigned number in column R (in same row).
(without disturbing content already in column R. I've begun doing this manually.)

Example of what I'm trying to do: (Would love to have #s added in order, but if can't, that's okay)
worksheet "tags" (partial)
[TABLE="class: cms_table_head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH="align: center"][/TH]
[TH="align: center"]A
[/TH]
[TH="align: center"]B
[/TH]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1​

​

[/TD]
[TD]cat[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​

[/TD]
[TD]sat[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​

[/TD]
[TD]on[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​

[/TD]
[TD]mouse[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​

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


[/COLOR] worksheet "join"
Existing #s , #s added via this new trick
(It's okay if the numbers are added to column R at the end of what's there already, esp if I'm able to order
them later?)
[TABLE="class: cms_table_head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH="align: center"][/TH]
[TH="align: center"][/TH]
[TH="align: center"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]R[/COLOR]
[/TH]
[TH="align: center"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]U[/COLOR]
[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​

[/TD]
[TD][/TD]
[TD]1, 2, 4, 5, 21, [/TD]
[TD]more... the cat sat on the mouse ...more[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​

[/TD]
[TD][/TD]
[TD]1, 4, 5, 21, 28, [/TD]
[TD]more... cat ate the mouse ...more[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​

[/TD]
[TD][/TD]
[TD]1, 2, 4, 5, 21, 22, [/TD]
[TD]more... the mouse sat under the cat ...more[/TD]
[/TR]
</tbody>[/TABLE]



​Thank you!!

 

[Snakehips]

@Jennifre
What is the likely column range of the tags, A = tag, B = number ???
Any header row?
Are the tag numbers sequential? 1 to whatever?

What is the starting row for 'join' U text, excluding any header row?

 

[sergioMabres]

Here is an idea,
For Office 365 you can use the Matrix Formula
={TEXTJOIN(", ",1,IFERROR(FIND(tags!A2:A6,U2)>0,0)*tags!B2:B6)}

It is not exactingly what you want but it is a start

Numbers			Text
1, 0, 3, 4, 5			more... the cat sat on the mouse ...more
0, 0, 4, 5, 0			more... cat ate the mouse ...more
0, 4, 5, 0, 0			more... the mouse sat under the cat ...more

<tbody>
</tbody>

Here is the test file I created to test the problem https://1drv.ms/x/s!AovCE1fDrrdSnGf2BVprSo2cN7ny?e=Krl1dO


Cheers
Sergio

 

[sergioMabres]

And if you want use this formula (No matrix formula this time)
=SUBSTITUTE(SUBSTITUTE(R2,"0, ",""),", 0","")

To get exactly what you asked for

Improved result

1, 2, 3, 4, 5

4, 5

4, 5

<tbody>
</tbody>

(Same test file)
Cheers
Sergio

 

[sergioMabres]

Sorry you need to anchor the ranges to copy the formula down
={TEXTJOIN(", ",1,IFERROR(FIND(tags!$A$2:$A$6,U2)>0,0)*tags!$B$2:$B$6)}

(Same test file)
Like this
Cheers
Sergio

 

[Jennifre]

@Snakehips -- thank you for your response! I'm so grateful for your query; I just hope I will understand your answer (not terribly Excel-savvy, lately, need to catch up!)

What is the likely column range of the tags, A = tag, B = number ???
If I'm understanding "column range" correctly, there are only 2 columns. A is numbers, and C are tags. **Just to be difficult, some of the words in column B will need to be found as a phrase, or when there are multiple words per cell (often) ANY of the words found there will need to indicate the number in column A of that same row.
Any header row?
Let's pretend there will be: HeaderOne, HeaderTwo, etc
Are the tag numbers sequential? 1 to whatever?
Yes, from row 1 down, on worksheet "tags".
What is the starting row for 'join' U text, excluding any header row?
Row 2

 

[Snakehips]

@sergioMabres Sergio your formula will struggle with eg ' more… can their dog catch mouse? …more '

@Jennifre I have a simple vba code that will identify the tag number A for a word in C and or a phrase in B
I am unsure about the need to modify your current, partial, manual result in R?
Is there a real need to ADD to the tag numbers you have done manually?
If the tag list is complete, eg includes tags for #21 ,22, 28 etc, then they will they not automatically be included in the fresh result?
Or, will the tag list vary and you will perform different runs and need to accumulate the final result?

 

[Snakehips]

Excel 2010

	A	B	C
a phrase	CAT
another one	sat
yet another phrase	on
number four	mouse
that is all now	the

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Tags​

This...
Excel 2010

	R	S	T	U
1, 21, 28,	more… cat ate the mouse …more
4, 21,	more… the cat sat on the mouse … more
1, 21, 28,	more… cat ate the mouse …more
4, 21, 22,	more… the mouse sat under the cat …more
more… looking for a phrase this time …more
more.. Maybe yet another one ...more
more.. Another what? Yet Another Phrase! …More
1, 12,	more… can their dog catch mouse? …more
"stuff the cat!" …………. Said Mouse

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]43,[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Join​

To this...
Excel 2010

	R	S	T	U
1, 4, 5, 21, 28,	more… cat ate the mouse …more
1, 2, 3, 4, 5, 21,	more… the cat sat on the mouse … more
1, 4, 5, 21, 28,	more… cat ate the mouse …more
1, 2, 4, 5, 21, 22,	more… the mouse sat under the cat …more
more… looking for a phrase this time …more
more.. Maybe yet another one ...more
more.. Another what? Yet Another Phrase! …More
1, 4, 12,	more… can their dog catch mouse? …more
1, 4, 5,	"stuff the cat!" …………. Said Mouse

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]43,[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Join​

Using this code..

Sub Jennifre()
Dim AOne() As String, ATwo() As String, AThree(0 To 50) As String  '50 max on found numbers ????
Dim TagArry As Variant
Dim LasTag As Integer, LastU As Integer
Dim TestStr As String, Str1 As String, Str2 As String
Dim e, f, g, i, r As Integer


Application.ScreenUpdating = False


With Worksheets("tags")
LastTag = .Cells(Rows.Count, "B").End(xlUp).Row  'last tag row
Set TagArry = Worksheets("Tags").Range("A1:C" & LastTag) 'array to hold range of tag data columns A:C
End With


LastU = Cells(Rows.Count, "U").End(xlUp).Row  'Last row of text


For r = 1 To LastU  'Loop through rows of text


'Remove any punctuaton
With CreateObject("VBScript.RegExp")
.Pattern = "[^A-Z0-9 ]"
.IgnoreCase = True
.Global = True


'String to test
TestStr = " " & Trim(.Replace(Range("U" & r), "")) & " "
'Initialise bits and bobs
Str1 = ""  'Str1 = tag numbers found
Str2 = ""  'Str2 = Existing string of tag numbers in R
Erase AOne  'Array for split of Str1
Erase ATwo  'Array for split of Str2
Erase AThree    'Array for sorted combination destined for column R


'loop through all tags
For i = 1 To LastTag


'look for word tag from C in U text and add found numbers to Str1
If LCase(TestStr) Like "*" & " " & LCase(TagArry(i, 3)) & " " & "*" Then Str1 = Str1 & Trim(TagArry(i, 1)) & ","


Next i  'Next tag
End With


If Range("R" & r) = "" Then 'no previous numbers in R so R = Str1 only and dodge any sorting.
Range("R" & r) = Replace(Str1, ",", ", ", 1)
GoTo There:
End If


If Not Str1 = "" Then  'if Str1 has value then tags were matched and need sorting with values in R
'sort using three arrays...


Str2 = Trim(Range("R" & r))
'If Str2 = "" Then
'Range("R" & r) = Str2
'GoTo There:
'End If
' get rid of the trailing coma
Str1 = Left(Str1, Len(Str1) - 1)
Str2 = Left(Str2, Len(Str2) - 1)
'initialise counters
g = 0   'Counter for AThree
f = 0   'Counter for ATwo
e = 0   'Counter for AOne
'fill arrays
AOne = Split(Str1, ",")
ATwo = Split(Str2, ", ")
' loop
Do Until e > UBound(AOne) And f > UBound(ATwo)
'make comparisons to sort the numbers and avoid duplicates
If e > UBound(AOne) Then
AThree(g) = ATwo(f)
f = f + 1
GoTo Here:
End If


  If f > UBound(ATwo) Then
  AThree(g) = AOne(e)
       e = e + 1
       GoTo Here:
       End If
  
        If CInt(AOne(e)) > CInt(ATwo(f)) Then
        AThree(g) = ATwo(f)
        f = f + 1
        GoTo Here:
        End If
        If CInt(AOne(e)) = CInt(ATwo(f)) Then
        AThree(g) = ATwo(f)
        e = e + 1
        f = f + 1
        GoTo Here:
        End If
        If CInt(AOne(e)) < CInt(ATwo(f)) Then
       AThree(g) = AOne(e)
       e = e + 1
       End If
    
Here:
        g = g + 1


      Loop  'Continue sorting
   'Update the found numbers in R
   Range("R" & r) = Join(Split(Trim(Join(AThree)), " "), ", ") & ","
There:  'Jump to here to miss out on sorting
End If
Next r  ' Next text row
Application.ScreenUpdating = True
End Sub

Paste it into vbe sheet module and test on a backup copy.

It can be adapted, hopefully, to find phrases as per column B of tags once it is clear where you want the phrase result.

Hope that helps.

 

[Jennifre]

@Snakehips, Tony, thank you so much for this! If only I understood how to use this... I'm not sure I feel secure understanding it clearly enough to see where I need to make substitutions so it can work. I've never used vba code, nor a vba sheet module... Okay, off to hopefully try!

 

[Snakehips]

Hi, with your test sheet visible, right click the sheet tab and you should see an option to 'View Cod' Click that to get the vb editor open.
The top pane should be the pane for that sheet.
Paste the code into that pane.
Place the cursor somewhere in the code and then in the VBE menu bar, click Run >> Run Sub / User Form

GOOD LUCK

Any problems.. report back!