I'm away for a few days from tomorrow, so if you do reply then your
response is likely to be lost when I get back. Assuming that you do
only have 3 groups, then you could do this:
=IF(K11=1,B11&IF(OR(K12=1,K13=1)," and ",""),"")&IF(K12=1,B12&IF
(K13=1," and ",""),"")&IF(K13=1,B13,"")
This caters for all three groups being tied, for 2 out of 3 being
tied, as well as for only one group having a rank of 1.
If you have more than three groups then the above approach would be a
bit unwieldy.
Hope this helps.
Pete
On Nov 19, 1:40*am, Pete_UK <pashu...@auditel.net> wrote:
> Do you only have 3 groups, as in your example (and formula), or could
> there be a lot more in practise?
>
> Pete
>
> On Nov 18, 9:00*pm, swansonray <swanson...@discussions.microsoft.com>
> wrote:
>
>
>
> > This does produce the said result and works if there is only 1 group ranked
> > 1st. Thank you. But what about the second scenario if there are two groups
> > ranked 1 (tied) I would like to display "Group1 and Group2" or "Group1 &
> > Group2"
>
> > "Pete_UK" wrote:
> > > You could put this in, say, L5 if your data is in column B and column
> > > K as in your example:
>
> > > =INDEX(B:B,MATCH(1,K:K,0))
>
> > > It will return the name from column B which has a rank of 1 in column
> > > K.
>
> > > Hope this helps.
>
> > > Pete
>
> > > On Nov 17, 7:36 pm, swansonray <swanson...@discussions.microsoft.com>
> > > wrote:
> > > > I am trying to solve a problem I am having with RANK. *I found some great
> > > > information that is posted on cpearson.com and I see what it does but the
> > > > formulas have to be in the same rows that the data is in. What I amlooking
> > > > for is to populate the 1st place for a caption in a separate row.
> > > > =OFFSET(B$11,MATCH(SMALL(K$11:K$13,ROW()-ROW(K$11)+1),K$11:K$13,0)-1,0)
> > > > works if data is in same row.
> > > > Info:
> > > > Column B * * * * * * * * * * * ColumnK
> > > > Name * * * * * *Rank
> > > > Group1 * * * * *2
> > > > Group2 * * * * *1
> > > > Group3 * * * * *3
>
> > > > Now in a row below the above data and in a separate column I want to display
> > > > the name that ranked 1st. ie Group2.
>
> > > > And finally if there is a tie for first in the data I want to display
> > > > Group2 and Group(Tied Group) in tha same cell.
>
> > > .- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
Similar Threads
