need help converting row data to column data (Complex)

**ZeroKalvin** · 03-30-2005, 02:34 AM

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

**jindon** · 03-30-2005, 07:26 AM

try the code

Please Login or Register  to view this content.

**Leith Ross** · 03-30-2005, 04:25 PM

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

need help converting row data to column data (Complex)

LinkBack

Thread Tools

Rate This Thread

Display

need help converting row data to column data (Complex)

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions