Excel formula help with multiple

%3CLINGO-SUB%20id%3D%22lingo-sub-1711844%22%20slang%3D%22en-US%22%3EExcel%20formula%20help%20with%20multiple%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1711844%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20use%20a%20formula%20to%20make%20changes%20of%20selected%20list%20to%20be%20replaced%20with%20a%20common%20name.%20Example%3A%20If%20%3CSTRONG%3Ecolumn%20G%3C%2FSTRONG%3E%20in%20the%20sample%20sheet%20contains%20information%20such%20as%20surgery%20and%26nbsp%3BTB%20%26amp%3B%20Chest%20disease%2C%20I%20want%20the%20%3CSTRONG%3Ecolumn%20E%3C%2FSTRONG%3E%26nbsp%3B%20value%20to%20be%20changed%20to%20F%20for%20all%20those%20cells%20that%20matches.%20Can%20anyone%20help%20me%20out%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20Krish%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1711844%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1711984%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%20with%20multiple%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1711984%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F783518%22%20target%3D%22_blank%22%3E%40Krishck%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELike%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1712114%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%20with%20multiple%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1712114%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3BHi%2C%20I%20am%20noticing%20numerics%20in%20the%20column%20E%2C%20I%20cannot%20see%20any%20formula.%20Is%20that%20something%20to%20do%20with%20macros.%20As%20I%20am%20looking%20to%20change%3A%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20department%20values%20are%20Surgery%20or%20TB%20%26amp%3B%20Chest%20disease%2C%20I%20want%20the%20region%20to%20be%20converted%20to%20F.%20Is%20there%20a%20formula%20for%20this%20pls%20or%20any%20query%20function.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20can%20save%20that%20and%20instead%20change%20the%20information%20again%20and%20again%2C%20I%20can%20just%20hit%20refresh%20and%20the%20data%20gets%20updated.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1713593%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%20with%20multiple%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1713593%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F783518%22%20target%3D%22_blank%22%3E%40Krishck%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20a%20Power%20Query%20based%20solution%20not%20formulas.%20Cells%20can%20have%20either%20formulas%20or%20values.%3C%2FP%3E%3CP%3ETo%20retain%20the%20values%20in%20one%20column%20and%20replace%20some%20of%20them%20based%20on%20some%20condition%20you%20need%20either%20a%20VBA%20approach%20or%20a%20Power%20Query%20approach%20to%20get%20the%20desired%20output.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20did%20in%20your%20file%2C%20I%20converted%20your%20data%20on%20test_data%20sheet%20into%20an%20Official%20Excel%20Table%20and%20renamed%20it%20as%20Data%20and%20then%20I%20imported%20this%20data%20into%20the%20Power%20Query%20Editor%20and%20added%20a%20condition%20column%20along%20with%20few%20other%20steps%20and%20loaded%20the%20data%20back%20to%20a%20new%20sheet%20called%20%22Sheet1%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20click%20on%20Queries%20%26amp%3B%20Connections%20on%20Data%20Tab%20under%20Queries%20%26amp%3B%20Connections%20group%2C%20it%20would%20open%20the%20Queries%20%26amp%3B%20Connections%20pane%20on%20right%20side%20where%20you%20would%20find%20a%20query%20called%20%22FinalData%22%20and%20if%20you%20double%20click%20this%20query%2C%20the%20Power%20Query%20editor%20will%20be%20opened%20where%20you%20will%20find%20all%20the%20Applied%20Steps%20which%20were%20performed%20to%20get%20the%20desired%20data.%3C%2FP%3E%3CP%3EYou%20can%20go%20through%20all%20those%20steps%20by%20clicking%20on%20them%20to%20see%20the%20transformation%20applied%20in%20each%20step.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi all,

 

I am trying to use a formula to make changes of selected list to be replaced with a common name. Example: If column G in the sample sheet contains information such as surgery and TB & Chest disease, I want the column E  value to be changed to "F" for all those cells that matches. Can anyone help me out?

 

Thanks Krish

3 Replies
Highlighted

@Krishck 

 

Like this?

 

Highlighted

@Subodh_Tiwari_sktneer Hi, I am noticing numerics in the column E, I cannot see any formula. Is that something to do with macros. As I am looking to change: 

If the department values are Surgery or TB & Chest disease, I want the region to be converted to F. Is there a formula for this pls or any query function.

 

 

So I can save that and instead change the information again and again, I can just hit refresh and the data gets updated. 

Highlighted

@Krishck 

It's a Power Query based solution not formulas. Cells can have either formulas or values.

To retain the values in one column and replace some of them based on some condition you need either a VBA approach or a Power Query approach to get the desired output.

 

What I did in your file, I converted your data on test_data sheet into an Official Excel Table and renamed it as Data and then I imported this data into the Power Query Editor and added a condition column along with few other steps and loaded the data back to a new sheet called "Sheet1".

 

If you click on Queries & Connections on Data Tab under Queries & Connections group, it would open the Queries & Connections pane on right side where you would find a query called "FinalData" and if you double click this query, the Power Query editor will be opened where you will find all the Applied Steps which were performed to get the desired data.

You can go through all those steps by clicking on them to see the transformation applied in each step.