CODEWITHSUNDEEP
filtergoogle-sheetsgoogle-sheets-formulacountifarray-formulas
TC76
TC76

Reputation: 870

FILTER values from one sheet when not present on another

I'm trying to compare Items in "projectEstimate!D2:D & projectEstimate!E2:E" to Items in 'itemsAssociations!C3:C & itemsAssociations!D3:D" for matches.

If there is a match, confirm that the associated item (itemsAssociations!G3:G & itemsAssociations!H3:H) is not already listed in "projectEstimate".

If it is not listed, print that item. If it IS listed, do nothing.

I put together the following code which seems like it should work, but the item prints whether it's present on projectEstimate or not.

=ArrayFormula({itemsAssociations!I2:J2;FILTER(itemsAssociations!I3:J,
{projectEstimate!D2:D&IF(LEN(projectEstimate!E2:E),projectEstimate!E2:E,)=itemsAssociations!C3:C&IF(LEN(itemsAssociations!D3:D),itemsAssociations!D3:D,)},
{projectEstimate!D2:D&IF(LEN(projectEstimate!E2:E),projectEstimate!E2:E,)<>itemsAssociations!G3:G&IF(LEN(itemsAssociations!H3:H),itemsAssociations!H3:H,)}
)})

I also tried this QUERY, but not sure how to include the entire ranges

=QUERY(itemsAssociations!C3:J,"SELECT I,J WHERE C = '"&projectEstimate!D2:D&"' AND D = '"&projectEstimate!E2:E&"' AND J != '"&projectEstimate!D2:D&"'",0)

This is close, but the opposite result:

=FILTER(projectEstimate!D2:D,COUNTIF(FILTER(itemsAssociations!J3:J,COUNTIF(itemsAssociations!C3:C&itemsAssociations!D3:D,itemsAssociations!C3:C&itemsAssociations!D3:D)),projectEstimate!D2:D))

My sheet

Upvotes: 1

Views: 291

Answers (2)

TC76
TC76

Reputation: 870

This is what I came up with...

=IFERROR(FILTER(FILTER(itemsAssociations!J3:J,NOT(COUNTIF(projectEstimate!D3:D,itemsAssociations!C3:C))),NOT(COUNTIF(FILTER(projectEstimate!D2:E,NOT(COUNTIF(itemsAssociations!C3:C&itemsAssociations!D3:D,projectEstimate!D2:D&projectEstimate!E2:E))),FILTER(itemsAssociations!J3:J,NOT(COUNTIF(projectEstimate!D3:D,itemsAssociations!C3:C)))))),"No suggested items!")

Upvotes: 0

player0
player0

Reputation: 1

it would be something amongst theses lines:

=ARRAYFORMULA(REGEXREPLACE(QUERY(FILTER(projectEstimate!D2:D&":"&projectEstimate!E2:E, 
 NOT(COUNTIF(itemsAssociations!C3:C&":"&itemsAssociations!D2:D,projectEstimate!D2:D&":"&projectEstimate!E2:E))), 
 "where Col1 is not null", 0), ":$", ))

Upvotes: 1

Related Questions