Re: retrieve unique items with 2 criteria

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

You guys continue to impress. This is great.
I do have a couple of questions though.

If I understand this correctly, the match formula can have a lookup value as
an array instead of just a single value. If its lookup array and lookup value
are the same, then it is simply giving an array of the position of each item
in the total range used in the lookup value/lookup range.
It appears you've concatenated the 2 criteria columns for the purpose of
locating their commonalities in the array. This is intuitive looking back on
it but clever without knowing it. Was the "#" an arbitrary choice for a
connector? Why is it necessary to have any connector?

thanks to both Domenic and Bob for this one!

Dave




"Domenic" wrote:

> Try...
>
> =SMALL(IF((M7:M100=1986)*(MATCH(M7:M100&"#"&O7:O100,M7:M100&"#"&O7:O100,0
> )=ROW(O7:O100)-ROW(O7)+1),O7:O100),2)
>
> ....confirmed with CONTROL+SHIFT+ENTER. Adjust the range accordingly.
>
> Hope this helps!
>
> In article <07FDC55C-4184-425B-A900-1677FDAD439A@xxxxxxxxxxxxx>,
> "Dave Breitenbach" <DaveBreitenbach@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>
> > Alright. Question answered but I've got another one. Of the unique values
> > in column O, I've been trying to apply the small function to the formula
> > below to give me the kth smallest value of the unique O column values. I've
> > tried a few positions including the following but have not had any luck:
> >
> > =SMALL(--(FREQUENCY(IF(TRIM('prepay detail'!M7:M2710)=$B32,MATCH('prepay
> > detail'!O7:O2710,'prepay detail'!O7:O2710,0)),ROW(INDIRECT(
> > "1:"&ROWS('prepay detail'!O7:O2710))))>0),1)
> >
> > any thoughts?
> >
> > tia,
> > Dave
> >
> > "Bob Phillips" wrote:
> >
> > > Dave,
> > >
> > > You could use
> > >
> > > =SUM(--(FREQUENCY(IF(M7:M2710=1986,MATCH(O7:O2710,O7:O2710,0)),ROW(INDIRECT(
> > > "1:"&ROWS(O7:O2710))))>0))
> > >
> > > which is an array formula, so commit with Ctrl-Shift-Enter
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Dave Breitenbach" <DaveBreitenbach@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
> > > message news:04F1B3CE-7F39-4182-9E50-6BA9CCA23CBA@xxxxxxxxxxxxxxxx
> > > > I've been using the following formula from the xldynamic website detailing
> > > > sumproduct usage as a base for retrieving uniqe instances in a data
> > > series.
> > > > I've been trying to modify the formula to allow me to utilize one
> > > additional
> > > > criteria.
> > > > Original formula:
> > > > =SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&""))
> > > >
> > > > Here is my formula:
> > > >
> > > =SUMPRODUCT((($O$7:$O$2710<>"")*($M$7:$M$2710="1986"))/COUNTIF($O$7:$O$2710,
> > > $O$7:$O$2710&""))
> > > >
> > > > I'm trying to say how many unique instances are there for column O, where
> > > > column m = "1986?" Not sure what I'm doing wrong but help would be
> > > > appreciated.
> > > >
> > > > tia,
> > > > Dave
> > > >
> > > >
> > > > This seems to apply only the second criteria
> > >
> > >
> > >
>
.

Quantcast