Split Multiple items into different columns

[Viper20184]

Hi,

So I am wanting to help make some of our team's work a little easier but have no idea how to go about it.

We have this spreadsheet that is filled in with information needed by various teams to create accounts. These teams fill in the information manually from a ticket that gets automatically generated.

What I am wanting to do is make some kind of macro in Excel that either works on the information that is pasted in a cell.. or when you click on the macro, it brings up a pop up and they can copy/paste the information from the ticket.

The information comes in the following format:

Comments: Full Name-Lname, Firstname, UserID-Username, EmployeeID-EmployeeNumber , Email-EmailAddress, Job Title-Sometitle, DAU-some numbers , Job Code-123456, Department-Dept Name, DeptID-DeptNumber, Manager-Manager LName, Manager FName , Access Request ID: ReqNumber, Requester Comments- Comments from submitted , Approver Comments - Comments from approver


This information is in every ticket that is generated but all of the information is not used. Currently, our people have to manually copy and paste parts like full name, user ID, employee ID, Email, Job Title, Job Code, Dept Name, Dept ID and Requester Comments While it doesnt take long to do one or two, these teams usually do 50+ a day. Would it be possible to do what I am asking? I would take any help at this point.

 

[Rick Rothstein]

See if this macro does what you want. Note that you do not have to process your data one at a time... you can copy each set of data into its own cell starting at cell A2, putting the next one in cell A3, then the next in cell A4 and so on. Once all of the data has been copy/pasted into the cells in Column A (starting at Row 2), then run the macro. Let us know if the code works correctly for you or not.

[table="width: 500"]
[tr]
	[td]Sub DistributeTeamInformation()
  Dim R As Long, X As Long, Col As Long, NextOutRow As Long
  Dim Data As Variant, Categories() As String, Parts() As String
  Data = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row + 1)
  Categories = Split("Full Name-UserNameOT,UserID-SystemLogin,EmployeeID-MPI:ID,Email-EmailAddress,Job Title-Title,Job Code-Job Code,Department-Dept Desc,DeptID-Dept ID,Requester Comments-Specialnotes", ",")
  With Sheets("Import")
    For R = 1 To UBound(Data) - 1
      NextOutRow = .Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row + 1
      Parts = Split(Data(R, 1), ",")
      Parts(0) = Replace(Parts(0), "Comments: ", "", , , vbTextCompare)
      For X = 0 To UBound(Parts)
        If InStr(Parts(X), "-") Then
          Select Case Split(Parts(X), "-")(0)
            Case "Full Name"
              Col = .Rows(1).Find("UserNameOT", , xlValues, xlWhole, , , False, , False).Column
              .Cells(NextOutRow, Col) = Split(Parts(X), "-")(1) & "," & Parts(X + 1)
            Case Else
              On Error Resume Next
              Col = .Rows(1).Find(Split(Filter(Categories, Trim(Split(Parts(X), "-")(0)), , vbTextCompare)(0), "-")(1), , xlValues, xlWhole, , , False, , False).Column
              If Err.Number = 0 Then .Cells(NextOutRow, Col) = Split(Parts(X), "-")(1)
              On Error GoTo 0
          End Select
        End If
      Next
    Next
  End With
End Sub[/td]
[/tr]
[/table]

 

[Viper20184]

I just did a test run with it and it looks like its doing exactly what we need!
I will pass this along to the teams tomorrow and see if they run into any other issues, but I think they will all be loving this! Thank you very much!

 

[Viper20184]

Hi Rick. So the macro has been working like a charm. I was requested to make a change or two to it.
The first thing was to pull in the Manager into the Supervisor field on their spreadsheet. I tried to update the code on my own but could only get so far. I have a feeling its part of the Select Case part of the code. I added Manager-Supervisor into the Categories part and that partially added the manager name, but only did one part of the name. I tried to copy the case, add another, add in other parts but it either did nothing or affected the UserNameOT instead. So I humbly come to ask for your assistance once more in helping me understand how the code works.

The other thing they would like to have but not super important if it cant be done easily is.. they would like to track the ticket# for each of these requests came from. What they would like to do is say paste the information into A1 and then the ticket# into A2 and have it pull in when the macro is run. They would like it to have it in the same cell as the Requester Comments-Special Notes field but if thats not possible, move into something like Q column

 

[Rick Rothstein]

