+ Reply to Thread
Results 1 to 3 of 3

need help converting row data to column data (Complex)

  1. #1
    Registered User
    Join Date
    03-30-2005
    Posts
    2

    need help converting row data to column data (Complex)

    I would really appreciate it if someone could help me out. I need to reaarange data present in a column to a row format but its not as easy as just using the transpose function. I think a macro is needed because I want to change the format of the data. Please refer to example below:

    Before
    Source Target
    LH10021 LH10022
    LH10021 LH10023
    LH10021 LH10041
    LH10021 LH10051
    LH10021 LH10053
    LH10021 LH10251
    LH10021 LH10252
    LH10021 LH10352
    LH10021 LH10353
    LH10021 LH10722
    LH10021 LH11072

    LH10022 LH10021
    LH10022 LH10023
    LH10022 LH10031
    LH10022 LH10041
    LH10022 LH10043
    LH10022 LH10053
    LH10022 LH10061
    LH10022 LH10063
    LH10022 LH10081
    LH10022 LH10032

    LH10023 LH10021
    LH10023 LH10022
    LH10023 LH10031
    LH10023 LH10033
    LH10023 LH10043
    LH10023 LH10091
    LH10023 LH10252
    LH10023 LH10531
    LH10023 LH10251
    LH10023 LH10532


    After
    LH10021 LH10022 LH10023 LH10041 LH10051 LH10053 LH10251 LH10252 LH10352 LH10353 LH10722 LH11072
    LH10022 LH10021 LH10023 LH10031 LH10041 LH10043 LH10053 LH10061 LH10063 LH10081 LH10032
    LH10023 LH10021 LH10022 LH10031 LH10033 LH10043 LH10091 LH10252 LH10531 LH10251 LH10532

    As you can see formatting the data as such would require me to transpose the data and then delete all the rows from which the data was transposed. The number of source cells is much more than shown in the example also the number of target cells varies for each source cell so a standard macro would not suffice. If I did this manually it would take me a few days.

    If someone could please provide for a macro which can help me out or help me in writing one. Thank you all.

    ZeroKalvin

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418
    try the code
    Please Login or Register  to view this content.

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello Zerokalvin,

    Here is marco to help you out. It will "transpose" the data for you. Copy and paste the code into standard project Module. The macro starts at the cell you give it and continues until the first blank row is found.

    Calling the Macro:

    Call RowsToColumn("A1")

    Or

    RowsToColumns ("A1")

    Macro Code:
    _________________________________________________________________

    Public Sub RowsToColumn(ByVal First_Cell As String)

    Dim C As Long 'Column Number
    Dim I As Long 'Initial Row
    Dim N As Long 'Next Row
    Dim R As Long 'Row Number
    Dim T As Long 'Temporary Total of Rowa
    Dim Wks As Worksheet

    Application.ScreenUpdating = False
    Set Wks = ActiveSheet

    With Wks.Range(First_Cell)
    C = .Column
    R = .Row
    End With

    I = R
    N = R + 1
    T = 1

    With Wks
    Do
    If .Cells(N, C).Value = "" Then Exit Sub
    If .Cells(N, C).Value = .Cells(I, C).Value Then
    T = T + 1
    .Cells(I, C).Offset(0, T).Value = .Cells(N, C + 1).Value
    N = N + 1
    Else
    If T > 1 Then
    .Range(Cells(I + 1, C), Cells(N - 1, C + 1)).Select
    Selection.Delete Shift:=xlShiftUp
    End If
    I = I + 1
    N = I + 1
    T = 1
    End If
    Loop
    End With

    Application.ScreenUpdating = True
    Wks.Cells(N, C).Select

    End Sub

    _________________________________________________________________

    Hope this is what you're looking for,
    Leith Ross
    Last edited by Leith Ross; 03-30-2005 at 04:29 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1