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

Jennifre

Board Regular
Joined
Jan 11, 2011
Messages
147
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)
A
B
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1​

<tbody>
cat1
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
sat2
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
on3
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
mouse4
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
the5

</tbody>


[/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?)
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]R[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]U[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
1, 2, 4, 5, 21, more... the cat sat on the mouse ...more
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
1, 4, 5, 21, 28, more... cat ate the mouse ...more
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
1, 2, 4, 5, 21, 22, more... the mouse sat under the cat ...more

<tbody>
</tbody>



​Thank you!! :)
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
4,916
Office Version
2013
Platform
Windows
@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

Well-known Member
Joined
Feb 24, 2013
Messages
946
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:

sergioMabres

Well-known Member
Joined
Feb 24, 2013
Messages
946
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:

sergioMabres

Well-known Member
Joined
Feb 24, 2013
Messages
946
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:

Jennifre

Board Regular
Joined
Jan 11, 2011
Messages
147
@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

Well-known Member
Joined
May 17, 2009
Messages
4,916
Office Version
2013
Platform
Windows
@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:

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
4,916
Office Version
2013
Platform
Windows
Excel 2010
ABC
11a phraseCAT
22another onesat
33yet another phraseon
44number fourmouse
55that is all nowthe
6

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Tags



This...
Excel 2010
RSTU
11, 21, 28,more… cat ate the mouse …more
24, 21,more… the cat sat on the mouse … more
31, 21, 28,more… cat ate the mouse …more
44, 21, 22,more… the mouse sat under the cat …more
5more… looking for a phrase this time …more
643,more.. Maybe yet another one ...more
7more.. Another what? Yet Another Phrase! …More
81, 12,more… can their dog catch mouse? …more
9
10"stuff the cat!" …………. Said Mouse

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Join



To this...
Excel 2010
RSTU
11, 4, 5, 21, 28,more… cat ate the mouse …more
21, 2, 3, 4, 5, 21,more… the cat sat on the mouse … more
31, 4, 5, 21, 28,more… cat ate the mouse …more
41, 2, 4, 5, 21, 22,more… the mouse sat under the cat …more
5more… looking for a phrase this time …more
643,more.. Maybe yet another one ...more
7more.. Another what? Yet Another Phrase! …More
81, 4, 12,more… can their dog catch mouse? …more
9
101, 4, 5,"stuff the cat!" …………. Said Mouse

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</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:

Jennifre

Board Regular
Joined
Jan 11, 2011
Messages
147
@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! :biggrin:
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
4,916
Office Version
2013
Platform
Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,090,549
Messages
5,415,214
Members
403,573
Latest member
ballardgirl

This Week's Hot Topics

Top