I exported a few hundred contacts from my Windows Live Mail as CSV file and
opened it in Excel 2002. Of course, I can easily print this spreadsheet as
it is, where each row contains all information about each entry. However,
I'd like to print this information in a form of an address book:

Last Name, First Name
email address
Street Address, City, Zip
Home Phone
Business phone

Last Name, First Name
email address
etc.

Is there a way to do so?

--
Ilya Zeldes
Fort Myers, Florida

How is the original data laid out?

All in one cell?

In one row but separate cells?

Is Last name, First name in one cell or two?

How about Street Address, City, Zip?

If you wish, send me a copy of the workbook.

gorddibbATshawDOTca change the obvious for ny email address.


Gord Dibben MS Excel MVP

On Sat, 9 Jan 2010 20:05:30 -0500, "Ilya Zeldes"
wrote:

I exported a few hundred contacts from my Windows Live Mail as CSV file and
opened it in Excel 2002. Of course, I can easily print this spreadsheet as
it is, where each row contains all information about each entry. However,
I'd like to print this information in a form of an address book:

Last Name, First Name
email address
Street Address, City, Zip
Home Phone
Business phone

Last Name, First Name
email address
etc.

Is there a way to do so?

Gord,

in this workbook (just one sheet), each row contains one entry. First
cell -Last Name, second cell - First Name, etc., each cell contains just one
item: street address, city, state, zip...

Ilya.

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
How is the original data laid out?

All in one cell?

In one row but separate cells?

Is Last name, First name in one cell or two?

How about Street Address, City, Zip?

If you wish, send me a copy of the workbook.

gorddibbATshawDOTca change the obvious for ny email address.


Gord Dibben MS Excel MVP

On Sat, 9 Jan 2010 20:05:30 -0500, "Ilya Zeldes"
wrote:

I exported a few hundred contacts from my Windows Live Mail as CSV file
and
opened it in Excel 2002. Of course, I can easily print this spreadsheet as
it is, where each row contains all information about each entry. However,
I'd like to print this information in a form of an address book:

Last Name, First Name
email address
Street Address, City, Zip
Home Phone
Business phone

Last Name, First Name
email address
etc.

Is there a way to do so?

Rather than explain the process here, how about you send me the workbook?

I'll transpose it all for you and show you how I did it.

gorddibbATshawDOTca


Gord


On Sun, 10 Jan 2010 18:01:33 -0500, "Ilya Zeldes"
wrote:

Gord,

in this workbook (just one sheet), each row contains one entry. First
cell -Last Name, second cell - First Name, etc., each cell contains just one
item: street address, city, state, zip...

Ilya.

"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
How is the original data laid out?

All in one cell?

In one row but separate cells?

Is Last name, First name in one cell or two?

How about Street Address, City, Zip?

If you wish, send me a copy of the workbook.

gorddibbATshawDOTca change the obvious for ny email address.


Gord Dibben MS Excel MVP

On Sat, 9 Jan 2010 20:05:30 -0500, "Ilya Zeldes"
wrote:

I exported a few hundred contacts from my Windows Live Mail as CSV file
and
opened it in Excel 2002. Of course, I can easily print this spreadsheet as
it is, where each row contains all information about each entry. However,
I'd like to print this information in a form of an address book:

Last Name, First Name
email address
Street Address, City, Zip
Home Phone
Business phone

Last Name, First Name
email address
etc.

Is there a way to do so?

Frankly, Gord, I'm hesitant to share my address book with anyone. I presume
and hope that there is a way to do what I want and it is described
somewhere. If you may direct me to that source, I'd appreciate it.

Ilya

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Rather than explain the process here, how about you send me the workbook?

I'll transpose it all for you and show you how I did it.

gorddibbATshawDOTca


Gord


On Sun, 10 Jan 2010 18:01:33 -0500, "Ilya Zeldes"
wrote:

Gord,

in this workbook (just one sheet), each row contains one entry. First
cell -Last Name, second cell - First Name, etc., each cell contains just
one
item: street address, city, state, zip...

Ilya.

"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
How is the original data laid out?

All in one cell?

In one row but separate cells?

Is Last name, First name in one cell or two?

How about Street Address, City, Zip?

If you wish, send me a copy of the workbook.

gorddibbATshawDOTca change the obvious for ny email address.


Gord Dibben MS Excel MVP

On Sat, 9 Jan 2010 20:05:30 -0500, "Ilya Zeldes"
wrote:

I exported a few hundred contacts from my Windows Live Mail as CSV file
and
opened it in Excel 2002. Of course, I can easily print this spreadsheet
as
it is, where each row contains all information about each entry.
However,
I'd like to print this information in a form of an address book:

Last Name, First Name
email address
Street Address, City, Zip
Home Phone
Business phone

Last Name, First Name
email address
etc.

Is there a way to do so?

I appreciate your concern about sharing your address book.

I may be a spammer/scammer who trolls for new victims.

First of all you must concatenate the fields you want in one cell.

=A1 & ", " & B1 will return Lastname, Firstname.

Same for Street Address, City, State, Zip.

Do this in an unused range of your sheet..

Arrange the email, phone, phone columns as you want.

You now have five adjacent columns.

Copy those and paste specialvalues to a new sheet at A1

Run this macro on your 5 columns.

Sub rows_to_col()

Dim wks As Worksheet
Dim colnos As Long
Dim CopytoSheet As Worksheet

