Hi,

I want to pick out unique components in a cell which has both unique and
common components. My data list is as follows.

514/406
514/326
7/571
7/310
48/570
514/311
48/305
7/21
600/571
..
..
As you see, each cell has tow components "first/second". I want to pick out
unique "first" and count cells which has the same "first". For example using
the above list, what I want is as follows.

unique count
514 3
7 3
48 2
600 1


what I did is pick up the unique "first" by my own observation, and then use
'countif' to count the numbers using a certain trick such as 514/*, 7/*...

thank you in advance.

First separate the values into two separate columns using:
Text to Columns

and then see Counting Distinct Entries In A Range in:

http://www.cpearson.com/excel/Duplicates.aspx
--
Gary''s Student - gsnu200801


"iksuinje" wrote:

Hi,

I want to pick out unique components in a cell which has both unique and
common components. My data list is as follows.

514/406
514/326
7/571
7/310
48/570
514/311
48/305
7/21
600/571
.
.
As you see, each cell has tow components "first/second". I want to pick out
unique "first" and count cells which has the same "first". For example using
the above list, what I want is as follows.

unique count
514 3
7 3
48 2
600 1


what I did is pick up the unique "first" by my own observation, and then use
'countif' to count the numbers using a certain trick such as 514/*, 7/*...

thank you in advance.

While this won't group the 'Uniques' next to each other (row by row), it
will only display the 'Uniques' and their count one time only. Give it a try
and see if you can use the results.

Assuming you have a header row so that your data starts in row 2, and
further assuming your data is in Column A and that you want the 'Uniques' in
Column B and their counts in Column C, do the following. Put these formulas
in the indicated cells...

B2: =LEFT(A2,FIND("/",A2)-1)
B3:
=IF(A3="","",IF(ISNUMBER(MATCH(LEFT(A3,FIND("/",A3)-1),$B$2:B2,0)),"",LEFT(A3,FIND("/",A3)-1)))

and copy B3 down as far as you like. Now put this formula in where
indicated...

C2: =IF(B2="","",COUNTIF(A$2:A$1000,LEFT(A2,FIND("/",A2)-1)&"*"))

and copy C2 down to the same row you copied B3 down to. In both cases, you
can copy the data down past the end of your data (to account for the
addition of future data). If your data goes past row 1000, or if you
anticipate it doing so in the future, change the 1000 inside the COUNTIF
function in C2 to the maximum row number you ever expect to have data in
(and then copy that changed formula down instead).

Rick


"iksuinje" wrote in message
...
Hi,

I want to pick out unique components in a cell which has both unique and
common components. My data list is as follows.

514/406
514/326
7/571
7/310
48/570
514/311
48/305
7/21
600/571
.
.
As you see, each cell has tow components "first/second". I want to pick
out
unique "first" and count cells which has the same "first". For example
using
the above list, what I want is as follows.

unique count
514 3
7 3
48 2
600 1


what I did is pick up the unique "first" by my own observation, and then
use
'countif' to count the numbers using a certain trick such as 514/*, 7/*...

thank you in advance.