Complicated IF AND OR Statement help please

[FaithH]

I have a table that gives me a list of reasons why a lab cannot be reported on yet, and many times there are multiple reasons (we have a total of 19). The customer wants to know why we can't send them results yet so we send them a report with a reason, however we want to give them all of the reasons. So far, all I've managed to come up with is a long IF statement, but it only returns one of the reasons, not all of them if there are more than one. I want it to return all of the reasons, not just the one. Does anyone know how I can fix this? Thanks in advance for any help!


-- removed inline image ---


(AX2 cell here references the samples number and the reason(s) for it being on hold)

=IF(ISNUMBER(SEARCH("Reprep",Data!AX2)),"Reprep",IF(ISNUMBER(SEARCH("ICD",Data!AX2)),"Compliance-No ICD-9 Code",IF(ISNUMBER(SEARCH("DL",Data!AX2)),"DL Isomer Send Out",IF(ISNUMBER(SEARCH("Clerical",Data!AX2)),"Compliance-Requisition Verification",IF(ISNUMBER(SEARCH("Physician",Data!AX2)),"Compliance-No Requesting Physician",IF(ISNUMBER(SEARCH("RapidFire",Data!AX2)),"Additional Screening Required",IF(ISNUMBER(SEARCH("Screening",Data!AX2)),"Additional Screening Required",IF(ISNUMBER(SEARCH("Report",Data!AX2)),"Quick Report Requested",IF(ISNUMBER(SEARCH("PAF",Data!AX2)),"Compliance-No PAF",IF(ISNUMBER(SEARCH("Collection",Data!AX2)),"Compliance-No Collection Date",IF(ISNUMBER(SEARCH("Confirmation",Data!AX2)),"Compliance-Requisition Verification",IF(ISNUMBER(SEARCH("Sign",Data!AX2)),"Additional Screening Required",IF(ISNUMBER(SEARCH("Present",Data!AX2)),"Compliance-No Test Order Present","")))))))))))))

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Accession #
[/TD]
[TD]Hold Description
[/TD]
[TD]All Reasons for Hold
[/TD]
[/TR]
[TR]
[TD]OF14-94586
[/TD]
[TD]Additional Screening Required
[/TD]
[TD]Needs Screening^REPREP^D/L Isomer Send Out^Clerical Review Confirmation^Compliance - No Requesting Physician^Compliance - No Test Order Present^Compliance - No ICD-9 Code
[/TD]
[/TR]
</tbody>[/TABLE]

 

[lexxie2013]

Hi. In this particular example, what exactly are you wanting the formula to return ?

 

[FaithH]

I would like for it to return the approved description that I've given it (2nd column here) and I want it to return all reasons, not just the first one that it comes across

[TABLE="width: 592"]
<tbody>[TR]
[TD]AP Easy Hold Reason
[/TD]
[TD]Client TAT Report Hold Description[/TD]
[/TR]
[TR]
[TD]REPREP[/TD]
[TD]REPREP[/TD]
[/TR]
[TR]
[TD]D/L Isomer Send Out[/TD]
[TD]D/L Isomer Confirmation[/TD]
[/TR]
[TR]
[TD]Clerical Review Confirmation[/TD]
[TD]Compliance - Requisition Verification[/TD]
[/TR]
[TR]
[TD]Compliance - No Requesting Physician[/TD]
[TD]Compliance - No Requesting Physician[/TD]
[/TR]
[TR]
[TD]Compliance - No Test Order Present[/TD]
[TD]Compliance - No Test Order[/TD]
[/TR]
[TR]
[TD]Compliance - No ICD-9 Code[/TD]
[TD]Compliance - No ICD-9 Code[/TD]
[/TR]
[TR]
[TD]Compliance - No Collection Date[/TD]
[TD]Compliance - No Collection Date[/TD]
[/TR]
[TR]
[TD]Needs Data Before Sign Out[/TD]
[TD]Additional Screening Required[/TD]
[/TR]
[TR]
[TD]TCA Reprep[/TD]
[TD]REPREP[/TD]
[/TR]
[TR]
[TD]BARB Reprep[/TD]
[TD]REPREP[/TD]
[/TR]
[TR]
[TD]Needs Screening[/TD]
[TD]Additional Screening Required[/TD]
[/TR]
[TR]
[TD]Test Order Confirmation[/TD]
[TD]Compliance - Requisition Verification[/TD]
[/TR]
[TR]
[TD]Bath Salt REPREP[/TD]
[TD]REPREP[/TD]
[/TR]
[TR]
[TD]No PAF on file[/TD]
[TD]Compliance - No PAF[/TD]
[/TR]
[TR]
[TD]RapidFire Panel 1[/TD]
[TD]Additional Screening Required[/TD]
[/TR]
[TR]
[TD]RapidFire Panel 2[/TD]
[TD]Additional Screening Required[/TD]
[/TR]
[TR]
[TD]RapidFire Panel 3[/TD]
[TD]Additional Screening Required[/TD]
[/TR]
[TR]
[TD]RapidFire Panel 4[/TD]
[TD]Additional Screening Required[/TD]
[/TR]
[TR]
[TD]Quick Report[/TD]
[TD]Quick Report Requested[/TD]
[/TR]
</tbody>[/TABLE]

 

