Auto Find/Replace Multiples

[caj1980]

I have two lists. One is my source data. The second is the truncated version that I want the first list replaced with. I need a solution that will make this efficient and preferably automated as I don't want to have to follow a lengthy process every time I update the source data (typically daily).

This is being done as a result of the problem/solution in this thread:
https://www.mrexcel.com/forum/excel-questions/1020474-custom-list-length-limit.html#post4896913

Here are the two lists side by side.

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD="width: 484"]Ready[/TD]
[TD="width: 165"]Ready[/TD]
[/TR]
[TR]
[TD]Project Initiation#Pause#Provide Inputs at Project Kick-off[/TD]
[TD]PI#P#Kick-off[/TD]
[/TR]
[TR]
[TD]Project Initiation#Pause#Research Impact Assessment[/TD]
[TD]PI#P#Assess[/TD]
[/TR]
[TR]
[TD]Project Initiation#Pause#Verify Approved Cutsheet[/TD]
[TD]PI#P#Cutsheet[/TD]
[/TR]
[TR]
[TD]Project Initiation#Pause#Hard Reserve Position[/TD]
[TD]PI#P#Position[/TD]
[/TR]
[TR]
[TD]Project Initiation#In-Progress#Provide Inputs at Project Kick-off[/TD]
[TD]PI#IP#Kick-off[/TD]
[/TR]
[TR]
[TD]Project Initiation#In-Progress#Research Impact Assessment[/TD]
[TD]PI#IP#Assessment[/TD]
[/TR]
[TR]
[TD]Project Initiation#In-Progress#Verify Approved Cutsheet[/TD]
[TD]PI#IP#Cutsheet[/TD]
[/TR]
[TR]
[TD]Project Initiation#In-Progress#Hard Reserve Position[/TD]
[TD]PI#IP#Position[/TD]
[/TR]
[TR]
[TD]Cabling Vendor Engagement#Pause#Create Vendor SOW[/TD]
[TD]VE#P#SOW[/TD]
[/TR]
[TR]
[TD]Cabling Vendor Engagement#Pause#Assist in Vendor Walk-Thru[/TD]
[TD]VE#P#Walk-Thru[/TD]
[/TR]
[TR]
[TD]Cabling Vendor Engagement#Pause#Analyze Bids from Vendors[/TD]
[TD]VE#P#Bids[/TD]
[/TR]
[TR]
[TD]Cabling Vendor Engagement#Pause#Vendor Selection and Support[/TD]
[TD]VE#P#Select[/TD]
[/TR]
[TR]
[TD]Cabling Vendor Engagement#In-Progress#Create Vendor SOW[/TD]
[TD]VE#IP#SOW[/TD]
[/TR]
[TR]
[TD]Cabling Vendor Engagement#In-Progress#Assist in Vendor Walk-Thru[/TD]
[TD]VE#IP#Walk-Thru[/TD]
[/TR]
[TR]
[TD]Cabling Vendor Engagement#In-Progress#Analyze Bids from Vendors[/TD]
[TD]VE#IP#Bids[/TD]
[/TR]
[TR]
[TD]Cabling Vendor Engagement#In-Progress#Vendor Selection and Support[/TD]
[TD]VE#IP#Select[/TD]
[/TR]
[TR]
[TD]Financial Vendor Approval#Pause#Cut TT to NSI Finance for PR and PO[/TD]
[TD]VA#P#PR[/TD]
[/TR]
[TR]
[TD]Financial Vendor Approval#Pause#Approved PO to Vendor[/TD]
[TD]VA#P#PO[/TD]
[/TR]
[TR]
[TD]Financial Vendor Approval#In-Progress#Cut TT to NSI Finance for PR and PO[/TD]
[TD]VA#IP#PR[/TD]
[/TR]
[TR]
[TD]Financial Vendor Approval#In-Progress#Approved PO to Vendor[/TD]
[TD]VA#IP#PO[/TD]
[/TR]
[TR]
[TD]Cabling Installation#Pause#Cabling - CM and Access Request[/TD]
[TD]CI#P#Request[/TD]
[/TR]
[TR]
[TD]Cabling Installation#Pause#Verify Material Procurement[/TD]
[TD]CI#P#Procure[/TD]
[/TR]
[TR]
[TD]Cabling Installation#Pause#Monitor Vendor Cable Installation[/TD]
[TD]CI#P#Monitor[/TD]
[/TR]
[TR]
[TD]Cabling Installation#In-Progress#Cabling - CM and Access Request[/TD]
[TD]CI#IP#Request[/TD]
[/TR]
[TR]
[TD]Cabling Installation#In-Progress#Verify Material Procurement[/TD]
[TD]CI#IP#Procure[/TD]
[/TR]
[TR]
[TD]Cabling Installation#In-Progress#Monitor Vendor Cable Installation[/TD]
[TD]CI#IP#Monitor[/TD]
[/TR]
[TR]
[TD]Rack Strider Workflow#Pause#Confirm Rack Arrival[/TD]
[TD]SW#P#Arrival[/TD]
[/TR]
[TR]
[TD]Rack Strider Workflow#Pause#Final RSPC Validation[/TD]
[TD]SW#P#Validate[/TD]
[/TR]
[TR]
[TD]Rack Strider Workflow#Pause#Scan Rack Into Position[/TD]
[TD]SW#P#Scan[/TD]
[/TR]
[TR]
[TD]Rack Strider Workflow#Pause#Cut TT for Bolt Down & Energize[/TD]
[TD]SW#P#Energize[/TD]
[/TR]
[TR]
[TD]Rack Strider Workflow#Pause#Complete Strider Workflow[/TD]
[TD]SW#P#Complete[/TD]
[/TR]
[TR]
[TD]Rack Strider Workflow#In-Progress#Confirm Rack Arrival[/TD]
[TD]SW#IP#Arrival[/TD]
[/TR]
[TR]
[TD]Rack Strider Workflow#In-Progress#Final RSPC Validation[/TD]
[TD]SW#IP#Validate[/TD]
[/TR]
[TR]
[TD]Rack Strider Workflow#In-Progress#Scan Rack Into Position[/TD]
[TD]SW#IP#Scan[/TD]
[/TR]
[TR]
[TD]Rack Strider Workflow#In-Progress#Cut TT for Bolt Down & Energize[/TD]
[TD]SW#IP#Energize[/TD]
[/TR]
[TR]
[TD]Rack Strider Workflow#In-Progress#Complete Strider Workflow[/TD]
[TD]SW#IP#Complete[/TD]
[/TR]
[TR]
[TD]Build & Troubleshoot#Pause[/TD]
[TD]BT#P[/TD]
[/TR]
[TR]
[TD]Build & Troubleshoot#In-Progress[/TD]
[TD]BT#IP[/TD]
[/TR]
[TR]
[TD]Validation and Turn-Up#Pause#Final Validation[/TD]
[TD]VT#P#Validate[/TD]
[/TR]
[TR]
[TD]Validation and Turn-Up#Pause#Turn-Up and NHO[/TD]
[TD]VT#P#Turn-Up[/TD]
[/TR]
[TR]
[TD]Validation and Turn-Up#In-Progress#Final Validation[/TD]
[TD]VT#IP#Validate[/TD]
[/TR]
[TR]
[TD]Validation and Turn-Up#In-Progress#Turn-Up and NHO[/TD]
[TD]VT#IP#Turn-Up[/TD]
[/TR]
[TR]
[TD]Project Closure#Pause#Create Project Closure CM[/TD]
[TD]PC#P#Create[/TD]
[/TR]
[TR]
[TD]Project Closure#Pause#Rack Elevation & Take Pictures[/TD]
[TD]PC#P#Pics[/TD]
[/TR]
[TR]
[TD]Project Closure#Pause#Walk-Thru with DCO[/TD]
[TD]PC#P#PCF[/TD]
[/TR]
[TR]
[TD]Project Closure#Pause#NSI Manager sign-off[/TD]
[TD]PC#P#NSI Manager[/TD]
[/TR]
[TR]
[TD]Project Closure#Pause#Close Project Closure CM and Project[/TD]
[TD]PC#P#Close[/TD]
[/TR]
[TR]
[TD]Project Closure#In-Progress#Create Project Closure CM[/TD]
[TD]PC#IP#Create[/TD]
[/TR]
[TR]
[TD]Project Closure#In-Progress#Rack Elevation & Take Pictures[/TD]
[TD]PC#IP#Pics[/TD]
[/TR]
[TR]
[TD]Project Closure#In-Progress#Walk-Thru with DCO[/TD]
[TD]PC#IP#PCF[/TD]
[/TR]
[TR]
[TD]Project Closure#In-Progress#NSI Manager sign-off[/TD]
[TD]PC#IP#NSI Manager[/TD]
[/TR]
[TR]
[TD]Project Closure#In-Progress#Close Project Closure CM and Project[/TD]
[TD]PC#IP#Close[/TD]
[/TR]
[TR]
[TD]Project Done[/TD]
[TD]Project Done[/TD]
[/TR]
</tbody>[/TABLE]

 

