Page 1 of 2 12 LastLast
Results 1 to 10 of 19

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

  1. #1
    Board Regular
    Join Date
    Jan 2011
    Location
    Chicago + St Paul
    Posts
    147
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

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

    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=#FFFFFF ]2[/COLOR]
    sat 2
    [COLOR=#FFFFFF ]3[/COLOR]
    on 3
    [COLOR=#FFFFFF ]4[/COLOR]
    mouse 4
    [COLOR=#FFFFFF ]5[/COLOR]
    the 5


    [/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=#FFFFFF ]R[/COLOR] [COLOR=#FFFFFF ]U[/COLOR]
    [COLOR=#FFFFFF ]1[/COLOR]
    1, 2, 4, 5, 21, more... the cat sat on the mouse ...more
    [COLOR=#FFFFFF ]2[/COLOR]
    1, 4, 5, 21, 28, more... cat ate the mouse ...more
    [COLOR=#FFFFFF ]3[/COLOR]
    1, 2, 4, 5, 21, 22, more... the mouse sat under the cat ...more



    ​Thank you!!

  2. #2
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,671
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

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

    @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?
    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

  3. #3
    Board Regular sergioMabres's Avatar
    Join Date
    Feb 2013
    Location
    Córdoba, Argentina
    Posts
    946
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

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


    Cheers
    Sergio
    Last edited by sergioMabres; Jul 7th, 2019 at 07:13 PM.

  4. #4
    Board Regular sergioMabres's Avatar
    Join Date
    Feb 2013
    Location
    Córdoba, Argentina
    Posts
    946
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

    (Same test file)
    Cheers
    Sergio
    Last edited by sergioMabres; Jul 7th, 2019 at 07:38 PM.

  5. #5
    Board Regular sergioMabres's Avatar
    Join Date
    Feb 2013
    Location
    Córdoba, Argentina
    Posts
    946
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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 by sergioMabres; Jul 7th, 2019 at 08:15 PM.

  6. #6
    Board Regular
    Join Date
    Jan 2011
    Location
    Chicago + St Paul
    Posts
    147
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

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

    @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

  7. #7
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,671
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

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

    @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 by Snakehips; Jul 8th, 2019 at 08:46 AM.
    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

  8. #8
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,671
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

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

    Excel 2010
    A B C
    1 1 a phrase CAT
    2 2 another one sat
    3 3 yet another phrase on
    4 4 number four mouse
    5 5 that is all now the
    6
    Tags



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



    To this...
    Excel 2010
    R S T U
    1 1, 4, 5, 21, 28, more… cat ate the mouse …more
    2 1, 2, 3, 4, 5, 21, more… the cat sat on the mouse … more
    3 1, 4, 5, 21, 28, more… cat ate the mouse …more
    4 1, 2, 4, 5, 21, 22, more… the mouse sat under the cat …more
    5 more… looking for a phrase this time …more
    6 43, more.. Maybe yet another one ...more
    7 more.. Another what? Yet Another Phrase! …More
    8 1, 4, 12, more… can their dog catch mouse? …more
    9
    10 1, 4, 5, "stuff the cat!" …………. Said Mouse
    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 by Snakehips; Jul 9th, 2019 at 12:19 PM.
    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

  9. #9
    Board Regular
    Join Date
    Jan 2011
    Location
    Chicago + St Paul
    Posts
    147
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

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

    @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!

  10. #10
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,671
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

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

    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 by Snakehips; Jul 11th, 2019 at 05:01 PM.
    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •