Help

Re: How do I search two fields for instances where values match?

633 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Admin_Charriol
4 - Data Explorer
4 - Data Explorer

I have two fields full of product reference numbers that I want to compare for matches.

The first is called Reference . This is a long list of stuff we make, some of which are out of date and out of stock.

The second is called OldReference , which contains just the old items with remaining inventory.

I want to search the two fields for instances where the reference numbers match, and then mark them somehow. I assume in a third field that returns 1 or 0, true or false, yes or no…

Anybody know how to do this using a formula, lookup field, or some combination?

Thanks in advance to anyone with a good idea. :grinning_face_with_big_eyes:

-Frank
Screen Shot 2022-03-02 at 11.17.01 AM

4 Replies 4

Can you annotate a screen shot to highlight a match?

Admin_Charriol
4 - Data Explorer
4 - Data Explorer

@kuovonne These would all be matches. I went through 589 records and manually matched things using a linked record. I will have to do this again, though… would love to automate as much as possible.
Screen Shot 2022-03-02 at 6.34.17 PM

Try creating a formula field like

IF(
    {REFERENCE} = {Old Stuff},
    "MATCH!"
)

If there could be leading/trailing spaces, you can use

IF(
    TRIM({REFERENCE}) = TRIM({Old Stuff}),
    "MATCH!"
)

Then filter for when the formula field has a value.

Note that your two screen shots have different “old” field names: {OldReference} versus {Old Stuff}. You will need to use the proper field name.

Thanks for responding! I’ll give this a try. (And noted, I’ll be sure to use correct field names in the formula).