Help Again

[Spike9458]

In a convoluted way, I used Excel to weed out some information to make up a
mailing list. The column I had was in the following format:

First Name Last Name
Street Address
City, State, Zip

So I inserted a couple of work columns ... used a simple =a2 in cell b1, and
=a3 in cell c1, and copied it down.

Which then gave me

First Name Last Name Street Address City, State, Zip


First Name Last Name Street Address City, State, Zip


See my dilemma? Now I got the name and address on one line, with 2 lines in
between. Is there a function I can use to get rid of them, or am I destined
to manual removal? Also, if anyone has any other ideas on an easier way to
do this ... I'm all ears!

Thanks,

--Jim

 

[Max]

One take on your post ..

If you had in Sheet1, in col A, A1 down
the data in the structure as below
(groups of 3 lines each with a blank line in-between groups)

First Name Last Name
Street Address
City, State, Zip

First Name1 Last Name1
Street1 Address1
City1, State1, Zip1

First Name2 Last Name2
Street2 Address2
City2, State2, Zip2
etc

In Sheet2
-------------
Leaving row1 for headers

Put in A2:
=OFFSET(Sheet1!$A$1,ROW(A1)*4-4+COLUMN(A1)-1,)

Copy A2 across to C2,
then fill down until zeros appear
signalling exhaustion of data

The data from Sheet1 will be re-layed nicely
into cols A to C, viz.:

First Name Last Name Street Address City, State, Zip
First Name1 Last Name1 Street1 Address1 City1, State1, Zip1
First Name2 Last Name2 Street2 Address2 City2, State2, Zip2
etc

And if you need to further split the items within the cols,
just kill the formulas with an "in-place":
copy > paste special > values > Ok
then use: Data > Text to columns
on each column in turn to split
(insert additional in-between columns beforehand as necessary)

 

[Spike9458]

Wow, that is awesome ... it does exactly what I need it to.

Thanks so much!

--Jim

 

[Spike9458]

When I copy it down, it pulls the street address from a2, and the
city,state,zip from a3 into the mix. If the offset was able to 'move' rather
than 'copy' the data from sheet1, then this may work ... any other thoughts?

--Jim

 

[Max]

Try this sequence of action ..

a. Kill the formulas in cols A to C in Sheet2 with an "in-place":
copy > paste special > values > Ok
(this'll freeze the results returned by the formulas)

b. Go to Sheet1, delete the original data in col A

c. Hop back to Sheet2, copy the "frozen" results in cols A to C
then hop over to Sheet1 again, and do
a paste special > values > ok into cols A to C there

 

[Spike9458]

Your formula;

=OFFSET(Sheet1!$A$1,ROW(A1)*4-4+COLUMN(A1)-1,)

I had to do some experimentation, and had to change the *4-4 to *3-5. Once I
figured out what the *4-4 was trying to do it was easy to figure out. Thanks
so much for making this task so much easier!!!

--Jim

 

[Max]

Pleased to hear that, Jim !
Thanks for posting back

 

[Gord Dibben]

Spike

To get rid of the blank rows.......

Select Column A and Edit>Go To>Special>Blanks>OK.

Edit>Delete>Entire Row.

Gord Dibben Excel MVP