Finding multiple parts of a string in a cell in a column, and returning the column find

[RobG]

I have two reports that have similar data, but not exact. I need to tak the information in report A, find how it is read in report B, and then show the result in report B so I can look up that value.

For example
In report A the data reads "canned in water tuna"
In report B the data reads "tuna, canned in water"

More data would be
Report A "black ground pepper"
Report B "pepper, black ground"

I have added report A to Column A, and report B to column B in the same spread sheet for ease.

I found this formula =IF(AND(ISNUMBER(SEARCH(TEXTSPLIT(A1,," "),B1))),"Match","No Match") that works if the cells are next to each other. When I try to expand the search radius to B:B the formula fails.

I would also need to change "Match", "No Match" to Result in column B, "No Match"

Any help here is appreciated

 

[rondeondo]

Hi Rob,
Are the two examples you have provided typical of all of the data? ie could you programatically re-arrange one to match the other, so to conver Rep A to Rep B, put the last word first and add a comma? That can be done farly easily in the value to search portion of a lookup/vlookup/xlookup depending on your needs.
Alternately do you have any idenitifiers you could use that are independant of the presented description? Far more simple to compare non-variable identifiers than descriptions.

a way to achieve the swap of description as per your example:

=SUBSTITUTE(MID(SUBSTITUTE(A11," ","|",LEN(A11)-LEN(SUBSTITUTE(A11," ",""))),FIND("|",SUBSTITUTE(A11," ","|",LEN(A11)-LEN(SUBSTITUTE(A11," ",""))))+1,99)&", "&LEFT(SUBSTITUTE(A11," ","|",LEN(A11)-LEN(SUBSTITUTE(A11," ",""))),FIND("|",SUBSTITUTE(A11," ","|",LEN(A11)-LEN(SUBSTITUTE(A11," ",""))))),"|","")

It looks bad but it work for an example where the description in report A is in cell A11.
Alternately you can simplify it somewhat by using a few cells
The formula works by first counting the number of spaces in the description, then putting in a | in place of the space before the last word, then breaking the description at the | character, using mid and left, and throwing in the comma.
Much easier if you wanted to go back from Report B description to Report A:

=MID(A12,FIND(",",A12)+2,99)&" "&LEFT(A12,FIND(",",A12)-1)

Where the report B description is in A12

hope this helps someone