DISTINCT

Mike B

Can DISTINCT be used to identify a distinct row of data?

Paul Newcome

How so? What exactly are you trying to accomplish?

Mike B

The sheet would look like this:

I would like to identify the DISTINCT rows only:

Paul Newcome

Which field(s) would be used to meet the requirement? It looks like you are excluding the date field? Is there a particular one from each grouping that you would want to pull such as earliest or latest date?

Mike B

The grouping would just include LO No, P.O., Cost Ctr, Vendor, Amt.,

Paul Newcome

And if there are multiple rows such as for 873, which one would be pulled? Earliest Date?

Mike B

Correct, earliest date.

Paul Newcome

Ok. First, for any column that could contain both numerical and text entries (such as the [LO No] column), you will need to create a helper column that converts everything into text strings.

=[LO No]@row + ""

From there you would insert a checkbox column with something along these lines...

=IF(COUNTIFS([LO No Helper]:[LO No Helper], @cell = [LO No Helper]@row, [P.O.]:[P.O.], @cell = [P.O.]@row, [Cost Ctr]:[Cost Ctr], @cell = [Cost Ctr]@row, Vendor:Vendor, @cell = Vendor@row, Amt:Amt, @cell = Amt@row, Date:Date, @cell<= Date@row) = 1, 1)

This should check the box for the earliest date for each of those unique combinations. You can then create a row report that is filtered to only show rows where that box is checked.