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

J

Jennifre

Board Regular
Joined
Jan 11, 2011
Messages
160
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!! :-)
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
@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?
 
Upvote 0
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
NumbersText
1, 0, 3, 4, 5more... the cat sat on the mouse ...more
0, 0, 4, 5, 0more... cat ate the mouse ...more
0, 4, 5, 0, 0more... 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
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0
@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
 
Upvote 0
@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?
 
Last edited:
Upvote 0
Excel 2010
ABC
a phraseCAT
another onesat
yet another phraseon
number fourmouse
that is all nowthe

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

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.
 
Last edited:
Upvote 0
@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! :-D
 
Upvote 0
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!
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Separate data within cell to different columns.
Replies
7
Views
229
Peter_SSs
Peter_SSs
F
  • Solved
Formula to show in 4 columns the letters from one column with condition
Replies
6
Views
285
Fractalis
F
P
  • Question Question
ListBox Click to textbox & delete row
Replies
0
Views
607
pure vito
P
R
  • Solved
How to independently sort multiple columns of this SEQUENCE output without effecting the positions of the columns and of the blanks?
2
Replies
15
Views
519
Rnkhch
R
S
  • Question Question
Worksheet Change Event
Replies
2
Views
692
sspatriots
S

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

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