Hello,
I am trying to create a nested IF statement that looks at several different columns in a database CSV output. The purpose is to get the event type classifications that are shown in several different columns into one single column. This allows for easy counts/rates via a pivot table. Essentially, what I get each week is a "raw" CSV output that resembles below:
(Column Names from database in all caps)
FIRE WATER AIR EARTH RISK
Yes No No No 1
No Yes No No 2
No No Yes No 3
No No No No 3
What I would like to do is get the above into one column that I would insert called Type of Material:
(My Column Name in all caps)
Type of Material
FIRE
WATER
AIR
OTHER
It is the OTHER classification that I am struggling with. I compiled a IF macro that is able to go through the various columns and, if a given column is Yes, it outputs accordingly with the type of material name (ie-FIRE) via a command like =IF((FIRE)="Yes", "FIRE", IF(WATER)="Yes", "WATER" and so on. I have not had any luck trying to create (what I imagine will be) a IF(AND function that would look at each column and, where No is detected, move on to the next, eventually returning a "Other" for any row that has all No values AND where the risk is 3 or above. I have tried IF(AND(FIRE)="No", IF(WATER)="No", IF(AIR)="No", IF(EARTH)="No", (Risk)>2, "OTHER")))) to no luck. Really at a loss. Does anyone have any idea how this might be coded to perform all the functions in the same command (ie-interrogate each cell and, where a Yes is detected return the appropriate value, where only No is detected and the risk is greater than 2, return Other.
One more question....
The database also incorporates the use of drop down boxes in some areas, which then populate a given column with any of a number of possible choices. Is there any way to build a function that would look at the contents of a cell and output an appropriate value? For example:
(database Column Name in All Caps-empty spaces would be blank cells on database output)
Patient Illness/Injury
Illness
Injury
Illness
Illness
Injury
Can a function (IF?) look at the value of a cell (in this case either Illness or Injury or the cell could be blank), determine if it was an Illness or Injury event and output Patient Illness or Patient Injury? If so, can this function be linked into a longer string of functions looking at many different columns to output a value? For instance:
(database Column Names in all caps-in the example below Patient and Doctor Illness/Injury would be drop down boxes while Equipment Breakage and Pedestrian Accident would be check boxes on the user submission form (No is returned anytime a value is not selected on non-drop down boxes)
Patient Illness/Injury Pedestrian Accident EquipmentBreakage
No Yes
Illness No No
Yes No
Injury No No
Illness No No
Illness No No
No Yes
Yes No
Injury No No
No Yes
The column I would like to create based on the above would look like:
CAUSE OF EVENT
Equipment Breakage
Patient Illness
Pedestrian Accident
Patient Injury
Patient Illness
etc....
Does anyone know if this can be done?
Thank You,
Patrick
I am trying to create a nested IF statement that looks at several different columns in a database CSV output. The purpose is to get the event type classifications that are shown in several different columns into one single column. This allows for easy counts/rates via a pivot table. Essentially, what I get each week is a "raw" CSV output that resembles below:
(Column Names from database in all caps)
FIRE WATER AIR EARTH RISK
Yes No No No 1
No Yes No No 2
No No Yes No 3
No No No No 3
What I would like to do is get the above into one column that I would insert called Type of Material:
(My Column Name in all caps)
Type of Material
FIRE
WATER
AIR
OTHER
It is the OTHER classification that I am struggling with. I compiled a IF macro that is able to go through the various columns and, if a given column is Yes, it outputs accordingly with the type of material name (ie-FIRE) via a command like =IF((FIRE)="Yes", "FIRE", IF(WATER)="Yes", "WATER" and so on. I have not had any luck trying to create (what I imagine will be) a IF(AND function that would look at each column and, where No is detected, move on to the next, eventually returning a "Other" for any row that has all No values AND where the risk is 3 or above. I have tried IF(AND(FIRE)="No", IF(WATER)="No", IF(AIR)="No", IF(EARTH)="No", (Risk)>2, "OTHER")))) to no luck. Really at a loss. Does anyone have any idea how this might be coded to perform all the functions in the same command (ie-interrogate each cell and, where a Yes is detected return the appropriate value, where only No is detected and the risk is greater than 2, return Other.
One more question....
The database also incorporates the use of drop down boxes in some areas, which then populate a given column with any of a number of possible choices. Is there any way to build a function that would look at the contents of a cell and output an appropriate value? For example:
(database Column Name in All Caps-empty spaces would be blank cells on database output)
Patient Illness/Injury
Illness
Injury
Illness
Illness
Injury
Can a function (IF?) look at the value of a cell (in this case either Illness or Injury or the cell could be blank), determine if it was an Illness or Injury event and output Patient Illness or Patient Injury? If so, can this function be linked into a longer string of functions looking at many different columns to output a value? For instance:
(database Column Names in all caps-in the example below Patient and Doctor Illness/Injury would be drop down boxes while Equipment Breakage and Pedestrian Accident would be check boxes on the user submission form (No is returned anytime a value is not selected on non-drop down boxes)
Patient Illness/Injury Pedestrian Accident EquipmentBreakage
No Yes
Illness No No
Yes No
Injury No No
Illness No No
Illness No No
No Yes
Yes No
Injury No No
No Yes
The column I would like to create based on the above would look like:
CAUSE OF EVENT
Equipment Breakage
Patient Illness
Pedestrian Accident
Patient Injury
Patient Illness
etc....
Does anyone know if this can be done?
Thank You,
Patrick
