Merge lots of info into one cell!

Howdy!

I have a workbook with about 1500 rows of data. Each row has 7 columns.

The columns that have the info that I require are the 1st and 4th.

In the 1st if a manager's name

In the 4th is the departments that answer to him.

Every manager has at least 1 department, some have as many as 60.

Using the "Countif" function I can already tell how many departments each manager has. (When using a validation option, a Countif tells me the number when a specific maanger is selected.)

Now, What I need is this. In the 4th column under each manager is his department numbers, I need to merge all of this info into one cell. Using CONCATENATE would work but is very messy. I would have to identify each cell that is ajacent to a manager's name, place it into the CONCATENATE formula and insert a "," between each. As this must be done on the fly when a manager is selected.

Heres a example of the file:

So When you select a manager in the G3 field. In the G5 field, I want all of his departments to show seperated by ",".

EG. If Manager1 Selected in G3, In G5 I want to see DEP 1, DEP 2, DEP 3, DEP 4, DEP 5, DEP 6.

EG2. If Manager8 Selected in G3, In G5 I want to see DEP 35, DEP 36, DEP 37, DEP 38.

Now if this was the actual file, it would be easy, but the actual one has as mentionned above, over 1500 row and growing and each manager has many, many departments (Both French and English) and the list grows as they find more. But this small file will do the job for now!

Thanks a bunch in advance!

D.

I cant us that!

Once I have identified all of the manager's departments, I then go to another workbook with more than 20,000 rows where this information (From the first workbook) will be used to autocreate reports, setup an email and send it off to the manager.

The email format will follow this patern.

Dear (Manager)

Your first department spent (This much) on:
First job ($)
Second job ($)
Third job($)

Your second department spent (This much) on:
...

and so on!

D.

As per your solution, this can only be done by a macro. please find the attached solution.

Note the validation has been replace by a control to do the same thing. you have to enable macros to make it run

It is what you asked for, but is it what you really require, does it work?

Regards

Dav

Great help! Thank you! This will work fine!

D.