[caj1980]

I think this solution would work for me but I have no idea how to tweak it to match my workbook.

Multiple Find & Replace text in cells from a list

A sanitized copy of my workbook can be found here.
Dropbox - Public
Sheet "Import" is the target of the Fin/Replace, specifically column E.
Sheet "Custom Lists" shows the before (Phases) and after (Truncated Phases) for the find and replace operation.

 

[Peter_SSs]

Sheet "Import" is the target of the Fin/Replace, specifically column E.

I note that you have also been asking about unmerging cells in another thread. Is this related? That is, does column E contain merged cells?
If so, will they be unmerged with values duplicated like you asked for in that other thread, before the Find/Replace is implemented?

 

[caj1980]

I note that you have also been asking about unmerging cells in another thread. Is this related? That is, does column E contain merged cells?
If so, will they be unmerged with values duplicated like you asked for in that other thread, before the Find/Replace is implemented?

No, the column that I need to find/replace does not contain merged cells. In my final doc it is actually column J. It is only column E in the sanitized version.

 

[Peter_SSs]

Follow-up question. Assuming merged cells are not an issue, and all this shortening and Find/Replace is to get items sorted in the same order as the values in column A of 'Custom Lists', couldn't you ..

a) Forget about shortening and Find/Replace and Custom Lists
b) Put (yourself or by macro) a formula something like this in column F of 'Import' and copy down
F2: =MATCH(E2,'Custom Lists'!A$1:A$60,0)
c) Sort the data on 'Import' based on column F

 

