Add names to a cell if new in the column

[DenisAM]

Hi,

I am stuck on this since yesterday... sorry if you find this easy, I am a beginner

I want to add email addresses to a list in a cell to be able to copy past this addresses directly into outlook.
The problem I have is that I want to add a name only once if it shows up multiple times.
The other problem is that it has to show into 4 different columns. (this is more or less working).

Example: (/ is a column divider)
[email protected] / al / [email protected] / bob / [email protected] / cal / [email protected] / ralph /
[email protected] / norbert / - / - / - / - / - / 0 /
[email protected] / al / [email protected] / bob / - / #N/A / - / - /
[email protected] / norbert / - / - / 0 / - / - / - /

Result needed (in one cell): [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]

What I did so far:

Please Login or Register  to view this content.

Thank you very much in advance if somebody can help me!

[patel45]

add please a sample file

[DenisAM]

Unfortunately, I am restricted for excel files uploads... :-(((
I'll try a picture... ;-)

---------- Post added at 08:39 AM ---------- Previous post was at 08:31 AM ----------

It doesn't work neither.... :-((((((
Would you like that I explain a little bit more a special part?

---------- Post added at 08:50 AM ---------- Previous post was at 08:39 AM ----------

This is a list of teams with the name of team and various until column 8.
From column 9, I have the names of the team members.
In each team, there is a max of 4 candidates.
the names and emails from the candidates are in columns 9 to 15. Each time an email, then the name, an email, the name and so on.
There are various sub-contests where the candidates may register to but I only want to pick their email address one time.

---------- Post added at 09:11 AM ---------- Previous post was at 08:50 AM ----------

Herafter, my code without taking into account the problem of the reappearing emails:

Please Login or Register  to view this content.

[rkey]

I assumed your total rows can come from column A (where the teamnames are).
And i determine if it is an email adress by having a "@" and a "." in the value.
With it I created this. Can you try it?

Please Login or Register  to view this content.

[DenisAM]

I try it directly.
Thanks!

---------- Post added at 10:04 AM ---------- Previous post was at 09:54 AM ----------

Not sure to have done it correctly. I put the first part into my existing function and the second part at the end of the same module

But it doesn't show me the emails in one cell.

I would like to have all the emails in cell(1,9).
remark: my list starts at row 2

Is it not possible do add a simple a loop to what I already did? The computing time is not really an issue for me.

---------- Post added at 10:06 AM ---------- Previous post was at 10:04 AM ----------

I have to say that your code is really impressive. I don't understand anything... ;-(((
I have a lot of work to do to improve my vba skills...

---------- Post added at 10:35 AM ---------- Previous post was at 10:06 AM ----------

Sorry to not understand what you wrote but starting from my simple loops hereafter, is it possible to add something to my code to exclude repetitive email in my list?

Please Login or Register  to view this content.

[Cutter]

@ DenisAM

Welcome to the forum.

Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
Thanks.

[DenisAM]

Ok thanks!

For those who could be interested, I found the solution using one function in the reply of rkey. (thanks again!)

Hereafter, the code:

Please Login or Register  to view this content.

[rkey]

Hi DenisAM.

Good to see you have found your solution :D
From the initial question i understood your emailadresses where on columns 9 to 15 on multiple rows.
The macro I have posted is based on a dummy sheet, where 3/4 email adresses are printed to the Immediate Window, I've attached it to this message.

Regards,
Rkey

[Cutter]

@ DenisAM

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.