Forum Discussion
Complex Text Formula?
I tried the fuzzy matching through power query but I don't think that it is producing the desired output considering the data in your data table and the data in your Specialty list on sheet.
Look at the column D with a Vlookup formula which you can copy and paste as Values in column E if you are satisfied with the output returned by the formula and then delete the column D.
You cannot have both the formulas and manual entries in the same column.
If your column A is already populated with some Medical Specialty Requested and you want to populate the cells with Cardiology if the corresponding cell in column C has Cardiology in it, you may follow this approach...
- Insert a Column before Column A.
- In the newly inserted column (which should be the column A currently), place the formula =IF(ISNUMBER(SEARCH("Cardiology",D2)),"Cardiology",IF(B2="","",B2)) in the cell A2 and then copy it down.
- Now select the range A2:A10000 ( or whatever range with the formula in column A) and copy it by pressing Ctrl+C.
- Select the cell B2 (which should be your existing Medical Specialty Requested column) and paste the copied cells as VALUES only or press the shortcut key Alt,e,s,v and hit Enter.
- Remove the Column A.
Hope that helps.
Thank you very much!
Question: I want to get all the specialties into that column. So, first Cardiology, then oncology, then radiology, etc. Would you recommend that I make different columns for each of those, and then combine them after? Oh... I probably wouldn't be able to combine them because they would be formulas and not the actual text I need? How could I do all that?Subodh_Tiwari_sktneer
If you have to look for more than one specialties in column C, you may create a list of those specialties on another sheet which can be used to match with the procedures in column C and if any of the specialties is found in there, the formula would return the correct specialty from the list.
Form you sample data, it looks like it's a partial match.
Why not share a sample file (not an image) after removing any sensitive data from it, if any, along with a list of specialty to match on another sheet? It would be easy for us to suggest a working solution then.
Thank you sir. I have scrubbed this one of sensitive data. There is another column next to F that also contains more data, but I cannot share that column for privacy issues.
If I am able to get this medical specialties column, I can then create a pivot table showing whether more patients are seeing doctors in certain specialties throughout a period of time.
I tried the fuzzy matching through power query but I don't think that it is producing the desired output considering the data in your data table and the data in your Specialty list on sheet.
Look at the column D with a Vlookup formula which you can copy and paste as Values in column E if you are satisfied with the output returned by the formula and then delete the column D.