Conditional Find & Replace

Question

Is there a way to do a conditional find and replace between rows?

If Client [Entity ID], Tag, Value, Trade Date, and Posted Date equal another row, then replace the Entity ID and Security with that other row?

I've tried the multi-row formula, but not all of the data will have a match or be able to be sorted so it's counterpart is -1 or +1

Capture.PNG

Capture2.PNG

alexisdewaine · Answer

Yes, another row in the same data set. I will give the sort & summarize method a try!

DawnDuong · Answer

Hi @alexisdewaine

Not 100% sure I understand the problem statement, so hazarding a guess here.

* By "another row" do you mean "another row in the same dataset" i.e. there are more than records in the dataset with the same Client [Entity ID], Tag, Value, Trade Date, and Posted Date. If this is indeed the case, and suppose you want to update all the subsequent records with the EntityID and Security of the first record, then one solution is to use the Sort > Summarize > First, with Grouping Client [Entity ID], Tag, Value, Trade Date, and Posted Date.
* If you are checking one dataset against a different dataset, then the Join tool is probably what you are looking for.

Check out the excellent bite-sized interactive lessons on Academy if you need a refresher.

Best regards,
Dawn.

apathetichell · Answer

Use a join.