Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

transpose range of cells in excel 2007

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
revahi
Asked:
revahi
1 Solution
 
revahiAuthor Commented:
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
 
StephenJRCommented:
Could you post a small sample workbook?
0
 
Arno KosterCommented:
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
 
TracyVBA DeveloperCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now