rearranging copied addresses into columns

jknichlsn · Mar 5th, 2002, 09:36 AM

First posting; glad I found this.

If I copy a long list of addresses (say, from the Web) arranged in address blocks, like this:

Jack Nicholson
Big Bank Corp.
123 Fourth Street
Second Floor
Chicago, IL 60603

and want to end up with ALL these addresses in columns (all names in Col A, Company in Col B., etc.), how can I do this? To complicate things, some addresses have more rows in the address block than others; I can work that out by sorting the resulting columns and moving the data, unless someone has something very clever in mind.

Alternatively, does anyone know of a commercial product, faily cheap, that will convert addresses into ACT! contact management software format?

thanks!

AJ · Mar 5th, 2002, 09:48 AM

I had to do the very same thing myself with an output from ACT about a week ago.

The following worked.
Before I started all the addresses were in column A and started in cell A1.
There was also a blank line in between each address and, as in your case, the addresses took up variable numbers of lines.

--
Dim myRow As Integer
Dim myIncrement As Integer

myRow = 2
myIncrement = 1
Range("A1").Select

While ActiveCell.Value <> ""

ActiveCell.Offset(1, 0).Cut
ActiveCell.Offset(0, myIncrement).Select
ActiveSheet.Paste
Rows(myRow & ":" & myRow).Select
Selection.Delete Shift:=xlUp
myIncrement = myIncrement + 1
ActiveCell.Offset(-1, 0).Select
If ActiveCell.Offset(1, 0).Value = "" And ActiveCell.Offset(2, 0).Value <> "" Then
Rows(myRow & ":" & myRow).Select
Selection.Delete Shift:=xlUp
myIncrement = 1
myRow = myRow + 1

End If

Wend
--
As with most of my code, it's probably a bit cumbersome, but seems to do the trick.

Rgds
AJ

jknichlsn · Mar 6th, 2002, 12:36 PM

wow.

I am quite new to macros, so I don't have any idea how to proceed, but I'm sure I can find someone around here who can take your code and make it do its thing on my lists.

Thank you, VERY much, for taking the time. I really appreciate it.

jknichlsn · Apr 2nd, 2002, 03:20 PM

Well, I thought it would be easy to find out how to convert your text into something that would actually do something, but no.

Would you be so kind as to reply with something very fundamental, like:

Start with the spreadsheet of your listed addresses opened. Click on "Tools/Macros..." and click on "New Macro" ....'

Your response was like that of a native speaker of a foreign language who replied with a flood to the only phrase I've learned in his language. Thanks very much!!

AJ · Apr 10th, 2002, 07:10 PM

Hello again!

Sorry I've only just noticed your final post to this thread. I don't usually go that far back. (Was just having a final browse before I go for the day).

Anyway, the simple guide to get this to work is as follows:

1. Make a copy of your file incase this totally screws it up!

2. Because I've just given you code above, you need to go directly to the Visual Basic Editor (under Tools>Macros).

3. When you get there, in the project explorer window (usually the box filling the top half of the left side of the screen), right click on your filename and Insert Module

4. At the top of the module type
Sub YourMacroName ()
You'll see this adds End Sub a couple of lines down. Then paste the macro above in between the Sub line and the End Sub line.

5. Back in Excel see if it works...
Tools>Macro>Macros pick YourMacroName and click run.

If it works we're laughing! If it brings up an error message, reply telling me what it is. If it runs all the way through and does not work as you intended, go back to the Visual Basic Editor. Shrink the window a bit so you can see what's going on in Excel at the same time. Put your cursor anywhere in the macro and hit F8 repeatedly to step through it line by line. This will give you a clue as to where it's falling down. Again, reply and me or someone else will fix it!

Rgds
AJ

abe · Apr 11th, 2002, 08:57 AM

As to ACT.
ACT will import a text delimited file.
The key to success with the import is to ensure that your column headers are identical to the field names in ACT.
You should split your name column into first name and last name columns. Act will merge these two columns into the contact field automatically.

jknichlsn · Apr 11th, 2002, 10:20 AM

AJ: you are the best; thanks very much for taking the time to educate a frustrated banker.

Let me know if I can do anything for youl.

tom · Sep 21st, 2002, 09:55 PM

Thanks guys.
ASAP, I downloaded it, it's not working for this. Seems like it pretty much just does the normal transpose function, correct?

