Creating List of Unique Values from Data

Try this formula in G2:

Please Login or Register  to view this content.

amulee

Thanks and it is amazing

Could you tell me the logic of using * in this formula

I think the count is giving zero only in case of first value and for the subsequent values its answer is greater than zero. Kindly tell me why the values are not repeating

thanks again

Hi Azam,

The formula "COUNTIF(G$1:G1,C$2:C$15)" is to count occurrence of C$2:C$15 in G$1:G1. This formula will returns a series of numbers, which refers to each number's occurrence in G$1:G1.

When the formula being calulated in G2, it will returns {0;0;0;0;0;0;0;0;0;0;0;0;0;0}, which means each number({1200;1400;1600;1200;1400;1600;1200;1200;1600;1600;1200;1400;1400;1600}) occurrence in G$1:G1. 0 means the number was not in G$1:G2

When you copy the formula into G3, the formula will be changed to COUNTIF(G$1:G2,C$2:C$15). The result will be {1;0;0;1;0;0;1;1;0;0;1;0;0;0}. 1 means the number was in G$1:G2.

All these 1 and 0 actually reflect the occurrence of the numbers in C$2:C$15. When the number occurs, it will be 1 and vice versa. So I take 0 for the numbers I need. I only need the numbers which never occurs.

The formula (A$2:A$15&B$2:B$15=$F$2&$F$3) is the condition give, which will also returns a series of logical result.

Then I mutiply both results by each other. Logical True and False can be calculated as 1 and 0.

Please excuse my poor English. I hope you can understand it.

You can also use fomula tool to see what happened when the formula being calculated.

Dear amulee

thanks for your time and for such a detial reply

how do you create the enclosed (2011-6-14 14-10-59.gif) file. i use the evaluate formula option but there is no option to get a gif file.

thans a lot