Prev: Macro/VB coding question
Next: data conversion
From: TR53 on 4 Jun 2010 15:34
I have an Excel sheet that has 8000+ rows that include contact information.
I'd like to move them from all of those rows into 6 columns. Currently it
lookzs like:

name
title
company
address
city/state/zip
phone
....
....
....

And I'd like to create a formula to move it to 6 columns that look like this:

name title company address city/state/zip phone

I'm still an Excel novice but would appreciate any suggestions you might
have. Thanks!


From: Mike on 4 Jun 2010 15:46
You will need VBA code to get this done. If you would like I could whip up a
code for you if your willing to send me a copy of your workbook?

"TR53" wrote:

> I have an Excel sheet that has 8000+ rows that include contact information.
> I'd like to move them from all of those rows into 6 columns. Currently it
> lookzs like:
>
> name
> title
> company
> address
> city/state/zip
> phone
> ...
> ...
> ...
>
> And I'd like to create a formula to move it to 6 columns that look like this:
>
> name title company address city/state/zip phone
>
> I'm still an Excel novice but would appreciate any suggestions you might
> have. Thanks!
>
>
From: Steve on 4 Jun 2010 15:55
If your information is in column A starting in A1 then in cell B1 enter
=INDIRECT("A"&((ROW()-1)*6)+1)
copy this down column B to give all "names". In cell C1 enter
=INDIRECT("A"&((ROW()-1)*6)+2)
copy this down column C for all the "titles"
etc. etc. just increase the last nuber by 1 as you go across your 6 columns

Regards
Steve

"TR53" wrote:

> I have an Excel sheet that has 8000+ rows that include contact information.
> I'd like to move them from all of those rows into 6 columns. Currently it
> lookzs like:
>
> name
> title
> company
> address
> city/state/zip
> phone
> ...
> ...
> ...
>
> And I'd like to create a formula to move it to 6 columns that look like this:
>
> name title company address city/state/zip phone
>
> I'm still an Excel novice but would appreciate any suggestions you might
> have. Thanks!
>
>
From: Gord Dibben on 4 Jun 2010 20:10
Assuming column A with consistent sets of data........six cells to a set.

In B1 enter =INDEX($A:$A,(ROWS($1:1)-1)*6+COLUMNS($A:B)-1)

Copy across to G1

Select B1:G1 and copy down until you get zeros.


Gord Dibben MS Excel MVP

On Fri, 4 Jun 2010 12:34:38 -0700, TR53 <TR53(a)discussions.microsoft.com>
wrote:

>I have an Excel sheet that has 8000+ rows that include contact information.
>I'd like to move them from all of those rows into 6 columns. Currently it
>lookzs like:
>
>name
>title
>company
>address
>city/state/zip
>phone
>...
>...
>...
>
>And I'd like to create a formula to move it to 6 columns that look like this:
>
>name title company address city/state/zip phone
>
>I'm still an Excel novice but would appreciate any suggestions you might
>have. Thanks!
>

 | 
Pages: 1
Prev: Macro/VB coding question
Next: data conversion