Moving Rows to Columns

[Tom Garner]

I've got a project were I've been given a list of contacts in an Excel
file however instead of having columns for First Name, Last Name,
Company, Address etc., all information is in Column A stacked one on
top of the other.

What I have now:

1 Frist/Last Name
2 Company
3 Address
4
5 Frist/Last Name
6 Company
7 Address
8 Frist/Last Name
9 Company
10 Address


What I want:
A B C
D
Frist Name Last Name Company
Address
Frist Name Last Name Company
Address
Frist Name Last Name Company
Address
Frist Name Last Name Company
Address

I'd also like to split the combined first/last name cells into
separate First Name, Last Name cells for mail merge purposes.

Any help out there?

-Tom
(e-mail address removed)

 

[Domenic]

Hi Tom,

According to your example, the format is inconsistent. For example, Row
4 is blank between one address and the other but there's no blank row
between the next and the other.

Can you confirm the format? Does each address use the same number of
rows? And is there a blank row between each address?

 

[Tom Garner]

Yes, my error. There are actually 10 cells, a blank and then an 11th
and final cell to every record with another blank before the next
record.


(1) Last/First Name - I'd like to split these into two cells
(2) Title
(3) Company
(4) Street Address
(5) City, State Zip
(6) Phone
(7) Email
(8) Fax
(9) Web
(10) Blank Cell
(11) Specialties
(12) Blank Cell
(13) Last/First Name
etc.

I hope this clears up my error.

Thanks,
-Tom

 

[Tom Garner]

I just discovered there is one more cell between Email and Fax for
Voice Mail.

Also, is there a way to combine sheets? There are about 300 names in
this list but only five to a sheet. I'd like them all in one window.

-Tom

 

[Domenic]

Hi Tom,

With about 300 names in the list and only five to a sheet, it would seem
that VBA would be your best solution. Unfortunately, I don't have any
real experience with it and won't be able to help in that regard.

However, I am aware of one non-VBA solution you can use (there may be a
better way). It would be a bit more involved, but it can be done. You
would first use Word to combine all your records, then in Excel you
would use a formula to transpose your list of contacts from the one
column to individual rows, and then you would continue to make the
necessary refinements to your list.

If you're interested, let me know and I'll provide you with the details.

 

[JE McGimpsey]

I've got a project were I've been given a list of contacts in an Excel
file however instead of having columns for First Name, Last Name,
Company, Address etc., all information is in Column A stacked one on
top of the other.

What I have now:

1 Frist/Last Name
2 Company
3 Address
4
5 Frist/Last Name
6 Company
7 Address
8 Frist/Last Name
9 Company
10 Address


What I want:
A B C
D
Frist Name Last Name Company
Address
Frist Name Last Name Company
Address
Frist Name Last Name Company
Address
Frist Name Last Name Company
Address

I'd also like to split the combined first/last name cells into
separate First Name, Last Name cells for mail merge purposes.

You could use VBA to do this, but I'm not sure from your example how you
could tell when a new record starts - i.e., there's a blank line between
records one and two, but none between records two and three. Are all
records the same size?

As for splitting the names, after you transpose the data to columns, you
can insert a blank column and use Data/Text to Columns, using Delimited,
and "/" as the delimiter.

 

[Tom Garner]

Dear Domenic22:

What is VBA?

-Tom

Hi Tom,

With about 300 names in the list and only five to a sheet, it would seem
that VBA would be your best solution. Unfortunately, I don't have any
real experience with it and won't be able to help in that regard.

However, I am aware of one non-VBA solution you can use (there may be a
better way). It would be a bit more involved, but it can be done. You
would first use Word to combine all your records, then in Excel you
would use a formula to transpose your list of contacts from the one
column to individual rows, and then you would continue to make the
necessary refinements to your list.

If you're interested, let me know and I'll provide you with the details.

 

[Domenic]

What is VBA?

VBA stands for Visual Basic for Applications and is used to write
programming code to automate and enhance the performance of
applications, or something like that. VBA would be ideal for your
situation.

 

[JE McGimpsey]

What is VBA?

Visual Basic for Applications is the Macro and Programming language for
Office. You can use it to create new functions, to automate steps you'd
do manually, or to perform applications that can't be done manually in
XL.

If you could give a thorough description of your data and what you want
it to look like, a macro could be written to accomplish it.

 

[Tom Garner]

To (e-mail address removed),

Thank you.

It may be just as easy for me to email you the file and let you look
at it. There are 52 sheets that I'd like combined into one with a row
of headers as follows

First Name - Last Name - Title - Company - Address - City/State/Zip -
Phone - Email - Voice Mail - Fax - Web Site - Specialties

