none
Converting a column of data into more columns RRS feed

  • Question

  • I have a list of medications in column A, fill dates in column B, and respective prescribing doctors in columns C through E (I plan to concatenate these three fields - First Name Last Name, Degree). What I am trying to do is set the file up so it can auto fill into a PDF document for a customer with each of their medications listed, last date filled, and the prescribing doctor in list form.  But in order to do that, I have to convert the Excel file so each medication, date, and doctor is in their own columns, so I can change the field name ( i.e., Medication1 (col A), Date1 (col B), Dr1 (col C); Medication2 (col D), Date2 (col E), Dr2 (col F);..., etc.)

    The list could be as few as one and as many as 20 for any given customer.

    It would be nice if I could find a way to automate the conversion, too.

    Any ideas on how to do that?

    Any help is most appreciated!

    Wednesday, October 12, 2016 7:57 PM

Answers

All replies

  • Can you share a file so that we could see if any solution is there. In one sheet show how it now looks, and in other sheet show how want the result .

    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Thursday, October 13, 2016 2:59 AM
    Answerer
  • >>>But in order to do that, I have to convert the Excel file so each medication, date, and doctor is in their own columns, so I can change the field name ( i.e., Medication1 (col A), Date1 (col B), Dr1 (col C); Medication2 (col D), Date2 (col E), Dr2 (col F);..., etc.)

    According to your description, you could refer to codes below:

    Sub Demo()
    
      Dim targetRng As range
      Dim colNum As Integer
      Dim startNum As Integer
      
      
      Set targetRng = range("A1:B3")
      colNum = targetRng.Columns.Count
      
      For m = 2 To targetRng.Rows.Count
      
        Set curRow = targetRng.Rows(m)
        startNum = startNum + colNum
        For n = 1 To curRow.Cells.Count
        
            ActiveSheet.Cells(1, startNum + n).Value = curRow.Cells(n).Value
            
        Next n
        
      Next m
      
    End Sub

    The result:

    Thanks for your understanding.

    Thursday, October 13, 2016 5:14 AM
  • This is what the information looks like when I start.  And I may have more than one person's identification number.  I could have a list of 30-100 and each person can have anywhere from 1 to 30 or more medications listed for them.  (I apologize that the pharmacy column isn't in the first graphic, but it should be right between columns C and D.)

    But this is how I want it converted to:

    

    A coworker of mine had created this Excel file with VBA language in it and would run by a button.  The VBA would fetch a file with a specific file name, do all the formatting and conversions, then save the file with all the conversions.  All I had to do was open the Excel document, press the button and it would do all this work for me and then I could go back and open the document to see it had all be converted. It would just mean the specific file would be updated with new data before I ran the Excel file each time.  This would be the ideal situation.

    I really hope someone can help me.

    Thank you.


    Renee


    • Edited by JRenee97045 Thursday, October 13, 2016 11:34 PM
    Thursday, October 13, 2016 11:33 PM
  • Hi JRenee1,

    According to your description, you could refer to below code, then modify with your requirement:

    Sub Demo()
    
       Dim targetRng As Range
       Dim colNum As Integer
       Dim startNum As Integer
       
       Dim numberID As String
       Dim firstName As String
       Dim lastName As String
      
       Set targetRng = Worksheets("Sheet1").Range("A1:D6")
       startNum = targetRng.Columns.Count - 1
      
       numberID = targetRng.Cells(2, 1)
       firstName = targetRng.Cells(2, 2)
       lastName = targetRng.Cells(2, 3)
       
       Worksheets("Sheet2").Cells(1, 1).Value = targetRng.Cells(1, 1)
       Worksheets("Sheet2").Cells(1, 2).Value = targetRng.Cells(1, 2)
       Worksheets("Sheet2").Cells(1, 3).Value = targetRng.Cells(1, 3)
    
       Dim curRowNum As Integer
       curRowNum = 2
    
       For m = 2 To targetRng.Rows.Count
       
        Set curRow = targetRng.Rows(m)
        
        If numberID = curRow.Cells(1) And firstName = curRow.Cells(2) And lastName = curRow.Cells(3) Then
          n = n + 1
        Else
          curRowNum = curRowNum + 1
          numberID = curRow.Cells(1)
          firstName = curRow.Cells(2)
          lastName = curRow.Cells(3)
          n = 1
        End If
        Worksheets("Sheet2").Cells(1, startNum + n).Value = "Med" & n
        Worksheets("Sheet2").Cells(curRowNum, 1).Value = curRow.Cells(1).Value
        Worksheets("Sheet2").Cells(curRowNum, 2).Value = curRow.Cells(2).Value
        Worksheets("Sheet2").Cells(curRowNum, 3).Value = curRow.Cells(3).Value
        Worksheets("Sheet2").Cells(curRowNum, startNum + n).Value = curRow.Cells(4).Value
      Next m
      
    End Sub

    The result:

    Thanks for your understanding.

    Friday, October 14, 2016 8:04 AM
  • I will try this and get back to you.

    Thank you!


    Renee

    Monday, October 17, 2016 6:15 PM
  • For the most part, this worked well.  I appreciate the time you took to write the code because I don't know VBA.  There are just a few questions.  Sometimes there may be as many as 200 persons on the list and I noticed the range for Sheet 1 was specific to the three as in the examples I provided.

    In addition, there is the clinic name that does not show in my first graphic that goes between the member's last name and the medications column.  The clinic is always the same for each medication for the member.  The question is how do I get that in there without messing up the VBA?  It would be the number ID, First name, last name, clinic, then the medications.

    Thank you.


    Renee

    Monday, October 17, 2016 8:02 PM
  • Hi JRenee1,

    According to your description, you could modify your code like below:

    Sub Demo()
    
       Dim targetRng As Range
       Dim colNum As Integer
       Dim startNum As Integer
       
       Dim numberID As String
       Dim firstName As String
       Dim lastName As String
      
       Set targetRng = Worksheets("Sheet1").UsedRange
       startNum = targetRng.Columns.Count - 1
      
       numberID = targetRng.Cells(2, 1)
       firstName = targetRng.Cells(2, 2)
       lastName = targetRng.Cells(2, 3)
       
       Worksheets("Sheet2").Cells(1, 1).Value = targetRng.Cells(1, 1)
       Worksheets("Sheet2").Cells(1, 2).Value = targetRng.Cells(1, 2)
       Worksheets("Sheet2").Cells(1, 3).Value = targetRng.Cells(1, 3)
       Worksheets("Sheet2").Cells(1, 4).Value = targetRng.Cells(1, 4)
    
       Dim curRowNum As Integer
       curRowNum = 2
    
       For m = 2 To targetRng.Rows.Count
       
        Set curRow = targetRng.Rows(m)
        
        If numberID = curRow.Cells(1) And firstName = curRow.Cells(2) And lastName = curRow.Cells(3) Then
          n = n + 1
        Else
          curRowNum = curRowNum + 1
          numberID = curRow.Cells(1)
          firstName = curRow.Cells(2)
          lastName = curRow.Cells(3)
          n = 1
        End If
        Worksheets("Sheet2").Cells(1, startNum + n).Value = "Med" & n
        Worksheets("Sheet2").Cells(curRowNum, 1).Value = curRow.Cells(1).Value
        Worksheets("Sheet2").Cells(curRowNum, 2).Value = curRow.Cells(2).Value
        Worksheets("Sheet2").Cells(curRowNum, 3).Value = curRow.Cells(3).Value
        Worksheets("Sheet2").Cells(curRowNum, 4).Value = curRow.Cells(4).Value
        Worksheets("Sheet2").Cells(curRowNum, startNum + n).Value = curRow.Cells(5).Value
      Next m
      
    End Sub

    The result:

    In addition you could start Excel VBA with Excel VBA reference

    Thanks for your understanding.

    Tuesday, October 18, 2016 2:04 AM
  • You are AWESOME!  Thank you for your help!  This worked perfectly!

    Renee


    Renee

    • Marked as answer by JRenee97045 Tuesday, October 18, 2016 3:53 PM
    Tuesday, October 18, 2016 3:53 PM