Excel and keyword research

Consolidate keyword numbers from kw tools

[hairycoo]

I have a spreadsheet with loads of keywords on it (hundreds). Each keyword has 3 references e.g. how many searches Overture (+UK), Wordtracker, Espotting return. So it looks like this:

Ov Ov UK Esp Wtracker
-------------------------------
kw1 ¦ 20 ¦ n/a ¦ n/a ¦ n/a ¦
-------------------------------
kw1 ¦ n/a ¦n/a ¦ 10 ¦ n/a ¦
-------------------------------
kw1 ¦ n/a ¦ 12 ¦ n/a ¦ n/a ¦
-------------------------------
kw1 ¦ n/a ¦n/a ¦n/a ¦ 6 ¦
-------------------------------

How do I easily turn it into:

Ov Ov UK Esp Wtracker
-------------------------------
kw1 ¦ 20 ¦ 12 ¦ 10 ¦ 6 ¦
-------------------------------

Bear in mind I have hundreds of them so to automate this would be sheer heaven :)

[2oddSox]

There's probably much easier ways, but here's one way to do it:

If your spreadsheet is pretty uniform in its layout (like all the keywords have 4 rows each) then set up a macro with a hotkey and clean up one keyword section while the macro is running. e.g.

Firstly take out all the n/a's by doing a find and replace (replace with nothing).

After that, start recording the macro (choose relative cells (that's on the 'stop recording' toolbar))

Highlight all the cells and columns for the first keyword and go to Edit > Go To > Special > Blanks (this should highlight the blank cells)

Edit > Delete (move cells up) - this flattens the numbers into one line

Then highlight the 'extra' keyword instances and delete those (move cells up) - this should give you one instance of what you want the entire spreadsheet to look like.

Place the cursor on the next keyword and highlight the cell and then stop the recording of the macro.

You can then run the macro and cleaning up each keyword is as simple as hitting 'ctrl-a' (or whatever you set it to) for each one. You could also set up a conditional loop to go through the whole sheet at once if you're programmably inclined (which I'm not).

Make sure you make a backup of your sheet first :)