If ActiveSheet.Name = "Copyto" Then
MsgBox "Active Sheet Not Valid" & Chr(13) _
& "Try Another Worksheet."
Exit Sub
Else
Set wks = ActiveSheet
Application.ScreenUpdating = False
For Each Wksht In Worksheets
With Wksht
If .Name = "Copyto" Then
Application.DisplayAlerts = False
Sheets("Copyto").Delete
End If
End With
Next
Application.DisplayAlerts = True
Set CopytoSheet = Worksheets.Add
CopytoSheet.Name = "Copyto"
wks.Activate
Range("A1").Select
colnos = InputBox("Enter Number of Columns to Transpose to Rows")

Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
With ActiveCell
.Resize(1, colnos).Copy
End With
Sheets("Copyto").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, _
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
ActiveSheet.Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select
ActiveCell.Offset(2, 0).Select
Selection.EntireRow.Insert
wks.Activate
ActiveCell.Select
Loop
Sheets("Copyto").Activate
End If
End Sub


Gord


On Sun, 10 Jan 2010 21:16:54 -0500, "Ilya Zeldes"
wrote:

Frankly, Gord, I'm hesitant to share my address book with anyone. I presume
and hope that there is a way to do what I want and it is described
somewhere. If you may direct me to that source, I'd appreciate it.

Ilya

"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Rather than explain the process here, how about you send me the workbook?

I'll transpose it all for you and show you how I did it.

gorddibbATshawDOTca


Gord


On Sun, 10 Jan 2010 18:01:33 -0500, "Ilya Zeldes"
wrote:

Gord,

in this workbook (just one sheet), each row contains one entry. First
cell -Last Name, second cell - First Name, etc., each cell contains just
one
item: street address, city, state, zip...

Ilya.

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
How is the original data laid out?

All in one cell?

In one row but separate cells?

Is Last name, First name in one cell or two?

How about Street Address, City, Zip?

If you wish, send me a copy of the workbook.

gorddibbATshawDOTca change the obvious for ny email address.


Gord Dibben MS Excel MVP

On Sat, 9 Jan 2010 20:05:30 -0500, "Ilya Zeldes"
wrote:

I exported a few hundred contacts from my Windows Live Mail as CSV file
and
opened it in Excel 2002. Of course, I can easily print this spreadsheet
as
it is, where each row contains all information about each entry.
However,
I'd like to print this information in a form of an address book:

Last Name, First Name
email address
Street Address, City, Zip
Home Phone
Business phone

Last Name, First Name
email address
etc.

Is there a way to do so?

Thanks for your suggestions.

Ilya

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
I appreciate your concern about sharing your address book.

I may be a spammer/scammer who trolls for new victims.

First of all you must concatenate the fields you want in one cell.

=A1 & ", " & B1 will return Lastname, Firstname.

Same for Street Address, City, State, Zip.

Do this in an unused range of your sheet..

Arrange the email, phone, phone columns as you want.

You now have five adjacent columns.

Copy those and paste specialvalues to a new sheet at A1

Run this macro on your 5 columns.

Sub rows_to_col()

Dim wks As Worksheet
Dim colnos As Long
Dim CopytoSheet As Worksheet

If ActiveSheet.Name = "Copyto" Then
MsgBox "Active Sheet Not Valid" & Chr(13) _
& "Try Another Worksheet."
Exit Sub
Else
Set wks = ActiveSheet
Application.ScreenUpdating = False
For Each Wksht In Worksheets
With Wksht
If .Name = "Copyto" Then
Application.DisplayAlerts = False
Sheets("Copyto").Delete
End If
End With
Next
Application.DisplayAlerts = True
Set CopytoSheet = Worksheets.Add
CopytoSheet.Name = "Copyto"
wks.Activate
Range("A1").Select
colnos = InputBox("Enter Number of Columns to Transpose to Rows")

Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
With ActiveCell
.Resize(1, colnos).Copy
End With
Sheets("Copyto").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, _
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
ActiveSheet.Cells(Rows.Count,
ActiveCell.Column).End(xlUp).Select
ActiveCell.Offset(2, 0).Select
Selection.EntireRow.Insert
wks.Activate
ActiveCell.Select
Loop
Sheets("Copyto").Activate
End If
End Sub


Gord


On Sun, 10 Jan 2010 21:16:54 -0500, "Ilya Zeldes"
wrote:

Frankly, Gord, I'm hesitant to share my address book with anyone. I
presume
and hope that there is a way to do what I want and it is described
somewhere. If you may direct me to that source, I'd appreciate it.

Ilya

"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
Rather than explain the process here, how about you send me the
workbook?

I'll transpose it all for you and show you how I did it.

gorddibbATshawDOTca


Gord


On Sun, 10 Jan 2010 18:01:33 -0500, "Ilya Zeldes"
wrote:

Gord,

in this workbook (just one sheet), each row contains one entry. First
cell -Last Name, second cell - First Name, etc., each cell contains just
one
item: street address, city, state, zip...

Ilya.

"Gord Dibben" <gorddibbATshawDOTca wrote in message
m...
How is the original data laid out?

All in one cell?

In one row but separate cells?

Is Last name, First name in one cell or two?

How about Street Address, City, Zip?

If you wish, send me a copy of the workbook.

gorddibbATshawDOTca change the obvious for ny email address.


Gord Dibben MS Excel MVP

On Sat, 9 Jan 2010 20:05:30 -0500, "Ilya Zeldes"
wrote:

I exported a few hundred contacts from my Windows Live Mail as CSV
file
and
opened it in Excel 2002. Of course, I can easily print this
spreadsheet
as
it is, where each row contains all information about each entry.
However,
I'd like to print this information in a form of an address book:

Last Name, First Name
email address
Street Address, City, Zip
Home Phone
Business phone

Last Name, First Name
email address
etc.

Is there a way to do so?