Transposing a column to several rows?

Hi there, I got this transposing problem where I have these info's (for like 700 firms) in a column, and I'd like each firm w/adress transposed to a row of its own...

Each firm consists of: name of firm, adress1, adress2 and some also adress3, like "Co'libri" here...

BOANDERSEN Kommunikation
Klosterstræde 23
1157 København K


Co'libri
Kongelig Hofleverandør
Klosterstræde 16
1157 København K


Roto Smeets Denmark A/S
Lille Strandstræde 20 C
1254 København K


So, what I'd like would be:

BOANDERSEN Kommunikation | Klosterstræde 23 | 1157 København K

Co'libri | Kongelig Hofleverandør | Klosterstræde 16 | 1157 København K

and so on... ("|" is just to show that name, adress1, adress2 and so on is showed in a cell each...)

If I just marked the whole column and made a transpose to a cell I'd just be getting one row with all the firms, and what I'd like is a row for each firm.


If anyone could help it would be very appreciated... :-)



ncikusa

This is one option (see enclosed)

Hope it helps
Ola Sandström

Hey Ola, thx a lot for looking into it, i tried fondling a bit with the book u gave me but i couldnt get it to do the same for the rest of the firms, I enclosed the firms and the result I'm looking for in book4.zip :-)


ncikusa

Here's one way, but it will involve several steps....

F3, copied down:

=IF((A3<>"")*(A2=""),ROW()-ROW($F$3)+1,"")

G3, copied down and across to Column K:

=IF(COUNTA($A3:$D3)=4,OFFSET($A$3,(LOOKUP(9.99999999999999E+307,$F$3:INDEX($F$3:$F3,MATCH(9.99999999999999E+307,$F$3:$F3)))-1)+(COLUMN()-COLUMN($G3)),0),"")

L3, copied down and across to Column N:

=IF($G3<>"",B3,"")

Now, do the following...

1) Select/highlight Columns G through N

2) Edit > Copy > Edit > Paste Special > Values > Ok

3) Select/highlight Columns G through K

4) Edit > Go To > Special > Constants > check 'Numbers' only (uncheck the others) > Ok

5) Edit > Clear > Contents

6) Select/highlight Column F

7) Edit > Clear > Contents

Hope this helps!

Many thx, to both of you!

And Domenic, I got it! :-)






I'm gonna be using this forum some more in the future :-)



ncikusa