Help, please - How to rearrange 1 column of data into 4 columns ?

[Mark246]

I've got One column of data that has...
FirstName
LastName
City
ZipCode
John
Smith
Tulsa
89456
Joe
Adams
Denver
65454
etc...

Exactly 4 rows for each person.
How can I move the data for a person onto one row, 4 columns? Like...
John Smith Tulsa 89456
Joe Adams Denver 65454

Thanks, in advance, people.

Mark246

 

[JMB]

if your data is in A1:A8, try this in cell B1

=INDEX($A$1:$A$8,(ROWS(B$1:B1)-1)*4+COLUMNS($B1:B1))

then copy the formula across and down as far as necessary. Adjust range
references as needed to reflect where your data actually is. Then copy the
cells in columns B:E and Edit/Paste Special Values to hardcode the data (if
you wish to delete the original data in column A afterwards).

Be sure to back up your file in case of mishaps.

 

[JLatham]

JMB's is an excellent solution, just requires one formula with a mod to it
based on the first column you place it into.

If you want an alternative set of formulas, then these could be used. As
JMB did, I'll assume your starting in column B and with 1st name in A1:
First column to pick up 1st name (B1), formula:
=OFFSET(Sheet1!$A$1,(ROW()-1)*4,0)
in next column, to pick up last name:
=OFFSET(Sheet1!$A$1,(ROW()-1)*4+1,0)
in next column, to pick up city:
=OFFSET(Sheet1!$A$1,(ROW()-1)*4+2,0)
and in 4th/last column to pick up zip code:
=OFFSET(Sheet1!$A$1,(ROW()-1)*4+3,0)

Then fill those formulas down the sheet as far as needed, and follow JMB's
instructions on Edit | Copy followed by Edit | Paste Special w/Values option
selected if desired.

I've included the sheet name of the source information in my formulas; if
these are going on the same sheet as the source data, then the sheet name
part ( Sheet1! ) is not required.

 

[RagDyer]

Enter this anywhere, then copy across 4 columns, and then down as needed:

=INDEX($A:$A,4*ROWS($1:1)-3+COLUMNS($A:A)-1)

 

[JLatham]

Here is another, first time I saw it, it was provided by Ron Coderre:

In the first of 4 contiguous columns (for this example, assume column G) put
this formula:
=INDEX($A:$A,(ROWS($1:1)-1)*4+COLUMNS($G:G))
Then fill it right over to column J
Then fill the 4 formulas on down the sheet as far as you need to go.

Same comments about Edit | Copy | Paste Special, etc.

 

[RagDyer]

Bothers me that I didn't combine the 2 minuses!

=INDEX($A:$A,4*ROWS($1:1)+COLUMNS($A:A)-4)

 

[Mark246]

Magnificent !

I don't know how those hieroglyphs actually do the task, But It
WORKS !

THANKS Very Much, people.

I LOVE this forum.

Mark246

 

[RagDyer]

And we appreciate your feed-back.