Find and Replace Tool across different columns

Hi All,

I was wondering if it was possible to use the Find and Replace tool for finding matching columns across 2 different sheets and then replacing data in one column from that on the second sheet

For example

I have 2 sheets of data below and I want to replace the comment "Pending" with the comment "Collected" from the second table, replace the blank comment with comment "Reserved" and replace the comment "Acknowledged" with the comment "Cancelled".

Last Internal Note	Collection No
Pending	12345A
	67891B
Acknowledged	98765C

Comments	Collection No
Collected	12345A
Reserved	67891B
Cancelled	98765C

Workflow

Accepted answers

jdminton

I think what you're trying to do is in the attached. The issue with Find Replace is that it is very slow when you have much data. Also, bringing the data in this way allows you to create formulas to update the data. Let me know if this solves or at least helps.

Find Replace.yxmd

All comments

Raj

Please find attached
mark done if this solve the problem,

comm 542.yxmd

ChrisPark793

Hi Raj,

This solved the problem however I never explained properly. If the comment is originally blank, this will not always be replaced with "Reserved". Blank could also be replaced with "Collected" or any other type of comment.

Hammad_Rashid

Yes, it is possible to use the Find and Replace tool in Alteryx to replace data in one column from another sheet. Here’s how you can do it:

Use the Join tool to join the two sheets based on the Collection No column. This will create a new table with all the columns from both sheets.
Use the Find and Replace tool to replace the values in the Comments column based on the values in the Last Internal Note column. Here’s how you can configure the tool:
- Select the Comments column as the F Anchor input.
- Select the Last Internal Note column as the Find input.
- Select the Comments column as the Replace input.
- In the Replace column, enter the following formula: IF IsNull([Comments]) THEN "Reserved" ELSEIF [Last Internal Note] = "Pending" THEN "Collected" ELSEIF [Last Internal Note] = "Acknowledged" THEN "Cancelled" ELSE [Comments] ENDIF
Use the Select tool to remove the Last Internal Note column from the output.

This should give you the desired output. If the comment is originally blank, it will be replaced with “Reserved” as per your requirement. Let me know if you have any further questions!

ChrisPark793

Hi Hammad-Rashid,

Sorry I never gave a great sample to properly explain my problem. The only constant wihin my data would be the Collection No.

Last Internal Note and Comments will be variable and can change - for example

Sheet 1

Last Internal Note	Collection No
Pending	12345A
	67891B
Acknowledged	98765C
Pending	43210D
	12345E

Sheet 2

Comments	Collection No
Collected	12345A
Reserved	67891B
Cancelled	98765C
Cancelled	43210D
Collected	12345E

Desired Output

Last Internal Note	Collection No
Collected	12345A
Reserved	67891B
Cancelled	98765C
Cancelled	43210D
Collected	12345E

jdminton

Find Replace.yxmd

ChrisPark793

Thanks jdminton

Worked perfeclty

lynnekilgore

This is a simple and really helpful set of steps. I'm so glad I found this - I fixed a simple error just now and the workflow runs well. Thank you!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703