Excel macro - copy data from one line, append

[Closed]
Report
-
 OKBigKid -
Hello,

I have a spread sheet created from a PDF to XLS program. I have lines in which the address appears on two lines, with the extra text falling to a line below. This data is in Column B and where this 2nd address line exists, the data in Column A is blank.

LN 1 Column A (Name) Column B (Address)
LN 2 ARNOLD, STEPHEN 123 Anywhere St, Some_City,
LN 3 Some_State Some_Postal_Code
LN 4 ARNOLD, MARK 321 Anywhere St, Some_City,
LN 5 Some_State Some_Postal_Code
LN 6 ARNOLD, DAVID 345 Anywhere St, Some_City,
LN 7 Some_State Some_Postal_Code

I wish to copy the 2nd line data, append it to the first (with a space added between) and then delete the 2nd line, leaving the address in one field and complete:

LN 1 Column A (Name) Column B (Address)
LN 2 ARNOLD, STEPHEN 123 Anywhere St, Some_City, Some_State Some_Postal_Code
LN 3
LN 4 ARNOLD, MARK 321 Anywhere St, Some_City, Some_State Some_Postal_Code
LN 5
LN 6 ARNOLD, DAVID 345 Anywhere St, Some_City, Some_State Some_Postal_Code
LN 7

I may wish to then delete that entire empty line, but that should be optional after we confirm that the line is, in fact, entirely blank, and move the remaining data in the sheet up.

LN 1 Column A (Name) Column B (Address)
LN 2 ARNOLD, STEPHEN 123 Anywhere St, Some_City, Some_State Some_Postal_Code
LN 3 ARNOLD, MARK 321 Anywhere St, Some_City, Some_State Some_Postal_Code
LN 4 ARNOLD, DAVID 345 Anywhere St, Some_City, Some_State Some_Postal_Code

I can record a macro, but it only applies to the one cell where I started and I can not apply elsewhere. Actually a find next would be great too to automate the procedure.

Can someone please help me create a macro for Excel v2011 Mac ? there are 6 worksheets in this workbook with over 60,000 lines in each. It was created from a 5803 page PDF file converted to XLSX

Thanks in advance, Stephen A

2 replies

It is difficult to transfer your data to an excel sheet and then work on it.you must either just copy the data as it it is . there is no provision in this newsgroup to post the sheet. you can upload to some third party pages like speedyshare.com
and post the address of the upload and also explain again your problem with respect to the data. If this is possible please do it. you can use some random data for security reasons
Copied directly from the data
NAME ADDRESS
TYREE STEPHEN M 7232 QUEENS RD, KNOXVILLE, TN 37931-2530
U T HEALTH SYSTEM 9000 BOSSS PK DR, BLDG C SUITE 200,
KNOXVILLE, TN 37923
U T HOSPITAL 8229 GOODMAN LN, KNOXVILLE, TN 37920-9548
U T MED CTR P O BOX 9800, KNOXVILLE, TN 37940
UAB KNOXVILLE PO BOX 1234, KNOXVILLE, TN 37901
UHL DAVE N 8217 OAK HILL LN, KNOXVILLE, TN 38819-8718
ULTIMATE AQUARIUM 10911 JOHNSON WOODS DR, KNOXVILLE, TN
37922
UNDERSTAG DANNY R 799 DALEWOOD LN, KNOXVILLE, TN 37923
UNDERGROUND THE 284 W JOHNSON AVE, ACCOUNTS PAYABLE,
KNOXVILLE, TN 37542
UNDERWOOD STAN 2611 LINCOLN AVE, KNOXVILLE, TN 36934-5230
UNDERWOOD JANE D 6135 TOPICS PIKE, KNOXVILLE, TN
37926-8621
UNDERWOOD N 2611 LINCOLN AVE, KNOXVILLE, TN 36934-5230

Did that help?
When the ADDRESS fills two cells, for instance B3 and B4, A4 is empty.
I have written a formula to do this in a new Column (C), then simply copy the entire column to replace Column B with VALUES only, then I will still go back and erase the empty rows. I'll still need a macro for that function.

Here's the formula:
=IF(A3="",B2&", "&B3,IF(A2="","",B2))

Hope this helps explain. This was a 5803 page PDF that I converted to 7 works of about 900 pages (worksheets) each that I then consolidated to one workbook with 7 worksheets of 64,000 lines each. The data conversion was not perfect from PDF to XLS and I've got a lot of cleanup before the data becomes usable. This is the major issue to help get it more organized.
Stephen