Excel Logic under single cell

Hi All,

I am trying to write excel logic's based on different criteria to get the output. However i am not able to write all conditions under one cell to get the output. Below are conditions i have written :

Condition1:
=IF(T2="OR_L80419_Enterprise Care Solutions",IF(AR2="MO_A26","R&S",IF(AND(AR2="MO_A23",AL2="Muse"),"MUSE",IF(AND(AR2="MO_A23",AL2="Stress"),"STRESS",IF(AND(AR2="MO_A23",AL2="Holter"),"HOLTER",IF(AND(AR2="MO_A23",R2="CC_GLP_03508F"),"SSA",IF(AR2="MO_A23","Carts",IF(AP2="MO_726","Cuffs",IF(R2="CC_GLP_033088","SPO2",IF(U2="OR_J80446_ECS Specialty Monitoring","Specialty",IF(U2="OR_J80428_Enterprise Care Solutions Other","ECS HQ",IF(AND(U2="OR_J80445_Enterprise Care Solutions",OR(AL2="Central Stations",AL2="Communications and Informatics Default")),"CIC",IF(AND(U2="OR_J80445_Enterprise Care Solutions",OR(AL2="Enterprise Access",AL2="Interfacing",AL2="Networking",AL2="OEM Partnerships",AL2="Telemetry",AL2="Connectivity",AL2="ASCOM",AL2="Airstrip C&I")),"Wireless","Hardwire")))))))))))))

Condition2:
=IF(T2="OR_J80433_HCS-Dist-LCS-MIC",IF(AR2="MO_A17","NICU",IF(OR(AM2="MIC Maternal Fetal Care",AM2="ECG Monitoring"),"Coro",IF(AND(L2="CO_GLP_031010",AM2="MIC Default"),"Coro","ME"))))

Condition3:
=IF(T2="OR_L80421_HCS Dist-LCS-Other",IF(R2="CC_GLP_033135","CRO"))

Condition4:
=IF(T2="OR_L80854_HCS-Dist-LCS-Anesthesia",IF(R2="CC_GLP_033367","B2B",IF(AP2="MO_751","Anesth Delivery","OR Monitoring")))

I am not able to merge all the conditions under one cell. I request all the experts out here to help me to get the output under one column. I need results which mirror column AV (manually updated) in the attached spreadsheet.

I have also attached an excel spreadsheet where i have written 4 conditions with the data.

Looking forward for the positive response!

Thanks,
Ganesh
Screen-shot.docx
Test.xlsb
Ganesh VijaykumarAsked:
Who is Participating?
 
Ejgil HedegaardCommented:
If all combinations are available in the 4 conditions, something is wrong with the specification in column AV, or with one of the condition formulas,

But if the result does not match the expected output in column AV, it is not very useful.
That's why I suggested something else, combining the input to the expected result, because then it fit.
I did not dig into why the conditions did not fit the expected, until now.

The missing results are Coro, NICU and ME, and they all come from Condition2.
But Condition2 returns FALSE for all rows.
The match in the formula for column T is OR_J80433_HCS-Dist-LCS-MIC, but that does not exist in column T.
The nearest are OR_L80855_HCS-Dist-LCS-MIC.

Using that the combined formula for row 2 is
=IF(T2="OR_L80419_Enterprise Care Solutions",IF(AR2="MO_A26","R&S",IF(AND(AR2="MO_A23",AL2="Muse"),"MUSE",IF(AND(AR2="MO_A23",AL2="Stress"),"STRESS",IF(AND(AR2="MO_A23",AL2="Holter"),"HOLTER",IF(AND(AR2="MO_A23",R2="CC_GLP_03508F"),"SSA",IF(AR2="MO_A23","Carts",IF(AP2="MO_726","Cuffs",IF(R2="CC_GLP_033088","SPO2",IF(U2="OR_J80446_ECS Specialty Monitoring","Specialty",IF(U2="OR_J80428_Enterprise Care Solutions Other","ECS HQ",IF(AND(U2="OR_J80445_Enterprise Care Solutions",OR(AL2="Central Stations",AL2="Communications and Informatics Default")),"CIC",IF(AND(U2="OR_J80445_Enterprise Care Solutions",OR(AL2="Enterprise Access",AL2="Interfacing",AL2="Networking",AL2="OEM Partnerships",AL2="Telemetry",AL2="Connectivity",AL2="ASCOM",AL2="Airstrip C&I")),"Wireless","Hardwire")))))))))))),IF(T2="OR_L80855_HCS-Dist-LCS-MIC",IF(AR2="MO_A17","NICU",IF(OR(AM2="MIC Maternal Fetal Care",AM2="ECG Monitoring"),"Coro",IF(AND(L2="CO_GLP_031010",AM2="MIC Default"),"Coro","ME"))),IF(T2="OR_L80421_HCS Dist-LCS-Other",IF(R2="CC_GLP_033135","CRO"),IF(T2="OR_L80854_HCS-Dist-LCS-Anesthesia",IF(R2="CC_GLP_033367","B2B",IF(AP2="MO_751","Anesth Delivery","OR Monitoring"))))))

