Any Microsoft Office 2000 gurus out there? In Excel, is it possible to....

[spanky]

this is a little tricky to explain, but here goes...
i have a excel spreadsheet that has a list of addresses. right now, the format looks like this:

john doe (a1)
123 avenue a (a2)
hometown, nj 01234 (a3)

jane doe (a5)
123 avenue b (a6)
hometown, nj 01234 (a7)

and so on. each field is in the same column, but different row. basically, the addresses are listed vertically. is there an easy way to have excel modify the document so that each address will be listed horizontally? kinda like this....

john doe (a1) 123 avenue a (b1) hometown, nj 01234 (c1)

jane doe (a2) 123 avenue b (b2) hometown, nj 01234 (c2)

and so on. someone mentioned that there is a formula to do this, but i have no idea what they are talking about 😕 what do you guys think? any tips/suggestions would be greatly appreciated. thanx!

 

[spanky]

^

 

[spanky]

Originally posted by: spanky
^

 

[MrChad]

Highlight your data, go to Edit / Copy.

Go to a new sheet.

Edit / Paste Special / Check "Transpose".

 

[spanky]

Originally posted by: MrChad
Highlight your data, go to Edit / Copy.

Go to a new sheet.

Edit / Paste Special / Check "Transpose".

thanx for the tip :beer:
however... i know i may be asking for too much, but when i did that... that pasted everything into the same row. would there be a way to get excel to convert from this...

john doe (a1)
123 avenue a (a2)
hometown, nj 01234 (a3)

jane doe (a5)
123 avenue b (a6)
hometown, nj 01234 (a7)


to...


john doe (a1) 123 avenue a (b1) hometown, nj 01234 (c1)

jane doe (a2) 123 avenue b (b2) hometown, nj 01234 (c2)

(notice the cell addressing)



:light:

 

[MrChad]

Oops, just noticed your cell addressing. Hrm, I'll have to think about that ...

 

[NumberCruncher]

try the row and offset functions....if the data start in [A1], then try, for example:

[D1]: =(ROW(A1)-1)*3
[E1]: =OFFSET($A$1,$D1,0)
[F1]: =OFFSET($A$1,$D1+1,0)
[G1]: =OFFSET($A$1,$D1+2,0)

then copy these down for as many names as you have. This approach will only work if every entry is 3 lines long.

There are other methods, but they mostly involve having 2 empty rows after each fixed row and then you would need to copy - paste special - values and then filter out the blank rows..

Number Cruncher

 

[zetter]

Originally posted by: MrChad
Highlight your data, go to Edit / Copy.

Go to a new sheet.

Edit / Paste Special / Check "Transpose".

Sort of correct answer.

You can use the =Transpose(array) function to do it.

eg, use =TRANSPOSE(A1:A3) in cells F1, G1, H1 (as an array) and it'll convert the

john doe (a1)
123 avenue a (a2)
hometown, nj 01234 (a3)

to

john doe (f1) 123 avenue a (g1) hometown, nj 01234 (h1)

then do the same in F5, G5, H5 with A5:A7 to get the next result.

 

[jimmyl930]

Another question:

How do I shade every other row?

Thanks.

 

[Mday]

Originally posted by: jimmyl930
Another question:

How do I shade every other row?

Thanks.

click the first row number, then control+click the other rows you want. or, you could use the sort feature and a sacrificial column filled with 0,1 repeating.

 

[Mday]

Originally posted by: spanky

Originally posted by: MrChad
Highlight your data, go to Edit / Copy.

Go to a new sheet.

Edit / Paste Special / Check "Transpose".

thanx for the tip :beer:
however... i know i may be asking for too much, but when i did that... that pasted everything into the same row. would there be a way to get excel to convert from this...

john doe (a1)
123 avenue a (a2)
hometown, nj 01234 (a3)

jane doe (a5)
123 avenue b (a6)
hometown, nj 01234 (a7)


to...


john doe (a1) 123 avenue a (b1) hometown, nj 01234 (c1)

jane doe (a2) 123 avenue b (b2) hometown, nj 01234 (c2)

(notice the cell addressing)



:light:

do the transpose paste, and sort to gather the empty space, delete the rows. or create a macro to delete the rows.