IF findstring and replace

Question

If I have three columns but am looking to write a formula IF findstring for column 1(IRON) with partial match data to column 3(SALES), but then, if there is a partial match, I want column 3 to replace the column 1 partial data.

input:

IRONSUPPLYSALESPO676334PO00902 545PO112118PO67633 889PO009021PO22123 221

Output:

IRONSUPPLYSALESPO67633 8894PO00902 545PO112118PO67633 889PO00902 5451PO22123 221

Thanks in advance

ThePickleWizard · Answer

Awesome stuff. It's unfortunate that I cannot use the If findstring method. I will give both methods a try. Thanks for the responses and time!

binu_acs · Answer

@ThePickleWizard One way of doing this

Find_Replace_040223.yxmd

davidskaife · Answer

Hi @ThePickleWizard

In order to effectively do this a standard Formula tool won't suffice, instead you'll need to use aMulti-Row Formula tool. Below (and attached) is one way of doing this:

First place a RecordID tool to allow you to keep the order of the data, then Transpose it vertically. Sort the data on Value so it places the figures in Iron/Sales next to each other, then apply your formula like so:

Cross tab the data back to the horizontal, sort again but this time on RecordID, and finally move the fields around to the original layour/remove the RecordID:

Multi-Row.yxmd