Keyword Building In Excel

Combining 3 columns as keywords

[werty]

I have been playing with this for a while, but figured there would be an excel expert somewhere on the board.

here is my problem

Column 1
free
cheap
discount

Column 2
yellow
purple
red
orange
silver
black
pink

Column 3
widgets
wadgets
widgits
weedgits

My goal is to make column 4 something like this:
free yellow widgets
free purple widgets
free red widgets
free orange widgets
free silver widgets
free black widgets
free yellow wadgets
free purple wadgets
free red wadgets
free orange wadgets
free silver wadgets
free black wadgets
...and so on but then swap free for cheap and then for discount

So the end result would be one nice cut-and-pastable list of keyword combinations that would not change if one of the column lengths changed.

I have done something but if I add a word to any of the pre-existing columns it throws column 4 off, way off.

Is there any easy to use formula that I am completely missing out on? Thanks in advance.

[gopi]

Save the excel as csv and a simple perl script can process it ...just a simple 3 way nested 'for loop' can do what you want!

[Mardi_Gras]

You need to use the concatenate function:

Assuming the first cell in column 4 for data would be D2, you would insert this into D2:

=CONCATENATE(A2," ",B2," ", C2)

That tells excel to combine the text it finds in A2, B2, and C2 into D2 with spaces in between the strings.

[Symbios]

copy the 3 columns, paste them in to notepad, do a search and replace substituting the 'tabs' for spaces.

[Mardi_Gras]

>and so on but then swap free for cheap and then for discount

I missed that key phrase. Just insert the same function in another column, but alternate the the referenced cells, then repeat in a third column. Once you have your functions set into three columns, just drag them down through the length of your keywords.

You will have to "hard code" the reference to your three key cells - that is, the reference to A2 would be $A$2. No matter where you dragged your formula, it would always reference that particular cell.

[Mardi_Gras]

Assuming Row 1 is your header row, and free, cheap, and discount are cells 2-4 in colum A, and your two keywords come down through columns b and c, you would merge your results into colums D, E, and F.

Column D: =CONCATENATE($A$2," ",B2," ", C2)
column E: =CONCATENATE($A$3," ",B2," ", C2)
Column F: =CONCATENATE($A$4," ",B2," ", C2)

That's all there is to it...

[werty]

Mardi Gras, I have tried your example, but it does not output as many as it should:

Column 1: 1 2 3 4 5 6
Column 2: a b c d e f
Column 3: X Y Z XX YY ZZ

the output only gives me these:
1 A X
1 B Y
1 C Z
1 D XX
1 E YY
1 F ZZ
2 A X
2 B Y
2 C Z
2 D XX
2 E YY
2 F ZZ
3 A X
3 B Y
3 C Z
3 D XX
3 E YY
3 F ZZ

It should do something like
1 a X
1 a Y
1 a Z
1 a XX
1 a YY
1 a ZZ
1 b X
1 b Y
1 b Z
1 b XX
1 b YY
1 b ZZ ... until it hits "1 f ZZ" and then start with
2 a X
2 a Y
2 a Z
2 a XX
2 a YY
2 a ZZ
2 b X
2 b Y
2 b Z
2 b XX
2 b YY
I think it should yield 216 combinations

I am going to look into combinations and permutations and see if excel supports them, because I think thats what it needs.

Thanks for your help so far!

[Mardi_Gras]

>the output only gives me these:

No problem - just create a couple of new columns and change the order of your referenced cells. It will then create the new combinations.

[anallawalla]

Thanks guys. Tip paid for my subscription to WW a few times over.

I had to rework several hundred PowerPosted keywords to a new tracking string and was dreading doing it manually. I could do a lot with search and replace but concatenate was the icing on the cake.