[caj1980]

Follow-up question. Assuming merged cells are not an issue, and all this shortening and Find/Replace is to get items sorted in the same order as the values in column A of 'Custom Lists', couldn't you ..

a) Forget about shortening and Find/Replace and Custom Lists
b) Put (yourself or by macro) a formula something like this in column F of 'Import' and copy down
F2: =MATCH(E2,'Custom Lists'!A$1:A$60,0)
c) Sort the data on 'Import' based on column F

So that works and is certainly easier than find/replace. However, there is no logical definition of the Phase when it is converted to a number. I need the truncated text to display so that users of the document can still decipher the name of that phase. Am I missing something on how to sort by the numbers but display the labels? This is all happening in a Pivot table not in the Import sheet.

 

[Peter_SSs]

This is all happening in a Pivot table not in the Import sheet.

It is a bit tricky when the goalposts keep moving.

However, there is no logical definition of the Phase when it is converted to a number.

The phase (is that column E in the sanitized version?) isn't converted to a number, it is still there in its own column. A number is just placed in another column to do the sorting.

What about if you put this in the extra column instead?
=TEXT(MATCH(E2,'Custom Lists'!A$1:A$60,0),"00 ")&E2

Could you then use that in your Pivot Table so that the prefix number helps with the sorting but the column E text still appears.



... or if you do want the shorter text, try:

=TEXT(MATCH(E2,'Custom Lists'!A$1:A$60,0),"00 ")&VLOOKUP(E2,'Custom Lists'!A$1:B$60,2,0)

 

[caj1980]

It is a bit tricky when the goalposts keep moving.



The phase (is that column E in the sanitized version?) isn't converted to a number, it is still there in its own column. A number is just placed in another column to do the sorting.

What about if you put this in the extra column instead?
=TEXT(MATCH(E2,'Custom Lists'!A$1:A$60,0),"00 ")&E2

Could you then use that in your Pivot Table so that the prefix number helps with the sorting but the column E text still appears.



... or if you do want the shorter text, try:

=TEXT(MATCH(E2,'Custom Lists'!A$1:A$60,0),"00 ")&VLOOKUP(E2,'Custom Lists'!A$1:B$60,2,0)

The TEXT MATCH & VLOOKUP worked great! I like how it puts a number in front of the trunkated label, adds more clarity to the data as not all labels are always present depending on the data set. Shows when there are steps in the process not currently active. Great solution... straight through the moving uprights!

 

[Peter_SSs]

The TEXT MATCH & VLOOKUP worked great!

Good news.

.. straight through the moving uprights!