Importing Names
 

I am importing a list of names in column form and would like to tranfer them
into Rows.
Name
Address
City
Phone
Fax
Email
I would like it show Name, Address, City, Ph., Fax, Email
Is there a way to make an macro so it may be automatically done when
importing.
I am just getting started at learning how to work with Macros. Any help
would be much appreciated
Thank - You
B.W.

Importing Names
 

If your data is consistently 6 rows enter this in B1

=INDEX($A:$A,(ROWS($1:1)-1)*6+COLUMNS($A:B)-1)

Copy across to G1.

Select B1:G1 and copy down until you get zeros showing up.

Convert these formulas to values by copypaste specialvaluesokesc.

If you want a macro............enter 6 into InputBox when it appears.

Sub ColtoRows()
Dim rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Long

Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")

For i = 1 To rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(rng.Row, "A")).ClearContents

End Sub



Gord Dibben MS Excel MVP


On Sat, 3 Mar 2007 10:10:02 -0800, B.W. wrote:

I am importing a list of names in column form and would like to tranfer them
into Rows.
Name
Address
City
Phone
Fax
Email
I would like it show Name, Address, City, Ph., Fax, Email
Is there a way to make an macro so it may be automatically done when
importing.
I am just getting started at learning how to work with Macros. Any help
would be much appreciated
Thank - You
B.W.

Importing Names
 

Thank-You Gordon for your help.
As Stated I am new to Excel as well as creating macros and having a hard
time understanding them.
I entered the data in B1 and copied to G1 which Showed #REF. I then
Copied down but it did not show any 0. It only Showed #REF in every cell. I
spent some time trying to figure out what I did wrong. In this application
the data will require 6 rows, however another application will require more
rows. Do I enter 6 in the formula where it says enter the number of columns
desired.
BW

"Gord Dibben" wrote:

If your data is consistently 6 rows enter this in B1

=INDEX($A:$A,(ROWS($1:1)-1)*6+COLUMNS($A:B)-1)

Copy across to G1.

Select B1:G1 and copy down until you get zeros showing up.

Convert these formulas to values by copypaste specialvaluesokesc.

If you want a macro............enter 6 into InputBox when it appears.

Sub ColtoRows()
Dim rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Long

Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")

For i = 1 To rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(rng.Row, "A")).ClearContents

End Sub



Gord Dibben MS Excel MVP


On Sat, 3 Mar 2007 10:10:02 -0800, B.W. wrote:

I am importing a list of names in column form and would like to tranfer them
into Rows.
Name
Address
City
Phone
Fax
Email
I would like it show Name, Address, City, Ph., Fax, Email
Is there a way to make an macro so it may be automatically done when
importing.
I am just getting started at learning how to work with Macros. Any help
would be much appreciated
Thank - You
B.W.

Importing Names
 

Assuming your data is in column A starting at A1 you would copy the formula I
posted into B1. Do not enter any data in B1, just the formula to drag across
then down as posted.

If you have more than 6 rows per set you change the 6 to whatever

i.e. =INDEX($A:$A,(ROWS($1:1)-1)*8+COLUMNS($A:B)-1) if each set has 8 rows.

As far as the macro goes...........you would enter whatever number of columns
you need. No formulas involved with the macro.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord

On Sun, 4 Mar 2007 12:13:31 -0800, B.W. wrote:

Thank-You Gordon for your help.
As Stated I am new to Excel as well as creating macros and having a hard
time understanding them.
I entered the data in B1 and copied to G1 which Showed #REF. I then
Copied down but it did not show any 0. It only Showed #REF in every cell. I
spent some time trying to figure out what I did wrong. In this application
the data will require 6 rows, however another application will require more
rows. Do I enter 6 in the formula where it says enter the number of columns
desired.
BW

"Gord Dibben" wrote:

If your data is consistently 6 rows enter this in B1

=INDEX($A:$A,(ROWS($1:1)-1)*6+COLUMNS($A:B)-1)

Copy across to G1.

Select B1:G1 and copy down until you get zeros showing up.

Convert these formulas to values by copypaste specialvaluesokesc.

If you want a macro............enter 6 into InputBox when it appears.

Sub ColtoRows()
Dim rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Long

Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")

For i = 1 To rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(rng.Row, "A")).ClearContents

End Sub



Gord Dibben MS Excel MVP


On Sat, 3 Mar 2007 10:10:02 -0800, B.W. wrote:

I am importing a list of names in column form and would like to tranfer them
into Rows.
Name
Address
City
Phone
Fax
Email
I would like it show Name, Address, City, Ph., Fax, Email
Is there a way to make an macro so it may be automatically done when
importing.
I am just getting started at learning how to work with Macros. Any help
would be much appreciated
Thank - You
B.W.

