Advanced Filtering, HLookup or VLookup to display data

[la998]

I'm not an Excel poweruser and I may have bitten off more than I can chew, but here is my dilemma.

I have filtered down raw data to only show providers that belong to multiple departments. There is a flag for each of these providers indicating Y/N in two different columns. I need to be able to display the rows where a unique provider has ACD=Y, Pro=N in one row AND ACD=N, Pro=Y in a subsquent row.

In other words,

A B C D
Provider 1 Pediatrics N Y
Provider 1 Pedi NB Y N

I would attach a sample of the data, but I'm not sure how to attach here. Thanks!!!

 

[Robert Mika]

I'm not an Excel poweruser and I may have bitten off more than I can chew, but here is my dilemma.

I have filtered down raw data to only show providers that belong to multiple departments. There is a flag for each of these providers indicating Y/N in two different columns. I need to be able to display the rows where a unique provider has ACD=Y, Pro=N in one row AND ACD=N, Pro=Y in a subsquent row.

In other words,

A B C D
Provider 1 Pediatrics N Y
Provider 1 Pedi NB Y N

I would attach a sample of the data, but I'm not sure how to attach here. Thanks!!!

Hello and welcome.
To post data use method in my signature.

 

[la998]

[TABLE="width: 536"]
<COLGROUP><COL style="WIDTH: 129pt; mso-width-source: userset; mso-width-alt: 6290" width=172><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><COL style="WIDTH: 179pt; mso-width-source: userset; mso-width-alt: 8740" width=239><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" width=40><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3876" width=106><TBODY>[TR]
[TD="class: xl65, width: 172, bgcolor: #bfd2e2"]Provider Description[/TD]
[TD="class: xl65, width: 119, bgcolor: #bfd2e2"]Role Description[/TD]
[TD="class: xl65, width: 239, bgcolor: #bfd2e2"]Department Description[/TD]
[TD="class: xl65, width: 40, bgcolor: #bfd2e2"]Is ACD[/TD]
[TD="class: xl65, width: 39, bgcolor: #bfd2e2"]Is PBO[/TD]
[TD="class: xl65, width: 106, bgcolor: #bfd2e2"]Cost Center Number[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 1[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER[/TD]
[TD="class: xl66, bgcolor: transparent"]PEDI HEMATOLOGY/ONCOLOGY[/TD]
[TD="class: xl66, bgcolor: transparent"]Y[/TD]
[TD="class: xl66, bgcolor: transparent"]N[/TD]
[TD="class: xl66, bgcolor: transparent"]141[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 1[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER[/TD]
[TD="class: xl66, bgcolor: transparent"]YAWKEY 6A[/TD]
[TD="class: xl66, bgcolor: transparent"]Y[/TD]
[TD="class: xl66, bgcolor: transparent"]N[/TD]
[TD="class: xl66, bgcolor: transparent"]61[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 2[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER[/TD]
[TD="class: xl66, bgcolor: transparent"]HEM MALIGNANCIES[/TD]
[TD="class: xl66, bgcolor: transparent"]Y[/TD]
[TD="class: xl66, bgcolor: transparent"]N[/TD]
[TD="class: xl66, bgcolor: transparent"]66[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 2[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER[/TD]
[TD="class: xl66, bgcolor: transparent"]LYMPHOMA[/TD]
[TD="class: xl66, bgcolor: transparent"]Y[/TD]
[TD="class: xl66, bgcolor: transparent"]N[/TD]
[TD="class: xl66, bgcolor: transparent"]66[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 3[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER[/TD]
[TD="class: xl66, bgcolor: transparent"]PAIN CENTER[/TD]
[TD="class: xl66, bgcolor: transparent"]Y[/TD]
[TD="class: xl66, bgcolor: transparent"]N[/TD]
[TD="class: xl66, bgcolor: transparent"]503[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 3[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER[/TD]
[TD="class: xl66, bgcolor: transparent"]PAIN MEDICINE WALTHAM - PO[/TD]
[TD="class: xl66, bgcolor: transparent"]N[/TD]
[TD="class: xl66, bgcolor: transparent"]Y[/TD]
[TD="class: xl66, bgcolor: transparent"]132[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 4[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER[/TD]
[TD="class: xl66, bgcolor: transparent"]ORTHO PEDIATRICS[/TD]
[TD="class: xl66, bgcolor: transparent"]N[/TD]
[TD="class: xl66, bgcolor: transparent"]Y[/TD]
[TD="class: xl66, bgcolor: transparent"]14[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 4[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER[/TD]
[TD="class: xl66, bgcolor: transparent"]ORTHOPAEDICS AT NWH[/TD]
[TD="class: xl66, bgcolor: transparent"]N[/TD]
[TD="class: xl66, bgcolor: transparent"]Y[/TD]
[TD="class: xl66, bgcolor: transparent"]14[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 5[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER[/TD]
[TD="class: xl66, bgcolor: transparent"]GI CC HEMATOLOGY/ONCOLOGY[/TD]
[TD="class: xl66, bgcolor: transparent"]Y[/TD]
[TD="class: xl66, bgcolor: transparent"]N[/TD]
[TD="class: xl66, bgcolor: transparent"]69[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 5[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER[/TD]
[TD="class: xl66, bgcolor: transparent"]GI ONC WANG MULTI[/TD]
[TD="class: xl66, bgcolor: transparent"]N[/TD]
[TD="class: xl66, bgcolor: transparent"]Y[/TD]
[TD="class: xl66, bgcolor: transparent"]1300[/TD]
[/TR]
</TBODY>[/TABLE]

 

[la998]

[TABLE="width: 536"]
<TBODY>[TR]
[TD="class: xl65, width: 172, bgcolor: #bfd2e2"]Provider Description
[/TD]
[TD="class: xl65, width: 119, bgcolor: #bfd2e2"]Role Description
[/TD]
[TD="class: xl65, width: 239, bgcolor: #bfd2e2"]Department Description
[/TD]
[TD="class: xl65, width: 40, bgcolor: #bfd2e2"]Is ACD
[/TD]
[TD="class: xl65, width: 39, bgcolor: #bfd2e2"]Is PBO
[/TD]
[TD="class: xl65, width: 106, bgcolor: #bfd2e2"]Cost Center Number
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 1
[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER
[/TD]
[TD="class: xl66, bgcolor: transparent"]PEDI HEMATOLOGY/ONCOLOGY
[/TD]
[TD="class: xl66, bgcolor: transparent"]Y
[/TD]
[TD="class: xl66, bgcolor: transparent"]N
[/TD]
[TD="class: xl66, bgcolor: transparent"]141
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 1
[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER
[/TD]
[TD="class: xl66, bgcolor: transparent"]YAWKEY 6A
[/TD]
[TD="class: xl66, bgcolor: transparent"]Y
[/TD]
[TD="class: xl66, bgcolor: transparent"]N
[/TD]
[TD="class: xl66, bgcolor: transparent"]61
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 2
[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER
[/TD]
[TD="class: xl66, bgcolor: transparent"]HEM MALIGNANCIES
[/TD]
[TD="class: xl66, bgcolor: transparent"]Y
[/TD]
[TD="class: xl66, bgcolor: transparent"]N
[/TD]
[TD="class: xl66, bgcolor: transparent"]66
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 2
[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER
[/TD]
[TD="class: xl66, bgcolor: transparent"]LYMPHOMA
[/TD]
[TD="class: xl66, bgcolor: transparent"]Y
[/TD]
[TD="class: xl66, bgcolor: transparent"]N
[/TD]
[TD="class: xl66, bgcolor: transparent"]66
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 3
[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER
[/TD]
[TD="class: xl66, bgcolor: transparent"]PAIN CENTER
[/TD]
[TD="class: xl66, bgcolor: transparent"]Y
[/TD]
[TD="class: xl66, bgcolor: transparent"]N
[/TD]
[TD="class: xl66, bgcolor: transparent"]503
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 3
[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER
[/TD]
[TD="class: xl66, bgcolor: transparent"]PAIN MEDICINE WALTHAM - PO
[/TD]
[TD="class: xl66, bgcolor: transparent"]N
[/TD]
[TD="class: xl66, bgcolor: transparent"]Y
[/TD]
[TD="class: xl66, bgcolor: transparent"]132
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 4
[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER
[/TD]
[TD="class: xl66, bgcolor: transparent"]ORTHO PEDIATRICS
[/TD]
[TD="class: xl66, bgcolor: transparent"]N
[/TD]
[TD="class: xl66, bgcolor: transparent"]Y
[/TD]
[TD="class: xl66, bgcolor: transparent"]14
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 4
[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER
[/TD]
[TD="class: xl66, bgcolor: transparent"]ORTHOPAEDICS AT NWH
[/TD]
[TD="class: xl66, bgcolor: transparent"]N
[/TD]
[TD="class: xl66, bgcolor: transparent"]Y
[/TD]
[TD="class: xl66, bgcolor: transparent"]14
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 5
[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER
[/TD]
[TD="class: xl66, bgcolor: transparent"]GI CC HEMATOLOGY/ONCOLOGY
[/TD]
[TD="class: xl66, bgcolor: transparent"]Y
[/TD]
[TD="class: xl66, bgcolor: transparent"]N
[/TD]
[TD="class: xl66, bgcolor: transparent"]69
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 5
[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER
[/TD]
[TD="class: xl66, bgcolor: transparent"]GI ONC WANG MULTI
[/TD]
[TD="class: xl66, bgcolor: transparent"]N
[/TD]
[TD="class: xl66, bgcolor: transparent"]Y
[/TD]
[TD="class: xl66, bgcolor: transparent"]1300
[/TD]
[/TR]
</TBODY>[/TABLE]

This is a small fraction of the list, but essentially I'm trying to see those providers who do both ACD and PO (not necessarily for the same department). So provider 4 would not meet the criteria because they are Pro only, but provider 5 would because they do both.

 

[Robert Mika]

[TABLE="width: 536"]
[TR]
[TD="class: xl65, width: 172, bgcolor: #bfd2e2"]Provider Description[/TD]
[TD="class: xl65, width: 119, bgcolor: #bfd2e2"]Role Description[/TD]
[TD="class: xl65, width: 239, bgcolor: #bfd2e2"]Department Description[/TD]
[TD="class: xl65, width: 40, bgcolor: #bfd2e2"]Is ACD[/TD]
[TD="class: xl65, width: 39, bgcolor: #bfd2e2"]Is PBO[/TD]
[TD="class: xl65, width: 106, bgcolor: #bfd2e2"]Cost Center Number[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 1[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER[/TD]
[TD="class: xl66, bgcolor: transparent"]PEDI HEMATOLOGY/ONCOLOGY[/TD]
[TD="class: xl66, bgcolor: transparent"]Y[/TD]
[TD="class: xl66, bgcolor: transparent"]N[/TD]
[TD="class: xl66, bgcolor: transparent"]141[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 1[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER[/TD]
[TD="class: xl66, bgcolor: transparent"]YAWKEY 6A[/TD]
[TD="class: xl66, bgcolor: transparent"]Y[/TD]
[TD="class: xl66, bgcolor: transparent"]N[/TD]
[TD="class: xl66, bgcolor: transparent"]61[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 2[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER[/TD]
[TD="class: xl66, bgcolor: transparent"]HEM MALIGNANCIES[/TD]
[TD="class: xl66, bgcolor: transparent"]Y[/TD]
[TD="class: xl66, bgcolor: transparent"]N[/TD]
[TD="class: xl66, bgcolor: transparent"]66[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 2[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER[/TD]
[TD="class: xl66, bgcolor: transparent"]LYMPHOMA[/TD]
[TD="class: xl66, bgcolor: transparent"]Y[/TD]
[TD="class: xl66, bgcolor: transparent"]N[/TD]
[TD="class: xl66, bgcolor: transparent"]66[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 3[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER[/TD]
[TD="class: xl66, bgcolor: transparent"]PAIN CENTER[/TD]
[TD="class: xl66, bgcolor: transparent"]Y[/TD]
[TD="class: xl66, bgcolor: transparent"]N[/TD]
[TD="class: xl66, bgcolor: transparent"]503[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 3[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER[/TD]
[TD="class: xl66, bgcolor: transparent"]PAIN MEDICINE WALTHAM - PO[/TD]
[TD="class: xl66, bgcolor: transparent"]N[/TD]
[TD="class: xl66, bgcolor: transparent"]Y[/TD]
[TD="class: xl66, bgcolor: transparent"]132[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 4[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER[/TD]
[TD="class: xl66, bgcolor: transparent"]ORTHO PEDIATRICS[/TD]
[TD="class: xl66, bgcolor: transparent"]N[/TD]
[TD="class: xl66, bgcolor: transparent"]Y[/TD]
[TD="class: xl66, bgcolor: transparent"]14[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 4[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER[/TD]
[TD="class: xl66, bgcolor: transparent"]ORTHOPAEDICS AT NWH[/TD]
[TD="class: xl66, bgcolor: transparent"]N[/TD]
[TD="class: xl66, bgcolor: transparent"]Y[/TD]
[TD="class: xl66, bgcolor: transparent"]14[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 5[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER[/TD]
[TD="class: xl66, bgcolor: transparent"]GI CC HEMATOLOGY/ONCOLOGY[/TD]
[TD="class: xl66, bgcolor: transparent"]Y[/TD]
[TD="class: xl66, bgcolor: transparent"]N[/TD]
[TD="class: xl66, bgcolor: transparent"]69[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PROVIDER 5[/TD]
[TD="class: xl66, bgcolor: transparent"]ATTENDING PROVIDER[/TD]
[TD="class: xl66, bgcolor: transparent"]GI ONC WANG MULTI[/TD]
[TD="class: xl66, bgcolor: transparent"]N[/TD]
[TD="class: xl66, bgcolor: transparent"]Y[/TD]
[TD="class: xl66, bgcolor: transparent"]1300[/TD]
[/TR]
[/TABLE]

This is a small fraction of the list, but essentially I'm trying to see those providers who do both ACD and PO (not necessarily for the same department). So provider 4 would not meet the criteria because they are Pro only, but provider 5 would because they do both.

With a helper column and as I see PRPVIDER 3 match the criteria as well:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Provider Description	Role Description	Department Description	Is ACD	Is PBO	Cost Center Number					Provider Description	Role Description	Department Description	Is ACD	Is PBO	Cost Center Number
2	PROVIDER 1	ATTENDING PROVIDER	PEDI HEMATOLOGY/ONCOLOGY	Y	N	141	2				PROVIDER 3	ATTENDING PROVIDER	PAIN CENTER	Y	N	503
3	PROVIDER 1	ATTENDING PROVIDER	YAWKEY 6A	Y	N	61	2				PROVIDER 3	ATTENDING PROVIDER	PAIN MEDICINE WALTHAM - PO	N	Y	132
4	PROVIDER 2	ATTENDING PROVIDER	HEM MALIGNANCIES	Y	N	66	2				PROVIDER 5	ATTENDING PROVIDER	GI CC HEMATOLOGY/ONCOLOGY	Y	N	69
5	PROVIDER 2	ATTENDING PROVIDER	LYMPHOMA	Y	N	66	2				PROVIDER 5	ATTENDING PROVIDER	GI ONC WANG MULTI	N	Y	1300
6	PROVIDER 3	ATTENDING PROVIDER	PAIN CENTER	Y	N	503	1
7	PROVIDER 3	ATTENDING PROVIDER	PAIN MEDICINE WALTHAM - PO	N	Y	132	1
8	PROVIDER 4	ATTENDING PROVIDER	ORTHO PEDIATRICS	N	Y	14	0
9	PROVIDER 4	ATTENDING PROVIDER	ORTHOPAEDICS AT NWH	N	Y	14	0
10	PROVIDER 5	ATTENDING PROVIDER	GI CC HEMATOLOGY/ONCOLOGY	Y	N	69	1
11	PROVIDER 5	ATTENDING PROVIDER	GI ONC WANG MULTI	N	Y	1300	1
Sheet1

 

[la998]

With a helper column and as I see PRPVIDER 3 match the criteria as well:
Sheet1

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 99px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #ccccff, align: center"]Provider Description[/TD]
[TD="bgcolor: #ccccff, align: center"]Role Description[/TD]
[TD="bgcolor: #ccccff, align: center"]Department Description[/TD]
[TD="bgcolor: #ccccff, align: center"]Is ACD[/TD]
[TD="bgcolor: #ccccff, align: center"]Is PBO[/TD]
[TD="bgcolor: #ccccff, align: center"]Cost Center Number[/TD]

[TD="bgcolor: #ccccff, align: center"]Provider Description[/TD]
[TD="bgcolor: #ccccff, align: center"]Role Description[/TD]
[TD="bgcolor: #ccccff, align: center"]Department Description[/TD]
[TD="bgcolor: #ccccff, align: center"]Is ACD[/TD]
[TD="bgcolor: #ccccff, align: center"]Is PBO[/TD]
[TD="bgcolor: #ccccff, align: center"]Cost Center Number[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]PROVIDER 1[/TD]
[TD="align: center"]ATTENDING PROVIDER[/TD]
[TD="align: center"]PEDI HEMATOLOGY/ONCOLOGY[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]141[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]PROVIDER 3[/TD]
[TD="align: center"]ATTENDING PROVIDER[/TD]
[TD="align: center"]PAIN CENTER[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]503[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]PROVIDER 1[/TD]
[TD="align: center"]ATTENDING PROVIDER[/TD]
[TD="align: center"]YAWKEY 6A[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]61[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]PROVIDER 3[/TD]
[TD="align: center"]ATTENDING PROVIDER[/TD]
[TD="align: center"]PAIN MEDICINE WALTHAM - PO[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]132[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]PROVIDER 2[/TD]
[TD="align: center"]ATTENDING PROVIDER[/TD]
[TD="align: center"]HEM MALIGNANCIES[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]66[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]PROVIDER 5[/TD]
[TD="align: center"]ATTENDING PROVIDER[/TD]
[TD="align: center"]GI CC HEMATOLOGY/ONCOLOGY[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]69[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]PROVIDER 2[/TD]
[TD="align: center"]ATTENDING PROVIDER[/TD]
[TD="align: center"]LYMPHOMA[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]66[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]PROVIDER 5[/TD]
[TD="align: center"]ATTENDING PROVIDER[/TD]
[TD="align: center"]GI ONC WANG MULTI[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]1300[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]PROVIDER 3[/TD]
[TD="align: center"]ATTENDING PROVIDER[/TD]
[TD="align: center"]PAIN CENTER[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]503[/TD]
[TD="align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: center"]PROVIDER 3[/TD]
[TD="align: center"]ATTENDING PROVIDER[/TD]
[TD="align: center"]PAIN MEDICINE WALTHAM - PO[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]132[/TD]
[TD="align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: center"]PROVIDER 4[/TD]
[TD="align: center"]ATTENDING PROVIDER[/TD]
[TD="align: center"]ORTHO PEDIATRICS[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: center"]PROVIDER 4[/TD]
[TD="align: center"]ATTENDING PROVIDER[/TD]
[TD="align: center"]ORTHOPAEDICS AT NWH[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: center"]PROVIDER 5[/TD]
[TD="align: center"]ATTENDING PROVIDER[/TD]
[TD="align: center"]GI CC HEMATOLOGY/ONCOLOGY[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]69[/TD]
[TD="align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: center"]PROVIDER 5[/TD]
[TD="align: center"]ATTENDING PROVIDER[/TD]
[TD="align: center"]GI ONC WANG MULTI[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]1300[/TD]
[TD="align: center"]1[/TD]

</TBODY>

Spreadsheet Formulas

Cell Formula G2 =COUNTIFS($A$2:$A$11,A2,$D$2:$D$11,"Y",$E$2:$E$11,"N") K2 {=IFERROR(INDEX($A$2:$G$11,SMALL(IF($G$2:$G$11=1,ROW($A$2:$A$11)-ROW($A$2)+1),ROWS($A$2:A2)),MATCH(K$1,$A$1:$G$1,0)),"")} <TBODY> </TBODY>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

Hi Robert,

Thanks for your swift response! I got the first part no problem, but when I try to put in the second formula it keeps pasting it as text. I'm not sure what I'm doing wrong. I also tried adjusting the ranges to the real number of rows which is 1157, but no luck. ? Any help would be great.

Thanks,
Laura

 

[Robert Mika]

Hi Robert,

Thanks for your swift response! I got the first part no problem, but when I try to put in the second formula it keeps pasting it as text. I'm not sure what I'm doing wrong. I also tried adjusting the ranges to the real number of rows which is 1157, but no luck. ? Any help would be great.

Thanks,
Laura

Please do not copy the curly brackets.
copy the firmla from "=" to ") the paste into your cell and confirm Control+Shift+Enter

 

[Marcelo Branco]

Another way

Put in Z2 this formula
=AND(COUNTIFS(A:A,A2,D:D,"Y")>0,COUNTIFS(A:A,A2,E:E,"Y")>0)

Let Z1 empty

Select your data range including headers

Data > Advanced Filter

and in Criteria Range put
Z1:Z2

M.