Solved

Transposing using VBA

Posted on 2014-02-12
5
161 Views
Last Modified: 2014-02-15
Dear Experts:

I got a one-colum data list with the following make-up in Column A

it starts with a 9 digit number with hyphens and one space
the next tow to three cells below are just text
Then comes the next 9 digit number and so forth.

What I would like to achieve using a macro is the following:

The macro is to select the first number in A1 and the two cells below (i.e. from A1 to A3) and then go to B2 and do a transposing action
The macro goes on selecting the next 9 digit number in Cell A4 and the three cells below (i.e. from A4 to A7) and then selects B4 and does a transposing action
and so forth.

I hope I could make myself clear.

Help is much appreciated. Thank you very much in advance for your valuable help.

I have attached a sample file for your convenience.

Regards, Andreas

COLUMN A
20 17-445-12
some text
more text
40 16-445-45
some text
more text
more text
30 40-442-48
some text
more text
more text
18 45-454-22
etc.
Tranposing.xlsx
0
Comment
Question by:AndreasHermle
  • 3
5 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 250 total points
ID: 39853870
Try this.
Option Explicit

Sub Trans()
Dim I As Long
Dim J As Long
Dim X As Long
Dim arrSrc
Dim arrDst

    arrSrc = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value

    ReDim arrDst(1 To UBound(arrSrc), 1 To 1)

    For I = 1 To UBound(arrSrc)
        If IsNumeric(Left(arrSrc(I, 1), 2)) Then
            X = X + 1
            ReDim Preserve arrDst(1 To UBound(arrSrc), 1 To X)

            J = 1
            arrDst(J, X) = arrSrc(I, 1)
            J = J + 1
        Else
            arrDst(J, X) = arrSrc(I, 1)
            J = J + 1
        End If

    Next I
    
    Range("B1").Resize(UBound(arrDst, 2), UBound(arrDst)).Value = Application.Transpose(arrDst)
    
End Sub

Open in new window

0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 250 total points
ID: 39853944
In the desired Results worksheet, you had empty rows between each "record" of data. The previously suggested macro is not providing those empty rows. The one in the snippet below gives you the choice of empty rows or not.

As written, the macro puts empty rows between records. Two statements are commented out, however. If you use them instead of their alternatives, then you will eliminate the empty rows.
Sub Transposition()
Dim vData As Variant, vResults As Variant
Dim i As Long, j As Long, k As Long, n As Long, nCols As Long, nn As Long
Dim rg As Range
nCols = 5       'Maximum number of columns of data in a a record
With ActiveSheet
    Set rg = .Range("A1")   'First cell with data
    Set rg = Range(rg, .Cells(.Rows.Count, rg.Column).End(xlUp)) 'All the data in that column
End With
vData = rg.Value
n = rg.Rows.Count
nn = Int((n + nCols - 1) / nCols)
ReDim vResults(1 To n, 1 To nCols)
For i = 1 To n
    If IsNumeric(Left(vData(i, 1), 2)) Then
        k = k + 1
        j = 1
    Else
        j = j + 1
    End If
    'vResults(k, j) = vData(i, 1)    'If you don't want empty rows between records
    vResults(i - j + 1, j) = vData(i, 1) 'If you want empty rows between records
Next
rg.Cells(1, 2).Resize(n, nCols).Value = vResults    'Empty rows between records
'rg.Cells(1, 2).Resize(k, nCols).Value = vResults    'No empty rows between records
End Sub

Open in new window

0
 

Author Comment

by:AndreasHermle
ID: 39854466
Dear both,

both codes work just fine. I  am really deeply impressed with your excel/macro expertise. Incredible!!
I will do some more testing on my huge data list and then let you know.

Again, thank you very much

Regards, Andreas
0
 

Author Comment

by:AndreasHermle
ID: 39856311
I suggest splitting the points evenly if you do not mind
0
 

Author Closing Comment

by:AndreasHermle
ID: 39861073
Thank you very, very much for your superb and swift support. I really appreciate it.

Regards, Andreas
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Converting time 4 41
pull a field from a programme into Excel 4 17
Excel formula to append date to end of url 6 29
split data by day 21 34
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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…

733 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