Excel gurus please assist

[PilgrimToHyperion]

I've got a list that is supposed to show how long cetain cities' networks were unavailable.
The only format we can get the data in the following:

Column A
JHB
UNAVAILABLE 5MIN
UNAVAILABLE 7MIN
PTA
UNAVAILABLE 9MIN
DBN
UNAVAILABLE 5MIN
BLOEM
UNAVAILABLE 10MIN
UNAVAILABLE 1HOUR

In the example JHB was unavailable twice, PTA once, DBN once and then Bloem twice. Unfortunately the unavailability is indicated in the rows underneath the city name without the city name in the same row.

What I need is a formula (probably some and(if(or sort of thing) that will, in a new column, add the relevant city name next to the unavailability data so I can import it to SQL and do some reports.
Eg. I want the resultset to look something like this:

Column A Column B
JHB
UNAVAILABLE 5MIN JHB
UNAVAILABLE 7MIN JHB
PTA
UNAVAILABLE 9MIN PTA
DBN
UNAVAILABLE 5MIN DBN
BLOEM
UNAVAILABLE 10MIN BLOEM
UNAVAILABLE 1HOUR BLOEM

I'll appreciate any advice.

 

[ebendl]

Do you have a list somewhere with all the city names?

The first thing you need to do is pick up whether it was a new city in the next row. So you'll need the city names, or alternatively, you should work with the "Unavailable" text to pick up whether it is a new city.

 

[PilgrimToHyperion]

I've tried that by using =if(A1<>"unavailable",A1), but it doesn't work when there is more than 1 unavailable consecutively.

 

[TerrorBite]

Based on the fact that all your data is in column A, insert a new formula in column C and in column B like shown below. make sure u first enter formula in Column C

column C = "=IF(LEFT(A2,3)="UNA",C1,A2)"
column B = "=IF(LEFT(A2,3)="UNA",A2&" " &C2,A2)"

assuming that your data starts at cell "A2" and that "A2" contains a city e.g JHB, the formula should work... just copy and paste formula to the rest of the cells. Then copy and paste special as "value" to use the data output from the formula.....

You may have to edit the formula slightly depending on your output format....but this should get you on your way.

 

[PilgrimToHyperion]

@TerrorBite - absolutely brilliant. Thank you sir!

 

[AirWolf]

A solution for just one column would be (though all places names gets converted into 3 letters):

=IF(LEFT(A2,3)="UNA",A2&" "&RIGHT(B1,3),LEFT(A2,3))

[Data starts in A2 and formula above starts in B2].