seo Question posted in
Some basic information to get you started with this can be found here.

Excel Mix & Match Concatenate Columns – SEO

I’m having a problem mass concatenating two columns of data, and not sure how to go about it. I am not that proficient with excel, so anyone who could lend any actionable insights that would be great.

Basically I have two columns of data, one is a column with types of services, the other is a column with geo-modifiers. Example:

_____A______  _________________B_________________
Geo-modifier  Service
New York      search engine optimization
Los Angeles   seo services
Chicago       seo optimization
Houston       seo marketing
Philadelphia  seo service
Phoenix       best seo
San Antonio   search optimization
San Diego     search engine optimization services

So what I am trying to do with concatenate is append the geo modifier to all the service words. For example:

_________________C_________________
new york search engine optimization
new york seo services
new york seo optimization
etc

then when it reaches to the bottom of the services list it would go to the next query and concatenate to the bottom of the list again. Example LA search engine optimization, LA seo services, LA seo optimization!

Given the above scenerio how would I do this, in the easiest way possible?

Tags:

2

Answers


  1. 0

    In cell C2 and copied down, adjusted ranges to suit:

    =INDEX($A$2:$A$9,INT((ROW(A2)-2)/ROWS($B$2:$B$9))+1)&" "&INDEX($B$2:$B$9,MOD(ROW(B2)-2,ROWS($B$2:$B$9))+1)
    Login or Signup to reply.
  2. 0

    Here is a VBA solution to enter into a new Code module:

    Public Sub MixMatch()
    Dim i&, j&, c&
    Dim vIn1, vIn2, vOut
    vIn1 = [A2:INDEX(A:A,COUNTA(A:A))]
    vIn2 = [B2:INDEX(B:B,COUNTA(B:B))]
    ReDim vOut(1 To UBound(vIn1) * UBound(vIn2), 1 To 1)
    For i = 1 To UBound(vIn1)
        For j = 1 To UBound(vIn2)
            c = c + 1
            vOut(c, 1) = vIn1(i, 1) & " " & vIn2(j, 1)
        Next
    Next
    [c2].Resize(UBound(vOut)) = vOut
End Sub
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search