Duplicate Helper column with filter

T.Shuya

Hi!

I'm trying to build a formula where I search for duplicates within one column (Job Numbers) but only if "Invoice" has been selected in another column (Deliverable Type). I was able to get a formula to search for duplicates working but it is searching all the deliverable types, but I just need to know if there is more than one invoice submitted with the same job number. Thank you!

Samuel Mueller

@T.Shuya Okay, final answer, sorry. This should work

=IF([Deliverable Type]@row <> "Invoice", 0, IF(COUNTIFS([Job Number]:[Job Number], [Job Number]@row, [Deliverable Type]:[Deliverable Type], @cell = "invoice") > 1, 1, 0))

Samuel Mueller

@T.Shuya try something like this. Update the words in the brackets to match your column names.

=countifs([jobnumbers]:[jobnumbers], [jobnumbers]@row, [deliverable type]:[deliverable type], [deliverable type]@row)

This formula will return a number from 0 to 1, 2, etc. depending on how many times the critera is met. anything greater than 1 means a duplicate.

Depending on what you need returned, you can put this in an if statement like so:

=if( countifs([jobnumbers]:[jobnumbers], [jobnumbers]@row, [deliverable type]:[deliverable type], [deliverable type]@row) >0, "Duplicate", "Unique")

** if deliverable type is a multi select dropdown, you will need to use contains or has: contains("invoice",@cell)

T.Shuya

Thanks Sam! Not quite working the way I'd hoped. I'd like a check mark to appear in the "Duplicate Helper" column when there are duplicates in the job number column. I only want it to check and return a result if the "Deliverable Type" is "Invoice" which is a single select drop down. So if the deliverable type is "Report" it won't consider them. We usually get a report and an invoice with the same job number but sometimes we have multiple invoices with the same job number and I want to show that visually as it effects or process to complete them.

Samuel Mueller

@T.Shuya try this then,

=if( countifs([job number]:[job number], [job number]@row, [deliverable type]:[deliverable type], "invoice") >0, 1, 0)

Sorry I put deliverabletype @row instead of "invoice"

T.Shuya

Thank you so much for helping!! But that still isn't working. This just check marked all the invoice lines but not the duplicates.

Samuel Mueller

@T.Shuya Okay, final answer, sorry. This should work

=IF([Deliverable Type]@row <> "Invoice", 0, IF(COUNTIFS([Job Number]:[Job Number], [Job Number]@row, [Deliverable Type]:[Deliverable Type], @cell = "invoice") > 1, 1, 0))

T.Shuya

You are amazing! Thank you very much!!!

T.Shuya

This worked amazing but I overlooked another issue. I have another column (Status) and I also need the formula to ignore the "completed" invoices that are in the status column. Is it possible to add it?

Samuel Mueller

@T.Shuya

You can add an "or" condition at the beginning like this:

=IF(OR([Deliverable Type]@row <> "Invoice", Status@row = "Complete"), 0, IF(COUNTIFS([Job Number]:[Job Number], [Job Number]@row, [Deliverable Type]:[Deliverable Type], "invoice") > 1, 1, 0))

T.Shuya

Didn't quite work or it worked the opposite of how it should. I would like the invoices that aren't completed to have the check mark. The parameters are now: Show a flag if there are duplicate invoices with the same job number that are not completed.

Samuel Mueller

@T.Shuya Try this

=IF([Deliverable Type]@row <> "Invoice", 0, IF(COUNTIFS([Job Number]:[Job Number], [Job Number]@row, [Deliverable Type]:[Deliverable Type], "invoice", Status:status, "<>Complete") > 1, 1, 0))

T.Shuya

I can't tell you how much I appreciate your help on this. It still doesn't seem to work as it will also flag completed invoices and their duplicates.

Samuel Mueller

@T.Shuya Ok lets combine the criteria,

Try this -

=IF(OR([Deliverable Type]@row <> "Invoice", Status@row = "Complete"), 0, IF(COUNTIFS([Job Number]:[Job Number], [Job Number]@row, [Deliverable Type]:[Deliverable Type], "invoice", Status:Status, "<>Complete") > 1, 1, 0))

T.Shuya

No go. I wish I knew formulas.

Samuel Mueller

@T.Shuya

Tell me again what you are trying to do, now? it's working in my test. Invoice that's not started with duplicate job numbers. All the other duplicates either have complete, or are bills. 1 indicates a checkbox or flag

Samuel Mueller

@T.Shuya my formula says complete, you may need to change it to completed

=IF(OR([Deliverable Type]@row <> "Invoice", Status@row = "Completed"), 0, IF(COUNTIFS([Job Number]:[Job Number], [Job Number]@row, [Deliverable Type]:[Deliverable Type], "invoice", Status:Status, "<>Completed") > 1, 1, 0))