Assign values to items in a list

[rk1234]

Hi all,
I receive an excel file daily with a list of jobs to be carried out. People are assigned to carry out these jobs and each person will have multiple jobs assigned to them. The jobs also have GPS coordinates and I convert the file to a .csv file to upload into a mapping app on our work phones.

I wish to insert a new column with a formula/function which will assign a value (different colours of marker pins on the map) from a list to each person.

The people change on a regular basis, so I would like (if possible) to have excel assign these values on a first-come-first-served basis. i.e. Excel will look at the 1st name on the list and assign the 1st value from the list to it and every subsequent occurrence of that name. It then assigns the 2nd value from the list to the 2nd unique name and every subsequent occurrence of that name. etc etc.

It's not important that the same person has the same colour of marker pin each day. Only that we can see clusters of marker pins of the same colour so we can see where a particular person's jobs are.

I want to go from something like this:

	A	B	C	D	E	F	G
1	Job No.	Assigned Staff					List of Markers
2	1245	Mark					Red Pin
3	5321	John					Blue Pin
4	4312	Mark					Green Pin
5	4367	Dave					Purple Pin
6	7532	James					Yellow Pin
7	3467	Dave					Orange Pin
8							Cyan Pin

To this (text colour for illustration only):

	A	B	C	D	E	F	G
1	Job No.	Assigned Staff	Assigned Marker Type				List of Markers
2	1245	Mark	Red Pin				Red Pin
3	5321	John	Blue Pin				Blue Pin
4	4312	Mark	Red Pin				Green Pin
4	4367	Dave	Green Pin				Purple Pin
5	7532	James	Purple Pin				Yellow Pin
6	3467	Dave	Green Pin				Orange Pin
7							Cyan Pin

However, in tomorrow's file James might be listed 1st and be assigned the Red Pin, John listed 2nd and be assigned the Blue Pin etc etc.

Thanks for reading, any solutions/pointers much appreciated.

[MickG]

This is one way !!!

Please Login or Register  to view this content.

Regards Mick