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

M

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
 
J

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.
 
J

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.
 
R

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)
 
J

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.
 
R

RagDyer

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

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top