Pick out items in repeated list

[Charles C.]

Hello guys/gals/all,

I've a list which I put in column A.

This is a list with say, 200, cells and they consists of usually 5 or 6 items repeated 200 times in total. For my usage, I need to identify which are the 5 or 6 and list them seperately. I do not wish to use macro.

Can anyone tell me how to achieve this using perhaps worksheet functions?

 

[Peo Sjoblom]

One way

assume you have the items in A2:A201

in let's say C2 put

=INDEX($A$2:$A$201,MATCH(0,COUNTIF($C$1:C1,$A$2:$A$201),0))

enter it with ctrl + shift & enter

copy down until you get an error

not that you can't put the formula in the first row since it refers to the
cell above
so if you put it in H4 it would look like

=INDEX($A$2:$A$201,MATCH(0,COUNTIF($H$3:H3,$A$2:$A$201),0))

that will give you a list with the unique values, you could create it easier
by using the advanced filter

data>filter>advanced filter, copy to another location and unique records
only

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

Hello guys/gals/all,

I've a list which I put in column A.

This is a list with say, 200, cells and they consists of usually 5 or 6

items repeated 200 times in total. For my usage, I need to identify which
are the 5 or 6 and list them seperately. I do not wish to use macro.

 

[Ashish Mathur]

Hi,

Assuming that your list is in C3:C9. In cell E3, copy the cell contents of C3 directly. In E4, enter the following formula

IF(COUNTIF($E$3:E3,C4)=1,"",C4)

Caopy the formula to cell E9

Regards,

Ashish Mathur