Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to delete blank rows in a data range | Excel Discussion (Misc queries) | |||
reformating data- how to delete alternate blank rows quickly | Excel Discussion (Misc queries) | |||
How do I delete blank rows (rows alternate data, blank, data, etc | Excel Discussion (Misc queries) | |||
Macro to delete rows with same data | Excel Worksheet Functions | |||
Delete blank rows Macro | Excel Discussion (Misc queries) |