Need Formulas to Hide Partial Concatenate Data and Help Determining Two Other Formulas

Hello All!

I'm trying to automate creating certain keyword combinations I need, based off of the values I input into reference cells in columns A - E; the goal is to compile a list of keywords which I will then use to track my rankings in search engines. But there are three things I simply do not have the smarts to figure out, and I was hoping someone in here who is a bit more seasoned with excel formulas could provide me with some guidance. Below is what I am trying to accomplish:

1. I'm looking to only output 500 keywords, so some of the cells in columns A, B, C & E will not contain data (column D will always have a primary Geo-target listed). This results in some of the concatenate formulas I've created outputting partial data (i.e. if there is no data in cell A10, and cell D2 contains the word "Knoxville", then cell I10 will output the data, "Knoxville "). How can I setup conditioning formatting or a formula so that these auto-generated cells appear blank if one of the reference cells has no data within it?
2. The reason why I need the above to work is because I want to setup a formula that automatically counts the # of keyword combinations created by the data entered into any of the reference cells. With the partial combinations being listed, it skews my data. Which leads me to my next question: what is the best formula for counting the # of cells containing a full keyword combination from any of the cells listed in columns G - O (minus the data in the header cells; i.e. G1, H1, etc...)?
3. Lastly, is there a formula I could use that would then aggregate all of the full keyword combinations within the "Complete Keyword List" column (column P)?

Hopefully the above explanations and requests are not too confusing and/or difficult. I've attached an example document (click here: Ranking Reports - Automated Keyword Template.xlsx), which will hopefully increase my chances of getting some help on this one.

Thank you for your time and consideration, and please let me know if anything else is needed.

your attachment link is invalid

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic
ok now it works

Hey martindwilson,

Thanks for the prompt response; I knew I should have double-checked that link. I updated the original post with the correct document and have confirmed it works.

I have completed my document as much as possible, and it includes all of the necessary data as well as has the concatenate formulas set up. If more is needed, please let me know and I can provide that, but I'm feeling confident there is enough there to go off.

• Since posting the above, I found a way to count cells in a range of my choosing, but they still include cells that contain partially concatenated data (I need that number to exclude counting data from those partial cells) - and this formula is within cell C35, in case it's not obvious when looking at the sheet.
• To clarify, the following are reference cells (where I input data): A2:A11, B2:B31, C2:C31, D1, E1:E11. Columns G - O are where the concatenated data will appear, based off of the specifications marked in the headers. I've even outlined using a dotted red line the areas where data/formulas exists. I did this because I do not anticipate having to enter any more data in the reference cells than what is outlined, and thus only created formulas in each data column which would account for those max levels.
• When aggregating the concatenated data (again, minus the partial concatenated data, which I would prefer be hidden somehow) from columns G - O to column P, it can be listed in any order (chronologically or alphabetically; whichever is easiest/possible).

Lastly, I'm not sure if it matters, but I'm using Office Home and Business 2010.

Thanks again for the help bud! I look forward to hearing back from you.

FYI: I ended up using the below code to count the # of keywords that are aggregated to column P, but I'm realizing that although this works, it still requires me to go through the manual process of copy and pasting all data (minus partial concatenated data) just to determine the keyword count. What would be ideal is to get a live keyword count as I type data into the reference cells. Would there be a way to do the above before running a macro (which I'm assuming would be the solution to my #1 & #2 issues so I can get a live count?

Please Login or Register  to view this content.

ive changed a few columns to remove gaps ,i have no idea what the keyword list is supposed to be can you add to this what you eexpect

Hey Martin,

Thanks for looking into this for me! I truly appreciate it.

I love the formulas you drafted - they're brilliant! It did away with the restrictions my previous concatenate formula had, which is awesome. I've dragged them down though in some rows, and they are generating keyword combinations based off of the "# of Keywords" text in column B; I've highlighted these cells in columns H, J & L red so you can easily see them. Is there a way to revise the formulas for those particular cells so they exclude data from that column which matches "# of Keywords"?

Also, I've populated column P ("Complete Keyword List") to reflect what I am trying to achieve. Basically, I want all of the keyword combinations to automatically populate to column P (listing them in order from G - O) as soon as data is entered into any of the cells in columns A - E.

Example:

G1

G2

G3

H1

H2

I1

I2

I3

Keep in mind that the # of rows in columns G - O will vary based off of how many cells of data are populated in columns A - E, so I'm not quite sure how you would achieve having these being populated by formula instead of having to use a macro (the only reason I would prefer formula is so I can see a live keyword count in C42 as soon as data is populated into columns A - E). Which leads me to my final modification request; I added a new formula to C42 so it counts any data populated in column P, excluding the header ("Complete Keyword List"). This works fine, but only after I've copy and pasted the data to column P. Would it be possible to have this give me a live keyword count as soon as the keywords are automatically populated to column P?

Hopefully you can help me resolve the above modifications as well - I look forward to your response!

You can download the new attachment here: attempt 1 Automated Keyword Template_Modified.xlsx

ive just finished attempt 2 i didnt see your post see what you think it uses a few helper columns/rows to achieve the total list
you can always hide them

Hey Martin,

This is definitely great progress, but there are a couple of issues with the sheet I can't work out.

1. The complete keyword list should not include data from column F. The data in this column is only there to help build out the concatenated data in colums K, L & O. I tried to delete the formula in those adjacent cells, but it screwed up other formulas in the sheet. Can you remove the data in column F from the "Complete Keyword List Column" and also exclude those fields from the keyword count?
2. Also, if you refer to the last message I posed, I had extended your modified concatenate formulas down 100 rows, and the cells adjacent to the "# of Keywords" data cell pick that up as being a keyword combination. Is there a way to eclude that phrase from being picked up by the columns which concatenate data from the Secondary Services column? This would be helpful should I want to generate larger amounts of keyword combinations later down the road.

I look forward to seeing your response.

In pub! So you never know!
back now please attach your latest version highlighting those you dont want included

ok range adjusted
but i think its pointless adjusting formulas to ignore that total calculation
just move it out the way, i put it in a2:b2

Martin, you da MAN! Thank you SOOO much for all of your assistance/guidance with this - it truly has been a big help and is greatly appreciated, and will save my team some time with these monotonous tasks we've been handling. I am going to try to tweak the document to automatically take the keyword data entered into the main and secondary services columns and place those within another keyword column to be added to the complete keyword list and total count, but here is the final product as it stands now, in case you're interested in seeing it.

Ranking Reports - Automated Keyword Template_Example.xlsx

glad to have helped