Find and Replace Tool across different columns

[ChrisPark793]

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

[Raj]

Please find attached
mark done if this solve the problem,

[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:

1. 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.
2. 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
3. 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 · Accepted solution]

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.

 

 

[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!