Open in new window


And then the result fit the expected.

So with a little detour, I think we reached the target.
0
 
Saqib Husain, SyedEngineerCommented:
How do you get the result of row 115?
0
 
Ejgil HedegaardCommented:
It is possible to put all 4 conditions into 1 formula, but as Saqib states, some rules are missing.
The 2 values in column T not defined in the 4 condition columns, or more if something has been forgotten making the complex formula.

It is very difficult to make condition formulas with such complexity, and easy to make an error or miss something.
So I would use a different approach, which is easier to maintain.

In the attached file is a condition sheet, defining the Result for each of the 4057 combinations of the 8 columns used to get the result.
Then an Index/Match formula search the conditions, and returns the result in column AW.
When there are new data and the result returns NA, the missing combination and result, can just be added at the bottom of the list.
To speed up calculation I have sorted the list on the number of match, to get the most used at the top.
Test-conditions.xlsb
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Ganesh VijaykumarAuthor Commented:
@Ejgil Hedegaard : Wow, the approach and solution looks very easy and accurate. Thanks a ton for the response.

Could you please help me understand how did you get 4057 combinations out of 378066 rows. It will help me to add missing combinations when there is new data in future. And also column I "result & K "Number of match" how it is calculated.

It would be of great help if you could let me know the steps to get the results with this approach. It will help me to apply the same for new data in future.

Will be awaiting your response!

Thanks,
Ganesh
0
 
Ejgil HedegaardCommented:
Copied all rows in the table in the 8 condition columns used in the formulas (T, AR, AL, R, AP, U, AM, L) and the result columns (AV) to a new workbook.
I just used the columns in the same order as they appear in the formulas.

Used the Remove duplicates function (Data tab), resulting in the list with 4057 rows.

Copied the result to the Conditions sheet, and deleted the temporary workbook
The reason to use a temporary workbook is that Excel not always clean up when data is removed, and use the last row ever used as last row now, which makes the workbook much larger than needed.

Column I, Result is not calculated. It is the values in your result column AV, so when new combinations are added to the conditions list, you must determine what the result of that combination must be.

To get the number of match for a specific condition, the Countif function is used.
In column AW the text in the 8 conditions are concatenated, =T2&AR2&AL2&R2&AP2&U2&AM2&L2, copied down.
The Mix column (J) is text concatenation of the 8 columns A:H on the Conditions sheet.
In column K the countif function =COUNTIF('GL Details'!AW:AW,J2) is used, copied down.
It takes a couple of minutes to calculate.
Then copy the result, and paste special as values.
Sorting the list on "Number of match", descending, put the most used at the top, so the match search faster.
The column is only informative, and can be deleted.

Finally added the Index/Match functions to column AW.

Rearranging the condition list with the most used at the top is only important to speed up calculation.
But with +300k rows with formulas, I think you will benefit in setting calculation to manual, when you are working with the result, like filtering, and only use automatic calculation (or calculate by F9) on update, and then a few minutes more does not matter.
It also takes time to calculate to find the most used.
0
 
Ganesh VijaykumarAuthor Commented:
@Ejgil Hedegaard : Thank you very much for the response!

Column I, Result is not calculated. It is the values in your result column AV, so when new combinations are added to the conditions list, you must determine what the result of that combination must be.

With reference to the above point, The results under column AV is updated manually based on the preset criteria and it is a tedious task and takes lots of time for determining results for more than 500 thousand lines every time.

Hence i wrote those four conditions to determine the results automatically and could not write them in single cell.

Could we right a logic/condition in one column to mirror column AV (manually updated).

If i have to write determine what the result of any combination must be and update it manually under column AV. There is no need to write a logic. The whole purpose of writing logic or conditions is to avoid manual intervention. The moment i update new raw data it should calculate automatically and provide me desired results under one column.

Hope i am clear; all the combinations are available in those 4 conditions, it would of great help if you could help me finding a solution for this requirement.

Will be awaiting your positive response!

Thanks,
Ganesh
0
 
Ganesh VijaykumarAuthor Commented:
Oops Oh yeah! I didn't realize there is some mistake in Condition2. Thanks a ton for all your help! Yippe... its working now... Thanks Again!

Have a great day!
Ganesh
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.