Distinct value in numbers column

Hoot,

Here's a way that uses the Filter pane to give you a look only at a list of distinct entries. This may be better than creating a separate table listing of distince entries in some cases.

First, I add a column which is an index of distinct entries. This is where most approaches begin. The difference here is that I use a special text string to identify repeated instances of values from higher in the table. Here's a screen shot:

The cells displaying "hide" are the repeats. The formula in my column B is:

=IF(B=1, COUNTIF(B$1:B2, 1), "hide")

It's simple and that's all you will have to do to the table before setting up the filter criteria.

The following screen shot shows what the table will look like after you activate the filter that hides the rows with repeated content by clicking on the Filters checkbox.

I think it's a nice lean approach. I haven't been a big fan of the Filter pane operation, but I'm starting to see the benefits in cases like this.

Jerry

Hi Leo,

It was a great surprise to see Jerry's post dated today (January 24, 2017) as he hasn't posted in this forum for the past 21 months! I suspect the date was changed when you marked the post Helpful today.

Jerry's table does include a column B, as the formula indicates, but that column is hidden.

Here's a solution (very similar to Waynes's) that gives the same results as shown by Jerry, but uses only column A, containing the items and column B. containing the index.

The filter rule is shown to the right of the table:

Checking the "Filter" checkbox in the filter inspector shows only the rows with the first occurrence of each distinct value in column A.

Formula in column B, entered in B2 and filled down to the end of the column:

IF(COUNTIF(A$1:A2,A2)=1,ROW()−1−COUNTIF(B$1:B1,"hide"),"hide")

Plain language translation:

If this is the only occurrence of the value in 'this row' of column A from row 1 to 'this row' of column A,

then

Get the number of 'this row', subtract 1 and subtract the number of occurrences of the word "hide" in cells above 'this row' in column B. Return that number to 'this cell'

else (ie. if there is more than one copy of the value)

Return the text "hide" to 'this cell'.

The number returned will be the number of distinct values in Column A from row 2 to the row containing the number. In this table, the largest number is the largest possible for the set of random two letter values constructed from X or Y in the first position and the letters A, B, C, D, E, F, or G in the second.

Regards,

Barry

Hi Barry,

Thanks for posting this. I don't remember seeing Jerry's original post but this is indeed a nice, clean approach, that can be simplified even more:

Just a short COUNTIF in B2, filled down:

=COUNTIF(A$1:A2,A2)

And filter on 1.

The filter can be set without even going to the panel at right:

SG

This may work for you:

C2=IF(ISBLANK(A2), "", IF(ISBLANK(A2),"",IF(IFERROR(MATCH(A2, $A$1:A1,0),0)>0,"",A2)))

D2=IF(ISBLANK(A2), "", IF(C2=A2, B2, ""))

select C2 and D2, copy

select C2 thru the end of column D, paste

Hoot,

I was protecting both columns from blank cells at the end. Try without... you may not need. Well done on

understanding. You are a first in my experience!

All the best