extract unique records from one column

[Guest]

I had 6,000 email ids in one column.
I would like to have separate column for each unique id for ex. yahoo,
hotmail etc. From Find and Replace I gave Yahoo and choose Find All options.
In results I find all yahoo entries alone.
How can I transfer those yahoo entries alone and insert in the excel sheet
as separate column?

 

[Domenic]

Assuming that A4:A100 contains your data, try the following...

Let C2 contain Hotmail

Let D2 contain Yahoo

C3, copied across to D3:

=COUNTIF($A$4:$A$100,"*"&C2&"*")

C4, copied down and across:

=IF(ROWS(C$4:C4)<=C$3,INDEX($A$4:$A$100,SMALL(IF(ISNUMBER(SEARCH(C$2,$A$4
:$A$100)),ROW($A$4:$A$100)-ROW($A$4)+1),ROWS(C$4:C4))),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Mahendra raj, Coimbatore

 

[vezerid]

I had 6,000 email ids in one column.
I would like to have separate column for each unique id for ex. yahoo,
hotmail etc. From Find and Replace I gave Yahoo and choose Find All options.
In results I find all yahoo entries alone.
How can I transfer those yahoo entries alone and insert in the excel sheet
as separate column?

Assuming your emails are in A2:A6000, you can place the following
formula in, say, F2:

=INDEX($A$2:$A$6000,SMALL(IF(ISNUMBER(SEARCH("@yahoo.",$A$2:$A
$6000)),ROW($A$2:$A$6000)-ROW($D$2)+1)))

This is an *array* formula, commit with Shift+Ctrl+ENter. Copy down
until you get error values. If needed, you can then copy the new
column and Edit|Paste Special...>Values

HTH
Kostis Vezerides