Copying a number from a cell & pasting it into a 2nd cell, based on a 3rd cell's criteria

I have a column P: which either has a number in it or it doenst. If it does have a number in it, I want to paste that number(s) into another table on the same sheet, in column Z, next to the people that were resonsible for having that problem job.

For example, if Joey and Steve are on a job that has a problem and the number for that job was 45789. I want "45789" to be posted in Joey's cell in the Z column, and "45789" posted in Steve's cell in column Z. I need this done for every job that has a problem number associated with it. As you can see, sometimes each employee is on a job by them self, that is why their name is is colum Q and R at the same time. If they are by them self and have a problem job, I want that number posted by their name in column Z only once.

I cannot figure this out, Ive tried index, vlookup, and a bunch of other stuff. I have excel 2010, if you need anything else let me know.
I dont nescessarily need commas between each problem job number in column Z next to each persons name, but if I could have some way of differientating each problem job number with somthing, even if its a return, that would be awesome.

Thank you, and have a great day

For example Steve has several problem jobs. would problem number 1 go in Z26 and problem number 2 go in AA26 and so on for Steve?

Originally Posted by playaller

For example Steve has several problem jobs. would problem number 1 go in Z26 and problem number 2 go in AA26 and so on for Steve?

Thanks for the prompt reply…
No, all of Steves problem jobs need to be pasted in the same cell Z26
All of Johns go in z27
All of Joeys go in z28
Etc.
That is why I posted this originally
"I dont nescessarily need commas between each problem job number in column Z next to each persons name, but if I could have some way of differientating each problem job number with somthing, even if its a return, that would be awesome"
But if there is no way of isolating them I could put commas after the end of each problem job number in column P… maybe that would help

Thanks for looking in to this for me.

Got cha. Run the sub below:

Please Login or Register  to view this content.

I also forgot to tell you that some of the agent names have spaces behind them and the code will not count them. You need to remove all spaces from each agents cell. You can Ctrl+F find and replace "agent " with "agent" for each of the agents.

Also if you didn't know, instead of running a manual sub every time you can fully automate this process. I have placed an example sheet that will auto update as you change the data within the sheet. If you change the job #'s they will update. If you add or change names they will auto update.

Example attached: I've updated the attachment and corrected the names so they should be fine.

The problem with a normal formula would be getting all of the values in the same cell which would involve some concatenating, vlookups/matches and multiple if statements. It's honestly easier to create a vba which is easier to read, understand, learn and manipulate with little effort. Thank you and to help further here is the code labeled so you know whats going on with it:

Please Login or Register  to view this content.

Cool, got another question
Would it be smart to install a drop down list for each cell in Q & R that has each employees name in it. I will be handing this spreadsheet off eventually and cant garuntee that person will spell each employees name correctly or that they wont accidentally include a space behind each employees name.

Yes I would definitely use to prevent any chance of mismatching cells and would probably be easier to add agent to jobs:

Cell Q1 > Data Validation > List > Range("V26:V30")
Copy and paste to each cell in Q and R.
Update Select list names

I would also probably do the same for the Job numbers to make it easier for someone to pull from a list if you're handing off the sheet but i'm sure you're go according to your business structure there.

Just let me know if you need anything else.