SOLVED

Complex Text Formula?

Copper Contributor

I am crunching some data to find patterns. Of course people stopped filling in data in a certain area, and I need that data to get a clear picture. Let's pretend Medical Specialty requested is column A, Modified is column B, etc.

Basically I need something like, "If the word cardiology is in column C1, put the word cardiology in A1. If not, do nothing." How would you do this?  There's probably 10,000 lines and doing it manually isn't an option. Also, I'm in Excel 2013.

 

Capture.PNG

6 Replies

@Work4Rose 

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...

 

  1. Insert a Column before Column A.
  2. 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.
  3. Now select the range A2:A10000 ( or whatever range with the formula in column A) and copy it by pressing Ctrl+C.
  4. 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.
  5. 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 

Also, can you help me figure out what I did wrong here? 

 

clipboard_image_0.png

@Subodh_Tiwari_sktneer 

@Work4Rose 

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.

@Subodh_Tiwari_sktneer

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.

best response confirmed by Work4Rose (Copper Contributor)
Solution

@Work4Rose 

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.

 

 

1 best response

Accepted Solutions
best response confirmed by Work4Rose (Copper Contributor)
Solution

@Work4Rose 

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.

 

 

View solution in original post