[lexxie2013]

Without code, the only way I've found is also with a long formula. If the reference table in your last post is in columns A and B, and the reasons for hold are in AX2 then you could try the following:


=SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Data!AX2,A2,B2),A3,B3),A4,B4),A5,B5),A6,B6),A7,B7),A8,B8),A9,B9),A10,B10),A11,B11),A12,B12),A13,B13),A14,B14),A15,B15),A16,B16),A17,B17),A18,B18),A19,B19),A20,B20),"^"," ")

 

[FaithH]

It appears to work, except that it pulls in reasons that we don't want the client to have. This is pulling in all reasons, and there are some reasons that legally we cannot give them. I need it to leave out reasons that aren't included in the list.

 

[lexxie2013]

OK. How many possible reasons are there ? If not too many more, then you could add them to column A in the reference table, with column B blank and adapt the formula for the extra number of rows.

 

[Comfy]

There's no formula way to concatenate all the results, you could use a UDF but would obviously required the use of VBA.

Excel 2010
	A	B	C
1	AP Easy Hold Reason	Client TAT Report Hold Description
2	REPREP	REPREP
3	D/L Isomer Send Out	D/L Isomer Confirmation
4	Clerical Review Confirmation	Compliance - Requisition Verification
5	Compliance - No Requesting Physician	Compliance - No Requesting Physician
6	Compliance - No Test Order Present	Compliance - No Test Order
7	Compliance - No ICD-9 Code	Compliance - No ICD-9 Code
8	Compliance - No Collection Date	Compliance - No Collection Date
9	Needs Data Before Sign Out	Additional Screening Required
10	TCA Reprep	REPREP
11	BARB Reprep	REPREP
12	Needs Screening	Additional Screening Required
13	Test Order Confirmation	Compliance - Requisition Verification
14	Bath Salt REPREP	REPREP
15	No PAF on file	Compliance - No PAF
16	RapidFire Panel 1	Additional Screening Required
17	RapidFire Panel 2	Additional Screening Required
18	RapidFire Panel 3	Additional Screening Required
19	RapidFire Panel 4	Additional Screening Required
20	Quick Report	Quick Report Requested
21
22
23
24
25
26
27
28
29	Accession #	Hold Description	All Reasons for Hold
30	OF14-94586	Additional Screening Required	Needs Data Before Sign Out
31			Needs Screening
32			RapidFire Panel 1
33			RapidFire Panel 2
34			RapidFire Panel 3
35			RapidFire Panel 4
Sheet1

Cell Formulas
Range	Formula
C30	{=IF(ISERROR(INDEX($A$1:$B$20,SMALL(IF($B$1:$B$20=$B$30,ROW($B$1:$B$20)),ROW(1:1)),1)),"",INDEX($A$1:$B$20,SMALL(IF($B$1:$B$20=$B$30,ROW($B$1:$B$20)),ROW(1:1)),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[FaithH]

There are 34 reasons. Your suggestion may work...

 

[FaithH]

Comfy - unfortunately I am a true beginner with Macros and VBA. I've only done a couple basic ones, and only 1 has worked

The All Reasons for Hold is on another tab within the workbook and is all in one line, separated by carets.

I'm trying to automate as much as I can since others will be using this spreadsheet to send reports.

 

[lexxie2013]

good luck faith. Also, you could change the "^"," " to "^",", " if you wanted the reasons comma-separated.