reorganizing imported spread sheet

[dianna]

I have no idea if this is possible, buy i have a spreadsheet of data with
names, addresses and phone numbers listed all in one row, Name at top,
address underneath that, city, state zip under that, and phone # under that,
with a total of eight lines underneath name.

In addition, in the columns, there is information in b1.

How do I move A2 into B1, A3 into c1, a4 into d1, a5 into e1, a6 into f1, a7
into g1, a8 into h1, then A9 becomes a2 or a3, and the original info in b1
just moves out to the side.

 

[Rick Rothstein]

You should be able to do what you want with a VB macro, but you need to
clarify something before we can propose code for you. Is every group of data
**always** 8 rows each (never 7 or less rows nor 9 or more rows.. always 8
rows)? Also, when you say "and the original info in b1 just moves out to the
side", do you mean it will go to cell I1 (for the first group of data)? Or
did you mean something else by "to the side"?

 

[dianna]

I did look through some other posts, and to a similar question, someone
posted this formula:

=INDIRECT("A" & ROW()*3-4+COLUMN())

Now, just so you know, I am excel illiterate. I did attempt to simply type
this function in for cell A4, and all I got was cell A9 moved up to A4. I
have never really dealt with formulas. If someone could tell me exactly how
this is supposed to be used, I would appreciate it. Are you actually supposed
to enter something in the parentheses by row and column?

 

[Gord Dibben]

Sounds like the names, addresses etc. are in one column, not one row.

And I assume repeated many times down column A in sets of 9

Select B1 and insert 9 columns to the right of A, moving B data to K

Enter this formula in B1

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

Drag/copy across to J1 then drag B1:J1 down until you get zeros.

When happy, select the formulas range and copy>paste special(in
place)>values>ok>esc.

Delete column A


Gord Dibben MS Excel MVP

 

[SCGRL]

it works great, but make sure you have a blank row beteween each set of 9
records (rows).
Thank you! I have learned something very useful today..now I can go home!

 

[Gord Dibben]

Why do you think you would you need the blank row between sets?

Gord