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.

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

<tbody>
</tbody>

 

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