(There should be about 300+ contacts in this list.)

If you could send me the macro with instructions on how to use it I'd
be very grateful.

-Tom

 

[Tom Garner]

To (e-mail address removed),

Thank you.

It may be just as easy for me to email you the file and let you look
at it. There are 52 sheets that I'd like combined into one with a row
of headers as follows

First Name - Last Name - Title - Company - Address - City/State/Zip -
Phone - Email - Voice Mail - Fax - Web Site - Specialties

(There should be about 300+ contacts in this list.)

If you could send me the macro with instructions on how to use it I'd
be very grateful.

-Tom

 

[Tom Garner]

I tried to send file to email address listed but it won't go through.

What I have is one column with all the contact information listed one
after the other in that same column. What I'm needing to do is make
mulitiple columns and move the contact information into one row.

Here are how the information is listed in the column now:



(Rows)

(1) Last/First Name
(2) Title
(3) Company
(4) Street Address
(5) City, State Zip
(6) Phone
(7) Email
(8) Voice Mail
(9) Fax
(10) Web
(11) - Empty Cell
(12) Specialties
(13) - Empty Cell
NEXT SET OF CONTACT INFORMATION BEGINS HERE
(14) Last/First Name
(15) Title
(16) Company
(17) Street Address
(18) City, State Zip
(19) Phone
(20) Email
(21) Voice Mail
(22) Fax
(23) Web
(24) - Empty Cell
(15) Specialties
(26) - Empty Cell
AND THEN ANOTHER SET OF CONTACT INFORMATION BEGINS HERE
(27) Last/First Name
(28) Title
(29) Company
(30) Street Address
(31) City, State Zip
(32) Phone
(33) Email
(34) Voice Mail
(35) Fax
(36) Web
(37) - Empty Cell
(38) Specialties
(39) - Empty Cell
AND THIS CONTINUES THIS WAY TO THE END OF THE LIST


From this Column A should have info from rows (1), (14), (27) etc in
it
The same Column B should have info from rows (2), (15), (28)
And then Column C should have info from rows (3), (16), (29)

This would go through the end of the list.

The other problem was when this information was scanned in and had the
OCR export it into Excel it put each page into its own sheet. It would
be quite helpful if I could combine all 52 sheets into one.

-Tom

 

[JE McGimpsey]

I tried to send file to email address listed but it won't go through.

What I have is one column with all the contact information listed one
after the other in that same column. What I'm needing to do is make
mulitiple columns and move the contact information into one row.

Here are how the information is listed in the column now:



(Rows)

(1) Last/First Name
(2) Title
(3) Company
(4) Street Address
(5) City, State Zip
(6) Phone
(7) Email
(8) Voice Mail
(9) Fax
(10) Web
(11) - Empty Cell
(12) Specialties
(13) - Empty Cell
NEXT SET OF CONTACT INFORMATION BEGINS HERE

The other problem was when this information was scanned in and had the
OCR export it into Excel it put each page into its own sheet. It would
be quite helpful if I could combine all 52 sheets into one.

One way:

Since you aren't very familiar with macros, first see David McRitchie's
"Getting Started with Macros":

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

Then put this in a regular code module.

Public Sub TransposeAndConsolidate()
Dim vArr As Variant
Dim rDest As Range
Dim i As Long
Dim j As Long
Application.ScreenUpdating = False
With Worksheets.Add(Before:=Sheets(1))
.Name = "Consolidated"
Set rDest = .Range("A1")
End With
For i = 2 To Worksheets.Count
With Worksheets(i)
For j = 1 To .Range("A" & Rows.Count).End(xlUp).Row Step 13
vArr = .Cells(j, 1).Resize(13, 1).Value
rDest.Resize(1, 13).Value = Application.Transpose(vArr)
Set rDest = rDest.Offset(1, 0)
Next j
End With
Next i
Application.ScreenUpdating = True
End Sub

 

[Dr Rocket]

Your problem to convert a column into a two dimensional array has a
simple solution using just Excel functions.

You can place the resultant array anywhere you want, but to make the
explanation simple, start it on a worksheet at A1. Say you want it cc
columns wide by rr rows deep. Now define the original column by an
array name, say List. In A1 insert the formula

= INDEX(List,COLUMN() + cc*(ROW()-1)).

Fill the whole resultant array space by copying down and copying
right. You're done.

This has an unlimited number of variations. You can place the array
anywhere you want, skip unwanted entries (replacing COLUMN() with an
index column written, say, above or ROW() with an index row written
maybe to the left of the resultant), extract some instead of all of
each original entry, convert an mm by nn array into a different pp by
qq array, insert as values or text, etc.

Jeff