Auto Find/Replace Multiples

caj1980

Board Regular
Joined
Oct 23, 2013
Messages
108
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]
 
Last edited:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top