Excel Transpose Cells in Excel

M

Mikemcc817

Joined
Feb 24, 2015
Messages
1
Reaction score
0
So here is my problem:
I would like to transpose this:

A
B
C
D
E

Into this:
A B C D E

BUT some of the items in my lists are varying in number of rows in the cluster:

10Th Church Of Christ Scientist
171 MacDougal Street
New York, NY 10011-9212
(212) 777-1717
Categories: Charitable & Non-Profit Organizations, Religious Organizations

92Nd Street Y - Bronfman Center For Jewish Life
1395 Lexington Avenue
New York, NY 10128-1612
(212) 415-5765
Marty Maskowitz (Manager)
Categories: Charitable & Non-Profit Organizations, Youth Organizations Centers & Clubs

2041 Five Avenue
New York, NY 10035-1508
(212) 987-2041
Categories: Charitable & Non-Profit Organizations

Every cluster of info starts with the name and end with the category, but some have added info, and some not all the info.
Any suggestions on how I could transpose this cleanly? This file is HUGE so doing it by hand would be hard. I was thinking if there was a way to sort this by all the clusters that have 6 items, then by 5 items, and so on.
 
Last edited by a moderator:
Every cluster of info starts with the name and end with the category, but some have added info, and some not all the info.
Click to expand...
Due to the complexity of your requirement, I can't give an easy answer. Your third cluster only contains an address and phone #; that doesn't make sense. What are you trying to accomplish?

Actually, if I were dealing with a large file of this type in Excel, I would probably transfer the data into Access; a database seems like a much better way to handle such data.
 
This can be done using Excel VBA,
3fa8c50d02bc3d3316ed1ddd90a566af

b81c0a447dd0b665205bae94b189e587





http://www.xlorate.com/excel-questions.html#Copy and Transpose


The above links are examples of your workbook, Copy and Transpose Loop

Code: 
Sub Copy_Transpose()
  Dim Ra As Range
  Application.ScreenUpdating = False
  For Each Ra In Columns("A").SpecialCells(xlCellTypeConstants, 23).Areas
  Ra.Copy
  Worksheets("Sheet2"). _
  Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True
  Next Ra
  Application.CutCopyMode = False
End Sub

Example Workbook
 
Last edited:
Hi Dave,
I am not sure you addressed the problem. Your code did transpose the values but did not address the data issue. Your code put phone numbers in the same column as the location.
 
Back
Top