I got this "table" (page raids) and want to do function formating for highlight duplicates (yellow).

I already try functions VLOOKUP, Match, Filter, countifs and Quary (dont know how it works...) with Index function, but nothing seems work as I want ... VLOOKUP and Match only gives first value. Filter doesnt work on this strucker or I dont know how to use it in this case (I know how it works in normal table)

Can someone tell me what function conbination to use that can be put in Function formating ?

Formating for each row: Example: =IF(B3>0;IF(B4<1490;TRUE;FALSE);FALSE)

ECT.

CodePudding user response：

See if this is what you are looking for?

Conditional formatting with Custom formula in Range: A2:H:

=LAMBDA(NAME,OR(A1=NAME,A2=NAME,A3=NAME))("Gunslinger")

or do you means you want to highlight only duplicates?

If that is the case, you may try this formula:

=LAMBDA(DATARANGE,
 LAMBDA(FLAT,
  {{"Team","Duplicate"};{UNIQUE(FLAT),BYROW(UNIQUE(FLAT),LAMBDA(NAME,COUNTIF(FLAT,NAME)>1))}}
 )(
  QUERY(FLATTEN(
   BYCOL(DATARANGE,LAMBDA(C,
    BYROW(C,LAMBDA(R,
     IF((ROW(R) 1)/4=INT((ROW(R) 1)/4),R,"")
    ))
   ))
  ),"SELECT Col1 WHERE Col1 IS NOT NULL",0)
 )
)(A2:H)

This formula returns an array of unique Names with checking if there are Duplicates in the given datarange.

Combine this with the 1st formula should be able to highlight repeated data sets from your table.

Solution for 2 criteria matching:

The formula inside the image form the table of unique match results, use that result with OR(), XLOOKUP(), OFFSET(), INDEX() as below for the final formula to put into the costume formula in conditional formtting.

=LAMBDA(RESULT,
 LAMBDA(NAME,DUP,KEY,
  LAMBDA(KEY_M2,KEY_M1,KEY_P1,
   OR(
    XLOOKUP(KEY_M2&"&&"&KEY_M1,NAME,DUP,FALSE),
    XLOOKUP(KEY_M1&"&&"&KEY,NAME,DUP,FALSE),
    XLOOKUP(KEY&"&&"&KEY_P1,NAME,DUP,FALSE)
   )
  )(IFERROR(OFFSET(KEY,-2,0),""),IFERROR(OFFSET(KEY,-1,0),""),IFERROR(OFFSET(KEY,1,0),""))
 )(INDEX(RESULT,,1),INDEX(RESULT,,2),A2)
)(
 LAMBDA(DATARANGE,
  LAMBDA(FLATCLASS,
   {{"Name","Duplicate"};{UNIQUE(FLATCLASS),BYROW(UNIQUE(FLATCLASS),LAMBDA(NAME,COUNTIF(FLATCLASS,NAME)>1))}}
  )(
   QUERY(FLATTEN(
    BYCOL(DATARANGE,LAMBDA(C,
     BYROW(C,LAMBDA(R,
      IF((ROW(R) 1)/4=INT((ROW(R) 1)/4),IF(R="","",INDEX(C,ROW(R)-2)&"&&"&R),"")
     ))
    ))
   ),"SELECT Col1 WHERE Col1 IS NOT NULL",0)
  )
 )($A$2:$H)
)

CodePudding user response：

After some searching, testing and me not being dum I came up with this:

=ArrayFormula(SUM(IFERROR(FIND(B2&B3;MID(CONCATENATE((TRANSPOSE($B$2:$I$33)));SEQUENCE(LEN(CONCATENATE((TRANSPOSE($B$2:$I$33))));1;1;1);len(B2&B3)))=1)*1))>1

Still need to make the functions more tidy (picture 2 and 3).