Skip to main content

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


Can you annotate a screen shot to highlight a match?


@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.



@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.



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.


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).


Reply