Topic: Excel Coding (Page 1 of 1)

In Excel, I have a list like this:

London
• The capital of England. The Prime Minister Lives there.
• It rains a lot more than most towns.
  
  
  
  Berlin
• The capital of Germany.
• It used to be divided into 2 parts. There was a wall through the middle.
• Kennedy visited it once.

I want to turn it into a list like this:

The capital of England.  The Prime Minister Lives there. It rains a lot more than most towns.                                   | London
The capital of Germany. It used to be divided into 2 parts.  There was a wall through the middle. Kennedy visited it once.      | Germany

Where the city names are in the second column and the descriptions are concatenated into the cell to the left of them.

I've been trying to figure out how to do this in Excel code, but I've made nothing but messes. Notably, I don't know how to specify where one block of the list ends and the next begins.

This is something I need to use on my work machine, so I don't have many coding/compilation resources available to me, only VBA. The former list is generated by someone else, and with the latter list I can begin to build a sort of tracker. I figured out how to make Excel run a macro with some code, and I have an idea of the algorithm that I want to run:

10 IF the cell before this one was blank THEN move this cell to the cell to its right
20 DELETE the cell, moving every cell below it up by one
30 CONCATENATE every cell until the next empty cell into this cell
40 FIND the next cell with a blank cell above it
50 GOTO 10

I'm lost on how to code it. I've reading stuff like this: My Excel Pages - Strings and Manipulations, but anything deeper than replacing one cell with another has me stumped. Does anyone have any good references or snippets I can look at? Has anyone seen anything similar to this done?

(Edited by Petskull on 06-28-2009 01:19)

I ended up doing a *ton* of VBA coding for Excel a while back, building a handful of Excel Applicaitons for some work projects. A lot of what I had to do involved parsing and restructuring lists of data.
I found this site to be a goldmine of useful information: http://www.ozgrid.com/
A good VBA and/or Excel reference book can go a long way as well.

If you have an example file you can post, it may be helpful in understanding the specific of what you need to do.

Your example is unclear.
Either the descriptions are concatenated to the left of the city name or they are in a list...it's one or the other.
If you just want to concatenate descriptions this can be done very simply with formulas. I don't see any need for VBA unless your content is dynamic.
For example, if the first description is in cell a2 and the second description is in cell a3 and you leave some space in between, it would be as simple as entering:

=a2&" "&a3

you could add an if formula to check for blank rows.

Nature & Travel Photography
Main Entrance

(Edited by SleepingWolf on 06-29-2009 00:46)