Find & Replace with Open Cells for New Entries & Column Sorting

Friends,

I hope everyone is well and happy.

Now, this thread has been partially solved previously:

Find string of text within a cell and then replace the cell with new text
https://www.excelforum.com/excel-gen...-new-text.html

I would like to extend my query a bit more in depth.

Just to briefly recap the situation, the purpose is to find a sub-partial text in a column but then return another cell text related to the found cell.

There are two new queries that I would like to request.

First, I would like the end result column to be auto sorted alphabetically in column D.

Second, the column of find text, column B, should extend and create on its own new entries once there are new entries added to the main column in column A.

If there are new entries found, I will update column C what the replacement should be in the end result column.

Please see attached the file for better understanding.

I went a completely different route to what the original thread solved for the first problem.

If you are using Excel 2010, I put several helper columns in.

Column B is a formula removing duplicates and creating an ability to look up a unique list.
=IF(COUNTIF(A$2:A2,A2)=1,MAX(B$1:B1)+1,"")

Column C is simply looking up the row of each result and compiling that unique list into a single column.
=IFERROR(INDEX(A:A,MATCH(ROW()-1,$B:$B,0)),"")

There is no logical way I could find to replace text without a way of knowing what string is replacing what there are 2 different 190 numbers for example.
So I created a table of what the "old text" would be and what it needs to be changed to in column L and M
That is just a simple table with no formulas.

Column D
=IFERROR(INDEX(M:M,MATCH(C2,$L:$L,0)),"")
We are using that table to look up the "old text" and it will pull the result of what the new text should be.

Columns E and F are helper columns
It's an alphabetical "ranking" formula that
So in Column E
=IF(D2="","",COUNTIF($D:$D,"<="&D2))
and in Column F
=IFERROR(RANK(E2,E:E,1),"")

Then in column G we can look up the sorting logic Columns E and F provided.
=IFERROR(INDEX(D:D,MATCH(ROW()-1,F:F,0)),"")

You can now hide B, E, F, L and M.

This maybe unconventional and someone much more smarter than I may be able to cut down on the number of helper columns used.
But from what I gathered you created a unique list from Column A.
Column C is a unique list of column A
Column D replaces text in Column C from the table in Column M

Column G is sorted alphabetically.
if it needs to be sorted any other way, Values will have to be assigned in the Table in column N giving a hard number to how it should be sorted.

Maybe so with PQ

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Main Column", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Main Column"},Table5,{"If Text Found"},"Table5",JoinKind.LeftOuter),
    #"Expanded Table5" = Table.ExpandTableColumn(#"Merged Queries", "Table5", {"Replace With"}, {"Replace With"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Table5",{"Replace With", "Main Column"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Replace With", Order.Ascending}})
in
    #"Sorted Rows"

Thank you very much for the format.

However, I was looking for formulas and functions to automat my whole query.

Also, when I try to add new entries into the table that you have provided, nothing seem to update in either of the columns.

Anyways, I am really looking for formulas to update and regulate my data.

Wow, that is great effort!

Those columns L and M are basically something that I will have to update manually, is there really no other way? :/

Couldn't those be left out and rather columns C and D be used instead?

Column C could get the new entry, which is what you have done, and then I would update column D myself, which is what I have said I will do.

And yes, column B is basically a unique list of entries that are repeated in the main column which is why it's great that you have used a formula to identify any new unique cell and add to the end.

I wouldn't really mind all those helper columns if I had to.

What I really love about the whole format is that you didn't use CSE.

I would love it if there is a shorter version to all this because it will be really difficult for to trace down if there is a mistake anywhere that might be inevitable to occur.

Anyways, thank you very much, I really, really appreciate it.

Data-->Refresh All

No, it's not working.

I have tried adding new text, or even replacing existing text with new, to the main columns, A and E.

As a matter of fact, not only is it not working, it's also giving me some serious security errors/warning when I try to enable the Data Connection thing. :D But it is concerning, though.

Thank you again for your help!

If you MS Office 2010 download Power Query.

Hi Major Johnson,
If there was some rhyme or reason to why text would be replaced for example a 190 always gets replaced with XYZ, then there may be a way.
So yes the table will always have to be updated and maintained as the report grows. because there is no way to tell excel "Text one should be replaced by Text two" without either making a table with those rules or making an extremely long formula that will have to be updated every time something new is added. But as it stands looking at your example, it doesn't appear the numbers have any influence on how something is being sorted.

My apologies for the late response, I didn't get notification on my email for the replies.

I thought no one is really bothering to resolve this issue.

Anyways, in regard to installing PQ, I can't because I have a laptop from work where I have restrictions when it comes to downloading and installing new things.

See, the ID codes partially resemble full description which is why it makes it really difficult.

Which is why I have accumulated unique values and the cells of full desription that are related to it.

I really don't know how else to do it, really. :/

I think I might have to resort to helper's columns like
you have mentioned and shown which eventually gets the result the way I proposed and desired...