I am trying to process a list of data.


I want this in a table with unique entries

 Apple        1 
 Orange       2 
 Watermelon   3

Is this possible using formula alone?

  • 2
    countif() will fill out the counting part. But do you need formulae to develop the list of Apple, Orange, Watermelon? It's hard for a formula to know there are 3 unique values like that. A Pivot table would do the whole job if you didn't strictly need formulae to do the work. – Sir Adelaide Oct 30 '17 at 6:27
  • Are the 1,2,3 equal to the count of the repeated entries or are they unique ID's like a PK? It is not at all clear in your question, but all answers are assuming you want the count. – MoondogsMaDawg Oct 30 '17 at 13:28
  • 1
    If the real data is as simple as this example and you want a list with the count of occurrences, then a Pivot Table would serve nicely. – Engineer Toast Oct 30 '17 at 14:46

Select a blank cell such as D2, and enter formula

=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1,$B$2:$B$9),0)), "") 

into the Formula Bar, and then press the Ctrl + Shift + Enter keys simultaneously. (B2:B9 is the column data which you want to extract the unique values, D1 is the above cell where your formula is located)

The formula now should look like this:

{=IFERROR(INDEX($B$2:$B$9, MATCH(0, COUNTIF($D$1:D1, $B$2:$B$9), 0)), "")}

Keep selecting cell D2, then drag the Fill Handle down to get all unique values from the specified range.

Now all unique values in column range B2:B9 are extracted. When values in this range changed, the unique value list will be dynamically changed immediately.

After this point, you can use COUNTIF formula in an extra column in order to calculate the repetitions of each value. For example, enter the following formula in cell E2 and drag the Fill Handle down:


If you want to count the Occurrence as you have shown in 2nd Picture, you can use Countif function.

If you are looking for Unique entries in particular Data range, better use Data Validation.

let me say how, first select the Data range, then click Data validation command from Menu, then go to Settings and pick the Customs.

In the Formula Text Box just write this formula.


This Formula will only allow each entry once within the said Data range.

If you feel you can set Input Message & Error Alert also.

NB: In my Formula the Data range is an example you change it as you need.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.