Importing Names
 

I made a mistake. I did enter the formula not data in B1. In Column A is
where the list of names are. I Copied across to G1 and down but did not see
any 0's. It stills #REF! in every cell instead of 0. How would this change
the List from Column to row format?
Thanks
BW

"Gord Dibben" wrote:

Assuming your data is in column A starting at A1 you would copy the formula I
posted into B1. Do not enter any data in B1, just the formula to drag across
then down as posted.

If you have more than 6 rows per set you change the 6 to whatever

i.e. =INDEX($A:$A,(ROWS($1:1)-1)*8+COLUMNS($A:B)-1) if each set has 8 rows.

As far as the macro goes...........you would enter whatever number of columns
you need. No formulas involved with the macro.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord

On Sun, 4 Mar 2007 12:13:31 -0800, B.W. wrote:

Thank-You Gordon for your help.
As Stated I am new to Excel as well as creating macros and having a hard
time understanding them.
I entered the data in B1 and copied to G1 which Showed #REF. I then
Copied down but it did not show any 0. It only Showed #REF in every cell. I
spent some time trying to figure out what I did wrong. In this application
the data will require 6 rows, however another application will require more
rows. Do I enter 6 in the formula where it says enter the number of columns
desired.
BW

"Gord Dibben" wrote:

If your data is consistently 6 rows enter this in B1

=INDEX($A:$A,(ROWS($1:1)-1)*6+COLUMNS($A:B)-1)

Copy across to G1.

Select B1:G1 and copy down until you get zeros showing up.

Convert these formulas to values by copypaste specialvaluesokesc.

If you want a macro............enter 6 into InputBox when it appears.

Sub ColtoRows()
Dim rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Long

Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")

For i = 1 To rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(rng.Row, "A")).ClearContents

End Sub



Gord Dibben MS Excel MVP


On Sat, 3 Mar 2007 10:10:02 -0800, B.W. wrote:

I am importing a list of names in column form and would like to tranfer them
into Rows.
Name
Address
City
Phone
Fax
Email
I would like it show Name, Address, City, Ph., Fax, Email
Is there a way to make an macro so it may be automatically done when
importing.
I am just getting started at learning how to work with Macros. Any help
would be much appreciated
Thank - You
B.W.

Importing Names
 

If you want to send the workbook to my email I will have a look at it.

Change the AT and DOT in gorddibbATshawDOTca


Gord

On Sun, 4 Mar 2007 13:15:01 -0800, B.W. wrote:

I made a mistake. I did enter the formula not data in B1. In Column A is
where the list of names are. I Copied across to G1 and down but did not see
any 0's. It stills #REF! in every cell instead of 0. How would this change
the List from Column to row format?
Thanks
BW

"Gord Dibben" wrote:

Assuming your data is in column A starting at A1 you would copy the formula I
posted into B1. Do not enter any data in B1, just the formula to drag across
then down as posted.

If you have more than 6 rows per set you change the 6 to whatever

i.e. =INDEX($A:$A,(ROWS($1:1)-1)*8+COLUMNS($A:B)-1) if each set has 8 rows.

As far as the macro goes...........you would enter whatever number of columns
you need. No formulas involved with the macro.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord

On Sun, 4 Mar 2007 12:13:31 -0800, B.W. wrote:

Thank-You Gordon for your help.
As Stated I am new to Excel as well as creating macros and having a hard
time understanding them.
I entered the data in B1 and copied to G1 which Showed #REF. I then
Copied down but it did not show any 0. It only Showed #REF in every cell. I
spent some time trying to figure out what I did wrong. In this application
the data will require 6 rows, however another application will require more
rows. Do I enter 6 in the formula where it says enter the number of columns
desired.
BW

"Gord Dibben" wrote:

If your data is consistently 6 rows enter this in B1

=INDEX($A:$A,(ROWS($1:1)-1)*6+COLUMNS($A:B)-1)

Copy across to G1.

Select B1:G1 and copy down until you get zeros showing up.

Convert these formulas to values by copypaste specialvaluesokesc.

If you want a macro............enter 6 into InputBox when it appears.

Sub ColtoRows()
Dim rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Long

Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")

For i = 1 To rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(rng.Row, "A")).ClearContents

End Sub



Gord Dibben MS Excel MVP


On Sat, 3 Mar 2007 10:10:02 -0800, B.W. wrote:

I am importing a list of names in column form and would like to tranfer them
into Rows.
Name
Address
City
Phone
Fax
Email
I would like it show Name, Address, City, Ph., Fax, Email
Is there a way to make an macro so it may be automatically done when
importing.
I am just getting started at learning how to work with Macros. Any help
would be much appreciated
Thank - You
B.W.