Tom, I tried several times your idea. I don't know why, but it doesn't work. I failed to mention that I only listed 4 records as an example, I really have 500 records to do this to.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 5th, 2002, 09:36 AM	#1
jknichlsn New Member Join Date: Mar 2002 Location: Chicagoland Posts: 4	First posting; glad I found this. If I copy a long list of addresses (say, from the Web) arranged in address blocks, like this: Jack Nicholson Big Bank Corp. 123 Fourth Street Second Floor Chicago, IL 60603 and want to end up with ALL these addresses in columns (all names in Col A, Company in Col B., etc.), how can I do this? To complicate things, some addresses have more rows in the address block than others; I can work that out by sorting the resulting columns and moving the data, unless someone has something very clever in mind. Alternatively, does anyone know of a commercial product, faily cheap, that will convert addresses into ACT! contact management software format? thanks!

Mar 5th, 2002, 09:48 AM	#2
AJ Board Regular Join Date: Mar 2002 Location: =ActiveCell.Address Posts: 478	I had to do the very same thing myself with an output from ACT about a week ago. The following worked. Before I started all the addresses were in column A and started in cell A1. There was also a blank line in between each address and, as in your case, the addresses took up variable numbers of lines. -- Dim myRow As Integer Dim myIncrement As Integer myRow = 2 myIncrement = 1 Range("A1").Select While ActiveCell.Value <> "" ActiveCell.Offset(1, 0).Cut ActiveCell.Offset(0, myIncrement).Select ActiveSheet.Paste Rows(myRow & ":" & myRow).Select Selection.Delete Shift:=xlUp myIncrement = myIncrement + 1 ActiveCell.Offset(-1, 0).Select If ActiveCell.Offset(1, 0).Value = "" And ActiveCell.Offset(2, 0).Value <> "" Then Rows(myRow & ":" & myRow).Select Selection.Delete Shift:=xlUp myIncrement = 1 myRow = myRow + 1 End If Wend -- As with most of my code, it's probably a bit cumbersome, but seems to do the trick. Rgds AJ

Mar 6th, 2002, 12:36 PM	#3
jknichlsn New Member Join Date: Mar 2002 Location: Chicagoland Posts: 4	wow. I am quite new to macros, so I don't have any idea how to proceed, but I'm sure I can find someone around here who can take your code and make it do its thing on my lists. Thank you, VERY much, for taking the time. I really appreciate it.

Apr 2nd, 2002, 03:20 PM	#4
jknichlsn New Member Join Date: Mar 2002 Location: Chicagoland Posts: 4	Well, I thought it would be easy to find out how to convert your text into something that would actually do something, but no. Would you be so kind as to reply with something very fundamental, like: Start with the spreadsheet of your listed addresses opened. Click on "Tools/Macros..." and click on "New Macro" ....' Your response was like that of a native speaker of a foreign language who replied with a flood to the only phrase I've learned in his language. Thanks very much!!

Apr 10th, 2002, 07:10 PM	#5
AJ Board Regular Join Date: Mar 2002 Location: =ActiveCell.Address Posts: 478	Hello again! Sorry I've only just noticed your final post to this thread. I don't usually go that far back. (Was just having a final browse before I go for the day). Anyway, the simple guide to get this to work is as follows: 1. Make a copy of your file incase this totally screws it up! 2. Because I've just given you code above, you need to go directly to the Visual Basic Editor (under Tools>Macros). 3. When you get there, in the project explorer window (usually the box filling the top half of the left side of the screen), right click on your filename and Insert Module 4. At the top of the module type Sub YourMacroName () You'll see this adds End Sub a couple of lines down. Then paste the macro above in between the Sub line and the End Sub line. 5. Back in Excel see if it works... Tools>Macro>Macros pick YourMacroName and click run. If it works we're laughing! If it brings up an error message, reply telling me what it is. If it runs all the way through and does not work as you intended, go back to the Visual Basic Editor. Shrink the window a bit so you can see what's going on in Excel at the same time. Put your cursor anywhere in the macro and hit F8 repeatedly to step through it line by line. This will give you a clue as to where it's falling down. Again, reply and me or someone else will fix it! Rgds AJ

Apr 11th, 2002, 08:57 AM	#6
abe New Member Join Date: Mar 2002 Location: Boston Posts: 4	As to ACT. ACT will import a text delimited file. The key to success with the import is to ensure that your column headers are identical to the field names in ACT. You should split your name column into first name and last name columns. Act will merge these two columns into the contact field automatically.

Apr 11th, 2002, 10:20 AM	#7
jknichlsn New Member Join Date: Mar 2002 Location: Chicagoland Posts: 4	AJ: you are the best; thanks very much for taking the time to educate a frustrated banker. Let me know if I can do anything for youl.

Sep 21st, 2002, 09:55 PM	#8
tom New Member Join Date: Sep 2002 Posts: 10	Thanks guys. ASAP, I downloaded it, it's not working for this. Seems like it pretty much just does the normal transpose function, correct? Tom, I tried several times your idea. I don't know why, but it doesn't work. I failed to mention that I only listed 4 records as an example, I really have 500 records to do this to.