Vlookup Formula

[Petefav]

Hi All,

Thanks everyone for the help in advance.

I was trying to figure out a formula that will pick up a new email format when entered into excel.

Basically column D has a list of e-mail address that have traded with us. The addresses have been listed more than once for those that have traded multiple times. I copied this column into AO and removed duplicates to leave me with the number of clients we have based on their email address. I would like a formula that recognises any new email address that has traded (column D) and duplicate that email address to column AO. Is this possible?

Please see e-mail formats listed below, I'm guesisng as more than one person share an email format the formula will need "*@" somewhere.
Column D
peer@stc.com.au
peter.green@DIY.com.au
sophie@llc.com.au
andy.shaw@DIY.com.au
mike@pagin.com
sal@pagin.com

[Max, Singapore]

Here's one simple way to extract a uniques list dynamically (on-the-fly updating) ...
Assume your source data (eg email addresses) runs in A2 down
Put in B2: =IF(A2="","",IF(COUNTIF(A$2:A2,A2)>1,"",ROW()))
Put in C2: =IF(ROWS($1:1)>COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROWS($1:1))))
Copy B2:C2 down as far as data is expected in col A, eg down to row 200?
Hide away col B. Col C will return a uniques listing of the items in col A, all neatly packed at the top. This uniques list will auto-update as new data is input (or pasted) into col A
------------------------------------------------
Success? Wave it, click the little star at the bottom left of my response

[ramananhrm]

Please paste this function with Ctrl+Shift+Enter in B2 & copy across to generate unique email ids without the person name.


=IFERROR(INDEX(SUBSTITUTE(RIGHT(SUBSTITUTE($A$2:$A$7,"@",REPT("!",LEN($A$2:$A$7))),LEN($A$2:$A$7)),"!",""),SMALL(IF(FREQUENCY(IF(SUBSTITUTE(RIGHT(SUBSTITUTE($A$2:$A$7,"@",REPT("!",LEN($A$2:$A$7))),LEN($A$2:$A$7)),"!","")<>"",MATCH(SUBSTITUTE(RIGHT(SUBSTITUTE($A$2:$A$7,"@",REPT("!",LEN($A$2:$A$7))),LEN($A$2:$A$7)),"!",""),SUBSTITUTE(RIGHT(SUBSTITUTE($A$2:$A$7,"@",REPT("!",LEN($A$2:$A$7))),LEN($A$2:$A$7)),"!",""),0)),ROW($A$2:$A$7)-ROW($A$2)+1),ROW($A$2:$A$7)-ROW($A$2)+1),ROWS($C$2:C2))),"")

And to count each email, use this function
Paste the function in C2
=SUMPRODUCT(1*(ISNUMBER(SEARCH(B2,$A$2:$A$7))))