Find and Replace Tool across different columns
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 |
Solved! Go to Solution.
- Labels:
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks jdminton
Worked perfeclty
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
