Search code examples
if-statementgoogle-sheetsfilterlambdaarray-formulas

Last Step for a Google Sheet ArrayFormula to Compare Multiple Cells from Multiple Different Rows

We've made a checklist of Disney DVDs and Blu-rays. Some Disney movies are available in 4K, some are only on BD (Blu-ray), some are on 3D, etc.

This should hopefully all make a lot more sense with the test sheet we've been using here... https://docs.google.com/spreadsheets/d/1L2-npiEdTE9ardDi2q38sYhkKRFKAPCu_gtJFbOZa80/edit?usp=sharing

1.) We've created a column that will show if a movie release is available in another edition in a HIGHER quality format than the current entry for that row.

2.) We've also created a column that will show if a movie release is available in another edition in the SAME max quality as the current entry for that row.

We've received a lot of great help so far that had gotten us to these goals... Google Sheets ArrayFormula to Compare Multiple Cells from Multiple Different Rows ...but our users are asking for a final addition that will break our current formulas for making these determinations.

We've been asked to include separate columns to show which of each different format is included with any particular release as either a checkbox or yes/no dropdown.

Long Details You Probably Don't Need: This gives a higher level of information but breaks the formulas we currently use for what I've descried above. These would be separate columns from our disc quantity columns so that users can enter a movie release that they know the format for, but don't know the disc counts for. Entering just the formats included would still work for what we want even if users don't enter any quantities. I hope that all makes sense.

1.) For "Available in a Higher Quality Format?": 3D should be ignored as a possible quality upgrade BUT, 3D releases should look for 4K options as upgrade possibilities from 3D. For example. If Movie-A is only available on DVD and 3D, the DVD entry should NOT show that there is a quality upgrade available. (Unless the 3D release also includes a standard BD Blu-ray disc.) However, if Movie-B is on 3D and 4K, the 3D entry should show that there IS a quality upgrade. [4K > BD > DVD], [4K > 3D]

2.) For "Other Edition Available in Same Quality Format?": No format is ignored but each entry looks for other editions with the same max quality format. For example, if a 4K release includes a BD, a standard BD release will not include the 4K release as another edition available in the same quality even though it includes a BD disc. [4K > 3D > BD > DVD]

Thank you to everyone that has already gotten us this for and thank you for any further assistance!

Solution

  • Here's one approach you may test out. Adapted from earlier formula iteration

    1) Available in a Higher Quality Format?

    =let(Λ,byrow(F3:I,lambda(Σ,min(index(if(Σ="Yes",column(Σ),))))),
   map(B3:B,D3:D,Λ,lambda(b,d,x,if(b="",,if(x>vlookup(b&d,sort({B3:B&D3:D,Λ},2,1),2,),"Yes","No")))))

    2) Other Edition Available in Same Quality Format?

    =let(Λ,byrow(F3:I,lambda(Σ,min(index(if(Σ="Yes",column(Σ),))))),
       map(B3:B,D3:D,Λ,lambda(b,d,x,if(b="",,if(countif(filter(Λ,B3:B&D3:D=b&d),x)>1,"Yes","No")))))

    enter image description here