Unique Identifier for Certain Criteria

[rosetc16]

I posted a similar question earlier, but this question has slightly different criteria.

I'm looking for this column to either have the number 1 or 2... a number will only appear if the column says "YES" for include2 column... if a team has a yes in Include1 and Include2, then I want the first team that shows up with the same TransID to have a 1 and the other team to have a 2....

If a team has a no in the include1 column, but a yes in the include2 column, then they should have a 2.... if the other team in this trans has two yes in Include1 and Include 2, regardless of whether they are the first team or not, then they will have a 1

The desired result is below and attached. Thank you so much!

[Special-K]

Not quite clear.

This

"If a team has a yes in Include1 and Include2, then I want ... ...and the other team to have a 2...."

contradicts this

"if the other team in this trans has two yes in Include1 and Include 2, regardless of whether they are the first team or not, then they will have a 1"

[rosetc16]

The attached document has the desired result if that helps...

Let me see if I can give 2 situations to help paint the picture:

Situation 1: Same transaction #... both teams in the transaction have a "YES" in the "Include1" and the "Include2" columns... the first team listed as the YES/YES team would be team 1 and the other would be team 2

Situation 2: Same transactions #.... The first team listed has a "NO" in the "Include1" and a "YES" in the "Include2" column... I want them listed as team 2.... the 2nd team listed would have a "YES" in the "Include1" column and a "YES" in the "Include2" column


Both of these situations should be represented in the document attached

[Special-K]

I'm finding this very difficult, I'll have a go at solving it but I'm running out of ideas..

[rosetc16]

Difficult as far as my explanation or just the general difficulty of the formula?

[Special-K]

Formula, I can't even get the first situation set up.

[rosetc16]

Maybe we can tag team it...

I think this figures out the first portion, but I can't get the second one

=IF($H3="NO","-",IF(AND($H3="YES",$E3=INDEX($E$3:$E$17,MATCH($D3,$D$3:$D$17,0))),1,2))

[johnmpl]

Hi, to all!

Maybe this could be a try:
=IF(H3="NO","-",IF(COUNTIFS(D$3:D$17,D3,G$3:G$17,"YES")=1,1+(G3="NO"),IF(D2=D3,IF(COUNTIF(E$2:E2,E3),INDEX(B$2:B2,MATCH(E3,E$2:E2,)),N(B2)+1),1)))

If not, put a "representative" file showing us the correct answer. Blessings!

[rosetc16]

I have attached a new file that is more "representative." That formula is correct for some of my columns, but as you can see from the
"Desired ID" column, some are not showing up with an ID at all (which is confusing me because it is pulling a "-" from the first
IF statement even though the "Include2" column says "YES"). Please let me know what I can do to fix this!

[johnmpl]

Hi, again!

Check this revised formula:

[B3] : =IF(H3="NO","-",
IF(COUNTIFS(D$3:D$1000,D3,G$3:G$1000,"YES")=1,1+(G3="NO"),
IF(D2=D3,IF(COUNTIFS(D$2:D2,D3,E$2:E2,E3),LOOKUP(2,1/(D$2:D2=D3)/(E$2:E2=E3),B$2:B2),N(B2)+1),1)))

Blessings!