?
Solved

transpose range of cells in excel 2007

Posted on 2011-09-09
5
Medium Priority
?
254 Views
Last Modified: 2012-05-12
Dear experts
I know how to trasnpose a column in a row and vice versa, what I want to do in this case, without using vb script if possible is to take a column exported from my property database which simply has a gap (empty cell) between each record and move that as a whole chunk across as a row. This is because the mail merge merge program in Word (2007) only seems to import the field headers as a row, unless someone can tell me how to mail merge from that layout instead it would acheive the same thing.
Fro example, I have 3 records all on column A, the first 2 contain 4 rows and the last one is 5 rows, how would I get this trasnposed into 3 columns using the empty cell between each reacord as a delimiter, or I could insert a delimiter character in between each record such as a tab or comma if that would make it easier.
Your help is much appreciated.
0
Comment
Question by:revahi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 

Author Comment

by:revahi
ID: 36509069
Apologies for my spelling, I rushed this Q out in a hurry without previewing first! I trust it's still understood otherwise please query and I will correct.
thank you
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 36509285
Could you post a small sample workbook?
0
 
LVL 19

Accepted Solution

by:
Arno Koster earned 2000 total points
ID: 36509321
I am not sure if it could be done without using VB, but with a macro it is fairly easy, assuming that you start with an empty worksheet and copy the records to column A
Sub process()

    target_row = 1
    target_col = 3
    For Row = 1 To UsedRange.Rows.Count

        If Range("A" & Row) = "" Then
            '-- empty cell = delimiter, move to new column
            target_row = 1
            target_col = target_col + 1
        Else
            target_row = target_row + 1
            Cells(target_row, target_col) = Range("A" & Row)
        End If

    Next Row

End Sub

Open in new window



0
 
LVL 24

Expert Comment

by:broomee9
ID: 37169473
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question