1

Based on the Columns A AND B, (which are duplicates), I'd like to return the value in Column D IF Column C is blank enter image description here

1 Answer 1

0

Consider adding these two columns!

ColE: It will join both columns and B (in E2)...

=ARRAYFORMULA(CONCAT(A2:A,B2:B))

ColF: ... in order to count them and find the duplicates in (F2)

=byrow(E2:E,lambda(each,IF(each="","",COUNTIF(E2:E,each))))

ColH: This query will return A,B and D columns where C is blank and it's a duplicate (ColF > 1)

=query(A:F,"SELECT A,B,D where F > 1 AND C is null")

You can see it in action in this sheet: https://docs.google.com/spreadsheets/d/1fu8Z38IVj46fF1uXABZkX9s06vxF-UU9TkO9Bs9KtW4/edit?usp=sharing

I've tried to do it all in one cell, but calculations took forever

enter image description here

2
  • I know it says I'm not supposed to say thanks on these comments, but I really appreciate your help. Clever yet light workaround, I'm going to use this for other things I've overcomplicated!
    – Dustin
    Nov 5, 2022 at 22:16
  • Well, I'm glad it worked! Good luck with your projects!!
    – Martín
    Nov 6, 2022 at 3:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.