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.

 

[My Aswer Is This]

We would need more information.

Like where on the sheet is this data and what information you want copied and copied to where.

We need column numbers like A or B

Do not say column "Date" and "Address.

And we need sheet names

 

[Rick Rothstein]

I am guessing that your data groups are comma delimited and the group consists of the category name, a dash, and the data for that category. Do you have a sheet with headers corresponding to the category names already in place to which the individual group data is to be copied to? If so, what is the name of sheet and are the headers spelled exactly the same as the category names in the text that is pasted into the cell? Or are your headers in Columns B onward (where I am assuming you paste your data into the cells of Column A)?

 

[Viper20184]

So they start on a new spreadsheet daily.
If we do it where they can copy and paste the information from the ticket, it would be into the 2nd row, first column (so would start in A2)
The name would go in the following:

Name - Column A
User ID - Column B
Employee ID - Column i
Email - Column D
Job Title - Column L
Job Code - Column F
Dept Name - Column H
Dept ID - Column G
Requester Comments - Column M

The rest of the info would not be needed.
The sheet name would be Import

 

[Viper20184]

I am guessing that your data groups are comma delimited and the group consists of the category name, a dash, and the data for that category. Do you have a sheet with headers corresponding to the category names already in place to which the individual group data is to be copied to? If so, what is the name of sheet and are the headers spelled exactly the same as the category names in the text that is pasted into the cell? Or are your headers in Columns B onward (where I am assuming you paste your data into the cells of Column A)?

Correct, the information comes from our Access management system.. it comes with each section delimited by a comma (except the name and manager name also have a comma in them since they are formatted lastname, firstname).
Yes they do have a sheet they work from daily (cleaned each day). The name of the sheet is Import and they have headers in there in the first row, so they start entering information in row 2. We can change up the spreadsheet if we need a blank cell to paste in the information from our access management system in order for the macro to work off of.

 

[Rick Rothstein]

Name - Column A
User ID - Column B
Employee ID - Column i
Email - Column D
Job Title - Column L
Job Code - Column F
Dept Name - Column H
Dept ID - Column G
Requester Comments - Column M

Are these the exact header names on the Import sheet (we need to know because they differ slightly from the data being pasted into Excel by the user)? If not, we need the exact text (even down to the letter casing) for each header cell.

 

[Viper20184]

Are these the exact header names on the Import sheet (we need to know because they differ slightly from the data being pasted into Excel by the user)? If not, we need the exact text (even down to the letter casing) for each header cell.

Name - Column A - name on spreadsheet - UserNameOT
User ID - Column B - name on spreadsheet - SystemLogin
Employee ID - Column i - Name on Spreadsheet - MPI:ID
Email - Column D - Name on Spreadsheet - EmailAddress
Job Title - Column L - Name on Spreadsheet - Title
Job Code - Column F - Name on Spreadsheet - Job Code
Dept Name - Column H - Name on Spreadsheet - Dept Desc
Dept ID - Column G - Name on Spreadsheet - Dept ID
Requester Comments - Column M - Name on Spreadsheet - Specialnotes

Name on spreadsheet is what the header row is actually listed as.. all of those are in Row 1.

 

[My Aswer Is This]

Glad you provided these details. I'm sure Rick will be able to help you.

 

[Rick Rothstein]

...(except the name and manager name also have a comma in them...

Neither the data nor headers you have posted show a manager's name. Is that something that would be listed in the data that should be ignored because it does not have a corresponding header that you are interested in?

 

[Viper20184]

Correct. Its information that is automatically generated but not information we use in applications. The only information we use are the ones I listed before.

This is what we get and want to be able to automatically pull the parts we do use with a macro so make analyst life a little easier:
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

If we could have the information we dont need removed, we would but we lost that fight a long time ago.