Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default Macro to delete blank rows and move data/info

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.
--
Tx,
Samoan
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,441
Default Macro to delete blank rows and move data/info

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


"samoan" wrote in message
...
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.
--
Tx,
Samoan



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default Macro to delete blank rows and move data/info

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" wrote:

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


"samoan" wrote in message
...
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.
--
Tx,
Samoan




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,441
Default Macro to delete blank rows and move data/info

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" wrote in message
...
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" wrote:

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


"samoan" wrote in message
...
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.
--
Tx,
Samoan






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to delete blank rows in a data range Youlan Excel Discussion (Misc queries) 5 September 17th 08 08:51 AM
reformating data- how to delete alternate blank rows quickly datamanipulator Excel Discussion (Misc queries) 4 November 27th 07 05:41 PM
How do I delete blank rows (rows alternate data, blank, data, etc ncochrax Excel Discussion (Misc queries) 2 June 27th 07 04:40 AM
Macro to delete rows with same data Connie Martin Excel Worksheet Functions 12 November 22nd 05 02:18 PM
Delete blank rows Macro Richard Excel Discussion (Misc queries) 3 November 4th 05 09:02 AM


All times are GMT +1. The time now is 06:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"