Hi Rick. So the macro has been working like a charm. I was requested to make a change or two to it.
The first thing was to pull in the Manager into the Supervisor field on their spreadsheet. I tried to update the code on my own but could only get so far. I have a feeling its part of the Select Case part of the code. I added Manager-Supervisor into the Categories part and that partially added the manager name, but only did one part of the name. I tried to copy the case, add another, add in other parts but it either did nothing or affected the UserNameOT instead. So I humbly come to ask for your assistance once more in helping me understand how the code works.

The other thing they would like to have but not super important if it cant be done easily is.. they would like to track the ticket# for each of these requests came from. What they would like to do is say paste the information into A1 and then the ticket# into A2 and have it pull in when the macro is run. They would like it to have it in the same cell as the Requester Comments-Special Notes field but if thats not possible, move into something like Q column

Tell me the name in the text on the data worksheet and the header text it maps to on the Import sheet for each of these. I can probably guess the first (but tell me anyway), but for the second one, I don't see what would be a "ticket number" in the text example you posted originally.

 

[Viper20184]

The field that comes in from their info is "Manager".. the field it will move into on the Import Sheet is "Supervisor". This is the 2nd field that has a person's name in LastName,FirstName format.

The ticket number itself is not included in the info that was originally requested. It would be separate. What they want to be able to do is enter the ticket number in the cell next to where they paste the info from the ticket (so A2) and have it move over with all the other info. They would like for it to be added in with the info with the requestor comments / Specialnotes on the import but if that is not possible, I can add a field on Import named "TicketNumber"

 

[Rick Rothstein]

The field that comes in from their info is "Manager".. the field it will move into on the Import Sheet is "Supervisor". This is the 2nd field that has a person's name in LastName,FirstName format.

The ticket number itself is not included in the info that was originally requested. It would be separate. What they want to be able to do is enter the ticket number in the cell next to where they paste the info from the ticket (so A2) and have it move over with all the other info. They would like for it to be added in with the info with the requestor comments / Specialnotes on the import but if that is not possible, I can add a field on Import named "TicketNumber"

Does this revised macro do what you want...

[table="width: 500"]
[tr]
	[td]Sub DistributeTeamInformation()
  Dim R As Long, X As Long, Col As Long, NextOutRow As Long
  Dim Data As Variant, Categories() As String, Parts() As String
  Data = Range("A2:B" & Cells(Rows.Count, "A").End(xlUp).Offset(, 1).Row + 1)
  Categories = Split("Full Name-UserNameOT,UserID-SystemLogin,EmployeeID-MPI:ID,Email-EmailAddress,Job Title-Title,Job Code-Job Code,Department-Dept Desc,DeptID-Dept ID,Requester Comments-Specialnotes,Manager-Supervisor", ",")
  With Sheets("Import")
    For R = 1 To UBound(Data) - 1
      NextOutRow = .Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row + 1
      Parts = Split(Data(R, 1), ",")
      Parts(0) = Replace(Parts(0), "Comments: ", "", , , vbTextCompare)
      For X = 0 To UBound(Parts)
        If InStr(Parts(X), "-") Then
          Select Case Trim(Split(Parts(X), "-")(0))
            Case "Full Name"
              Col = .Rows(1).Find("UserNameOT", , xlValues, xlWhole, , , False, , False).Column
              .Cells(NextOutRow, Col) = Split(Parts(X), "-")(1) & "," & Parts(X + 1)
            Case "Manager"
              Col = .Rows(1).Find("Supervisor", , xlValues, xlWhole, , , False, , False).Column
              .Cells(NextOutRow, Col) = Split(Parts(X), "-")(1) & "," & Parts(X + 1)
            Case "Requester Comments"
              Col = .Rows(1).Find("Specialnotes", , xlValues, xlWhole, , , False, , False).Column
              .Cells(NextOutRow, Col) = Trim(Split(Parts(X), "-")(1)) & " (Ticket Number: " & Data(R, 2) & ")"
            Case Else
              On Error Resume Next
              Col = .Rows(1).Find(Split(Filter(Categories, Trim(Split(Parts(X), "-")(0)), , vbTextCompare)(0), "-")(1), , xlValues, xlWhole, , , False, , False).Column
              If Err.Number = 0 Then .Cells(NextOutRow, Col) = Split(Parts(X), "-")(1)
              On Error GoTo 0
          End Select
        End If
      Next
    Next
  End With
End Sub[/td]
[/tr]
[/table]

 

[Viper20184]

That looks like exactly what they are looking for. Thank you very much again!