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]

Provider Description	Role Description	Department Description	Is ACD	Is PBO	Cost Center Number
PROVIDER 1	ATTENDING PROVIDER	PEDI HEMATOLOGY/ONCOLOGY	Y	N	141
PROVIDER 1	ATTENDING PROVIDER	YAWKEY 6A	Y	N	61
PROVIDER 2	ATTENDING PROVIDER	HEM MALIGNANCIES	Y	N	66
PROVIDER 2	ATTENDING PROVIDER	LYMPHOMA	Y	N	66
PROVIDER 3	ATTENDING PROVIDER	PAIN CENTER	Y	N	503
PROVIDER 3	ATTENDING PROVIDER	PAIN MEDICINE WALTHAM - PO	N	Y	132
PROVIDER 4	ATTENDING PROVIDER	ORTHO PEDIATRICS	N	Y	14
PROVIDER 4	ATTENDING PROVIDER	ORTHOPAEDICS AT NWH	N	Y	14
PROVIDER 5	ATTENDING PROVIDER	GI CC HEMATOLOGY/ONCOLOGY	Y	N	69
PROVIDER 5	ATTENDING PROVIDER	GI ONC WANG MULTI	N	Y	1300

<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>
</TBODY>

 

[la998]

Provider Description	Role Description	Department Description	Is ACD	Is PBO	Cost Center Number
PROVIDER 1	ATTENDING PROVIDER	PEDI HEMATOLOGY/ONCOLOGY	Y	N	141
PROVIDER 1	ATTENDING PROVIDER	YAWKEY 6A	Y	N	61
PROVIDER 2	ATTENDING PROVIDER	HEM MALIGNANCIES	Y	N	66
PROVIDER 2	ATTENDING PROVIDER	LYMPHOMA	Y	N	66
PROVIDER 3	ATTENDING PROVIDER	PAIN CENTER	Y	N	503
PROVIDER 3	ATTENDING PROVIDER	PAIN MEDICINE WALTHAM - PO	N	Y	132
PROVIDER 4	ATTENDING PROVIDER	ORTHO PEDIATRICS	N	Y	14
PROVIDER 4	ATTENDING PROVIDER	ORTHOPAEDICS AT NWH	N	Y	14
PROVIDER 5	ATTENDING PROVIDER	GI CC HEMATOLOGY/ONCOLOGY	Y	N	69
PROVIDER 5	ATTENDING PROVIDER	GI ONC WANG MULTI	N	Y	1300

<TBODY>
</TBODY>

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]

Provider Description	Role Description	Department Description	Is ACD	Is PBO	Cost Center Number
PROVIDER 1	ATTENDING PROVIDER	PEDI HEMATOLOGY/ONCOLOGY	Y	N	141
PROVIDER 1	ATTENDING PROVIDER	YAWKEY 6A	Y	N	61
PROVIDER 2	ATTENDING PROVIDER	HEM MALIGNANCIES	Y	N	66
PROVIDER 2	ATTENDING PROVIDER	LYMPHOMA	Y	N	66
PROVIDER 3	ATTENDING PROVIDER	PAIN CENTER	Y	N	503
PROVIDER 3	ATTENDING PROVIDER	PAIN MEDICINE WALTHAM - PO	N	Y	132
PROVIDER 4	ATTENDING PROVIDER	ORTHO PEDIATRICS	N	Y	14
PROVIDER 4	ATTENDING PROVIDER	ORTHOPAEDICS AT NWH	N	Y	14
PROVIDER 5	ATTENDING PROVIDER	GI CC HEMATOLOGY/ONCOLOGY	Y	N	69
PROVIDER 5	ATTENDING PROVIDER	GI ONC WANG MULTI	N	Y	1300

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

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

*

*

*

*

*

*

*

*

*

<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>
</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.