Macro to delete blank rows and move data/info

S

samoan

I have an address list that I want to import into an address list mgmt
program. need to get it into excel first including name, address, city, ste
zip in separate colums. Sample below (made up names):

WAYNE TREDING
PO BOX 212
AVONDALE ESTATE GA 30002 0736



TONEY SANDRUFF
P O BOX 216755
MARIETTA GA 30006 0016




ROSELEA HOMESLEY
14 LAKESIDE LANE
PALM COAST FL 32164

Need to be:
WAYNE TREDING PO BOX 212 AVONDALE ESTATE GA 30002 0736
TONEY SANDRUFF P O BOX 216755 MARIETTA GA 30006 0016
ROSELEA HOMESLEY 14 LAKESIDE LANE PALM COAST FL 32164

I can deal with the extra spaces between first and last name, unless it's an
easy fix.
 
B

Bernie Deitrick

No macros needed.

Insert a new row 1 so that your list starts in A2. Then in B2, enter the formula

=IF(AND($A1="",$A2<>"",OFFSET($A2,COLUMN(A1)-1,0)<>""),TRIM(OFFSET($A2,COLUMN(A1)-1,0)),"")

and copy that cell three (or four, or five) columns to the right (if you can have more than three
rows of data per record).

Then copy all those formulas down to match your list.

Finally, copy all the formulas, paste values over them, delete column A, and then sort the entire
sheet to move the blank lines to the bottom.

HTH,
Bernie
MS Excel MVP
 
S

samoan

Tx Bernie,
I did the following:
Inserted a new row 1 so that your list starts in A2. Then in B2, entered
the formula
=IF(AND($A1="",$A2<>"",OFFSET($A2,COLUMN(A1)-1,0)<>""),TRIM(OFFSET($A2,COLUMN(A1)-1,0)),"")
Then copied that cell three columns to the right (C2.e2).
Then coped formulas in B2.F2 down to match the last record in the list.

While the first record showed up exactily as needed in cells B2.e2, no data
appeared for any of the other records below that first one. The formulas are
in the cells but no data appears.

Another suggestion?

Tx,
Samoan
____________________________________

Bernie Deitrick said:
No macros needed.

Insert a new row 1 so that your list starts in A2. Then in B2, enter the formula

=IF(AND($A1="",$A2<>"",OFFSET($A2,COLUMN(A1)-1,0)<>""),TRIM(OFFSET($A2,COLUMN(A1)-1,0)),"")

and copy that cell three (or four, or five) columns to the right (if you can have more than three
rows of data per record).

Then copy all those formulas down to match your list.

Finally, copy all the formulas, paste values over them, delete column A, and then sort the entire
sheet to move the blank lines to the bottom.

HTH,
Bernie
MS Excel MVP
Click to expand...
 
B

Bernie Deitrick

It appears that your 'blank cells' between are not blank.

Change the formula to

=IF(AND($A1=$A$6,$A2<>$A$6,OFFSET($A2,COLUMN(A1)-1,0)<>$A$6),TRIM(OFFSET($A2,COLUMN(A1)-1,0)),"")

where you replace the $A$6 with the cell address of one of the apparently blank cells in column A.

HTH,
Bernie
MS Excel MVP


samoan said:
Tx Bernie,
I did the following:
Inserted a new row 1 so that your list starts in A2. Then in B2, entered
the formula
=IF(AND($A1="",$A2<>"",OFFSET($A2,COLUMN(A1)-1,0)<>""),TRIM(OFFSET($A2,COLUMN(A1)-1,0)),"")
Then copied that cell three columns to the right (C2.e2).
Then coped formulas in B2.F2 down to match the last record in the list.

While the first record showed up exactily as needed in cells B2.e2, no data
appeared for any of the other records below that first one. The formulas are
in the cells but no data appears.

Another suggestion?

Tx,
Samoan
____________________________________
Click to expand...
 
Top