1

I've two collumns in excel and I want to see if there is a common word(s) betweeen my cells, for instance:

Cell A1: 76A08, 26A65, 22A26, 2A01, 12A05

Cell B1 I've : 26A65, 22A26

In cell C1 the result exepted is : 26A65, 224A26

I have over 7000 rows.

0

1 Answer 1

0

This VBa will do it

Rememeber to take a back up of your file first in case! (There is no undo option in VBa)

Since you have blank lines, but know how many rows, you can use this UNTESTED code.

Option Explicit
Sub doTheThing()

Dim row As Integer
row = 1

Dim totalRows as Integer
totalRows = 7000                      'Change the number to the total rows

for row = 1 to totalRows 

    Dim splitty() As String
    splitty = Split(Range("B" & row), ",")
    Dim i As Integer

    For i = 0 To UBound(splitty)
        Dim sp As String
        sp = splitty(i)
        If InStr(Range("A" & row).Value, Trim(sp)) Then
            Range("C" & row).Value = Range("C" & row) & sp & " "
        End If

    Next i

Next row

End Sub

How do I add VBA in MS Office?

Before

enter image description here

After

enter image description here

7
  • Oh you're genius! That was the title, by deleting it it works! One other issue, as sometime i met blank cell the macro stop :/
    – luc
    Jun 30, 2015 at 9:15
  • You didn't say there would be blank cells.. Does this mean you know exactly how many rows there are before you run the macro?
    – Dave
    Jun 30, 2015 at 9:29
  • Dave, as i created a new account i can't comment bellow you so i will delete this post. I've roughly 7000 raw
    – luc
    Jun 30, 2015 at 9:37
  • This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post. Jun 30, 2015 at 10:10
  • 1
    mods can't. He needs to register a new account (or use a existing registered account) and request an account merger via the contact us link
    – Journeyman Geek
    Jun 30, 2015 at 10:39

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .