VBA Insert Rows Based on Cells with comma

[Stephen_IV]

I have data that has values seperated by a comma in Range("D:D"). I need a single row for that data. Please see below! Thanks in advance!

Book5 A B C D 1 ID Name Sch Cert 2 111 Mr X School A 100 3 111 Mr X School A 120, 141, 156 4 222 Mr Y School Z 98,111 5 333 Mr A School D 980 6 444 Mr T School X 111,115,116,118,110 7 555 Mr C School C 98 Sheet1

I need the data to look like this:

Book5 A B C D 1 ID Name Sch Cert 2 111 Mr X School A 100 3 111 Mr X School A 120 4 111 Mr X School A 141 5 111 Mr X School A 156 6 222 Mr Y School Z 98 7 222 Mr Y School Z 111 8 333 Mr A School D 980 9 444 Mr T School X 111 10 444 Mr T School X 115 11 444 Mr T School X 116 12 444 Mr T School X 118 13 444 Mr T School X 110 14 555 Mr C School C 98 Sheet2

 

[Stephen_IV]

It is pulling down as a csv . Each code is made up of 3 digits. For instance, 12,13 really should be 012,013 or 1,068,107 should be 001,068,107

Sub SplitProblem()
Dim cell As Range
Dim i As Long
Columns("K:Z").NumberFormat = "@"
Dim r As Range
Set r = Range("J2", Cells(Rows.Count, "J").End(xlUp))
    For Each cell In r
    i = 0
        For Each x In Split(cell.Text, ",")
        i = i + 1
            cell.Offset(0, i) = Format(x, "000")
        Next
    Next
End Sub

 

[Grasor]

Stephen,

The issue is that if the original file doesn't have a delimiter a split won't work. Excel is looking at the value of 1213 and is adding a format mask to display 1,213. Without a delimiter to work with, we can't split reliably.

Does the original CSV contain true delimited values for your Endorsement Codes?

If it does, then we can reliably split each row by the delimiter into an array and then output the array onto a new sheet, etc.

Edit:

Can we assume that any number 3 digits and below is a single Endorsement code?

i.e. 7 would be 007, 95 would be 095, 1250 would be 001,250, etc. ? There aren't any single or double-digit codes?

Maybe we can just break apart each number by grabbing the last 3-digits at a time to create the individual codes?

 

[Joe4]

Can you open the CSV file in a Text editor (and NOT Excel) and copy and paste a section of the data, where we can see what these values look like in the native CSV file?
We need to see if that has text qualifiers, leading zeroes, and commas in the numbers.
If it does, we can do some stuff on the import that will make this a lot easier.

 

[Stephen_IV]

"EID","LastName","FirstName","ReportingDistrictName","SchoolName","TypeCode","CertificateType","EffDate","ExpDate","=""EndorsementCodes"""
"1112","aaa","xxx","yyy","zzz","011","Initial Educator","10/11/2021 12:00:00 AM","10/10/2024 12:00:00 AM","=""092"""
"1112","aaa","xxx","yyy","zzz","014","Professional Educator","8/1/2018 12:00:00 AM","7/31/2023 12:00:00 AM","=""023,101"""
"1113","aaa","xxx","yyy","zzz","014","Professional Educator","1/26/2022 12:00:00 AM","1/25/2027 12:00:00 AM","=""071"""
"1114",""aaa","xxx","yyy","zzz","011","Initial Educator","1/15/2020 12:00:00 AM","1/14/2023 12:00:00 AM","=""305"""
"1115","aaa","xxx","yyy","zzz","011","Initial Educator","1/21/2021 12:00:00 AM","1/20/2024 12:00:00 AM","=""015"""
"1116","aaa","xxx","yyy","zzz","013","Provisional Educator","5/10/2019 12:00:00 AM","5/9/2027 12:00:00 AM","=""042"""
"1117","aaa","xxx","yyy","zzz","011","Initial Educator","1/22/2021 12:00:00 AM","1/21/2024 12:00:00 AM","=""015"""

 

[Joe4]

So, if open up that CSV file directly in Excel, my column is imported much differently than you are showing on your file.
It looks like this:

So, the question is, how exactly are you opening this CSV in Excel?
And, are you doing any manipulation to column J after importing it?

 

[Grasor]

Here is a function that converts your numeric Endorsement Codes into a string data type with 3-digits delimited by a comma. It works until you get to very large numbers then Scientific Notation kicks in. Not sure how to get around that yet. This gets you part of the way there as the next step is to split the codes into an array and copy the other data in. Do you need help with that routine or have you done this before?

Put the function into a module then you can call it in the workbook syntax =ConvertEndorsementCode(value)

Function ConvertEndorsementCode(sValue As String) As String

'Function receives a numeric value from a single cell and then returns
'numbers stored as text with a comma delimiter for every three digits.
'All codes are 3 digits with leading zeros.


Dim iLen As Integer
Dim i As Integer
Dim sResult As String
Dim arrCodes() As String


'Determine number of 3-digit Codes
iLen = WorksheetFunction.RoundUp(Len(sValue) / 3, 0)

For i = 0 To iLen - 1
    ReDim Preserve arrCodes(i)
  
    'Debug.Print "In: " & sValue
  
    arrCodes(i) = Right(sValue, 3)

    On Error Resume Next 'If remaining value is 3 digits or less, then we are at the end and don't have to trim further.
    sValue = Left(sValue, Len(sValue) - 3)
  
    If Len(arrCodes(i)) < 3 Then
         'Add leading zeros
         Select Case Len(arrCodes(i))
       
            Case 2
                arrCodes(i) = "0" & arrCodes(i)
            Case 1
                arrCodes(i) = "00" & arrCodes(i)
      
        End Select
    End If
  
    'Debug.Print "Out: " & sValue
  
Next i

'Assemble Code String
For i = UBound(arrCodes) To LBound(arrCodes) Step -1
    sResult = sResult & arrCodes(i) & ","
Next i

'Debug.Print Left(sResult, Len(sResult) - 1) 'Remove trailing comma

ConvertEndorsementCode = Left(sResult, Len(sResult) - 1) 'Remove trailing comma and return value.

End Function