Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    columns to rows (xp)

    <font color=red>red</font color=red> Is there a way to convert columns to rows in Excel? I have attached a small portion of the table. I have 23,000 records to convert into rows then all of this is going into a comma-delimited file to be dumped into an Oracle database. What I was is to have all the colums in rows 2-13 converted into a single row. Ideally, I would only want the Acct_Num once. So for each patient, I have all their info on one row. But I thought even if that can't be done, maybe the whole thing could be and then I can delete the extra ID numbers.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: columns to rows (xp)

    Not sure I understand what you want/need? could you provide what the desired output would look like for this sample data?

    Steve

  3. #3
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: columns to rows (xp)

    Sure, I know it's confusing. We are converting to an Oracle database for this compliance program and they will only let me send one row per patient. I tried to get them to let me send it in two files but they said no. And this is just a part of the row. For some patients I will have 256 fields on the row!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: columns to rows (xp)

    This macro should do it, I think:

    Sub Transform()
    ' Row index
    Dim lngRow As Long
    ' Number of rows
    Dim lngMaxRow As Long
    ' Row we're currently filling from
    Dim lngCurRow As Long
    ' Row we're currently filling to
    Dim lngFillRow As Long

    ' Initialize
    lngMaxRow = Range("A1").CurrentRegion.Rows.Count
    lngCurRow = 1
    lngFillRow = 1

    ' Loop through rows
    For lngRow = 2 To lngMaxRow
    If Cells(lngRow, 1) = Cells(lngFillRow, 1) Then
    ' Move row
    Range(Cells(lngRow, 1), Cells(lngRow, 1).End(xlToRight)).Cut _
    Cells(lngFillRow, 1).End(xlToRight).Offset(0, 1)
    Else
    ' New Acct_Num
    lngCurRow = lngRow
    lngFillRow = lngFillRow + 1
    ' Move first row of Acct_Num
    Range(Cells(lngRow, 1), Cells(lngRow, 1).End(xlToRight)).Cut _
    Cells(lngFillRow, 1)
    End If
    Next lngRow
    End Sub

  5. #5
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: columns to rows (xp)

    Hans, This is my first time using a macro in Excel so I am really green. I ran the macro and it worked for the first ten lines but the patient has 3 more dispensings. I am thinking Excel reached it's limit on the number of fields in the row. And I know this sounds really ignorant but do I have to run the macro on each new acct number?

  6. #6
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: columns to rows (xp)

    I am getting an error when I run it again on another row. When I debugged it was this line:
    'Move row
    Range(Cells(lngRow, 1), Cells(lngRow, 1).End(xlToRight)).Cut Cells(lngFillRow, 1).End(xlToRight).Offset(0, 1)
    Else

    It is 'Run Time Error:1004' and it says "This is not a valid selection". Several reasons could be: copy and paste areas cannot overlap unless they are the same size and shape. and If you're using the Create command on the name sub menu of the Insert menu. The row or column containing the proposed names won't. .......... and then it just trails off and I can't read the rest of the message.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: columns to rows (xp)

    1. An Excel worksheet has 256 columns. This is a fixed hard limit. I didn't build in error handling, so if the total number of fields for a single Acct_num would exceed 256, you'd get an error message.

    2. I assumed that you have no empty rows or columns within the data.

    Perhaps you could post a small sample worksheet that causes an error (please don't try to post 23,000 records!) Take care to scramble sensitive data.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: columns to rows (xp)

    It should do the entire dataset at once.

    If you have more than 36 "rows" of data for a patient you will have too many columns (36 rows * 7 cols = 252, there are only 256 columns available)

    If your data has blank rows in it the macro will stop at the blank rows

    Steve

  9. #9
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: columns to rows (xp)

    Hans or Steve or anyone, Here's a file with two patients. And actually, if you have the time, what I really need is not the whole next row but the columns K-P, T, & X (these are the fields: RX Number #1|Drug Name #1,Drug #1 Quantity, Drug #1 Days Supply, Drug #1 Date of Fill, Diagnosis Code #1, Plan Code #1| Plan Code Description, Phy. Zip, Physician ID) if there is a way to separate them like that.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: columns to rows (xp)

    Hans' code will only work for 10 rows in your data set since you have 25 columns of data. Your sample data does not look like your dataset!

    It will also yield problems since there are some blanks in the rows, so some of the end-to rights will not actually get to the end of the data.

    I don't have time tonite to work on a modified code (to just extract the appropriate columns) so I won't get a chance until tomorrow morning or afternoon.

    1 suggestion if you need it done now: make a copy of the worksheet, delete the columns that you do not need transformed (B-J, Q-S, U-W), add a space into any blank cells in this dataset and then Hans' code should run fine. you will have to delete every 8th column to get rid of the dup IDs but that should not take too long manually.

    I will try to work up the code tomorrow to allow routinely getting what you need or perhaps Hans (or someone else) will have time tonite.

    Steve

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: columns to rows (xp)

    I have attached your workbook with a modified macro. It follows the rules you set out, and is therefore much less flexible than the original one. The code will need to be adapted for any change in the layour of the worksheet and any change in the columns you want to move.

    Sub Transform()
    'Row index
    Dim lngRow As Long
    'Number of rows
    Dim lngMaxRow As Long
    'Row we're currently filling from
    Dim lngCurRow As Long
    'Row we're currently filling to
    Dim lngFillRow As Long
    'Column we're currently pasting to
    Dim lngFillCol As Long

    'Initialize
    lngMaxRow = Range("A1").CurrentRegion.Rows.Count
    lngCurRow = 1
    lngFillRow = 1

    'Loop through rows
    For lngRow = 2 To lngMaxRow
    If Cells(lngRow, 1) = Cells(lngFillRow, 1) Then
    'Copy K-P
    Range(Cells(lngRow, 11), Cells(lngRow, 16)).Copy Cells(lngFillRow, lngFillCol)
    'Copy T
    Cells(lngRow, 20).Copy Cells(lngFillRow, lngFillCol + 6)
    'Copy X
    Cells(lngRow, 24).Copy Cells(lngFillRow, lngFillCol + 7)
    ' Increase lngFillCol for next row
    lngFillCol = lngFillCol + 8
    'Check if there is room
    If lngFillCol > 249 Then
    MsgBox "Too many data to fit in 256 columns!"
    Exit Sub
    End If
    Else
    'New Acct_Num
    lngCurRow = lngRow
    lngFillRow = lngFillRow + 1
    lngFillCol = 25
    'Copy first row of Acct_Num
    Range(Cells(lngRow, 1), Cells(lngRow, 24)).Copy Cells(lngFillRow, 1)
    End If
    Next lngRow

    ' Remove superfluous rows
    Rows(lngFillRow + 1 & ":" & lngMaxRow).Delete
    End Sub

  12. #12
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: columns to rows (xp)

    Thanks so much, Hans and Steve! I didn't even ask before because I didn't realize it could be done. I'll run it first thing tomorrow.

    Laura

Posting Permissions

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