Duplicate Entry Formula (Not a run-of-the-mill ask)

2weak2flex

Hi, I'm trying to create a duplicate entry "Flag" across my database. The database I'm working with is a database of equipment at my company. Here's the scenario: once we log the equipment in the database, that equipment is tied its room (Room A).

However, in a year or two, there could be a project that comes through and changes out SOME or ALL of the equipment that was in the room (Room A).

The objective is to NOT add ADDITIONAL equipment information to Room A. The data entry team needs a visual indicator that if they enter new data into the database that matches parameters of Room A, they need to pause, find Room A, explore what equipment was already listed, and then overwrite/replace any of the equipment data for Room A.

Room A should ONLY have one set of data tied to it, no matter how many times the equipment may change.

The duplicate flag I need will simply be a flag that screams out: "hey, this room already exists in the database, be careful with what you enter!"

I think this formula will be difficult, because most of the row data for each column will be the same.

The one thing that could be a distinguishing factor is that the L3AV JOB NUMBER would be a 'NEW' number if a new equipment entry for a particular room is added/changed. Maybe I need multiple columns? Conditional formatting? I don't know...In all my ramblings, I'm not sure if I'm making sense. I could probably explain better on a Zoom/Teams call.

Genevieve P.

Hey @2weak2flex

We can use a COUNTIFS formula to count how many times a row appears in this sheet that contains your criteria... we'd just need to know what cells have the data that you're looking for duplicates in.

For example, I presume you'll want to check the BLDG cell to see if in this building there's a duplicate. You may also want to use the Manufacturer column as a criteria, and the Category. I'm not quite sure what the job number is in reference to, so I'll leave that out in this example.

=COUNTIFS(column reference, "criteria", column reference, "criteria")

or in your case

=COUNTIFS(BLDG:BLDG, BLDG@row, MANUFACTURER:MANUFACTURER, MANUFACTURER@row, MODEL:MODEL, MODEL@row, CATEGORY:CATEGORY, CATEGORY@row)

If you need to add more criteria to match, you just need to add in the extra column and criteria after it:

=COUNTIFS(BLDG:BLDG, BLDG@row, MANUFACTURER:MANUFACTURER, MANUFACTURER@row, MODEL:MODEL, MODEL@row, CATEGORY:CATEGORY, CATEGORY@row, [L3AV JOB NUMBER]:[L3AV JOB NUMBER], [L3AV JOB NUMBER]@row)

Then here's how you would use a COUNTIFS within an IF statement to flag a duplicate:

=IF(COUNTIFS(formula) > 1, 1, 0)

or

=IF(COUNTIFS(BLDG:BLDG, BLDG@row, MANUFACTURER:MANUFACTURER, MANUFACTURER@row, MODEL:MODEL, MODEL@row, CATEGORY:CATEGORY, CATEGORY@row) > 1, 1, 0)

^ again, I've left out the Job number because if this value is unique for every entry (even when all the other values are duplicates) then you won't see a flag.

Let me know if that makes sense and works for you!

Cheers,

Genevieve