VLOOKUP and IF

decker04
NewLounger
Posts: 11
Joined: 18 Mar 2014, 16:02

VLOOKUP and IF

Post by decker04 »

Hans: I have two lists with about ten fields each. I need to filter to only the rows where TOTAL CHARGES are higher. If TOTAL CHARGES are higher on one list, I'll keep the associated fields from that row in the summary table The two source tables are ATTENDING PHYSICIAN and PRINCIPAL OPERATING PHYSICIAN. For any one operation, charges may be split into either category. But, it appears the larger of the TOTAL CHARGES row (whether ATTENDING or PRINCIPAL) will contain all of the charges associated with that procedure. So, I need to instruct Excel to look at the two tables and to choose the rows where TOTAL CHARGES is the highest. I have put the data into a pivot table and I can see the instances where a surgeon was both things (paired up). I do not know how to tell it to identify and to isolate only the row with the highest TOTAL CHARGES. Suggestions are welcome.

Doug in York PA

PS, Is this a job for Excel, or do I need to dust off my Access books?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VLOOKUP and IF

Post by HansV »

Welcome to Eileen's Lounge!

It would help if you could attach a sample workbook (without sensitive data).
Best wishes,
Hans

decker04
NewLounger
Posts: 11
Joined: 18 Mar 2014, 16:02

Re: VLOOKUP and IF

Post by decker04 »

Hans: Here is a screenshot,with the sensitive information disguised...

Well, I tried to paste a screenshot here. I do not see that option, nor do I see the button for inserting attachments. Is there a trick to this?

Doug

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VLOOKUP and IF

Post by HansV »

See Tip: adding an attachment using the prosilver skin.

Picture attachments (.gif, .png, .jpg) will be displayed in the post itself, other attachments as a link for downloading.
Best wishes,
Hans

decker04
NewLounger
Posts: 11
Joined: 18 Mar 2014, 16:02

Re: VLOOKUP and IF

Post by decker04 »

Screenshot is attached.

Doug
Doug's Screenshot 21-Apr-14.jpg
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VLOOKUP and IF

Post by HansV »

Thanks. That helps a bit, but I would still like to have a sample workbook, otherwise I have no idea what to do.
Best wishes,
Hans

decker04
NewLounger
Posts: 11
Joined: 18 Mar 2014, 16:02

Re: VLOOKUP and IF

Post by decker04 »

Hans: That would require re-labeling a LOT of data. Let me think about how to do this...

Doug

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VLOOKUP and IF

Post by HansV »

Perhaps you could create a small mockup?
Best wishes,
Hans

decker04
NewLounger
Posts: 11
Joined: 18 Mar 2014, 16:02

Re: VLOOKUP and IF

Post by decker04 »

Hans: A small mockup is attached.

Thanks.

Doug in York PA
Doug_FAKE_SET 21Apr14.xlsx
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VLOOKUP and IF

Post by HansV »

Thanks, I'll look at it later today (it's almost dinnertime here). Someone else may reply first, of course.
Best wishes,
Hans

decker04
NewLounger
Posts: 11
Joined: 18 Mar 2014, 16:02

Re: VLOOKUP and IF

Post by decker04 »

Thanks, Hans!

PS, Are you in England or in Germany?

Doug

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VLOOKUP and IF

Post by HansV »

See below my avatar next to my posts - I'm in The Netherlands.
Best wishes,
Hans

decker04
NewLounger
Posts: 11
Joined: 18 Mar 2014, 16:02

Re: VLOOKUP and IF

Post by decker04 »

Wow - my mother's maiden name was Dutch. Her ancestors emigrated to New York state from Utrecht around 1710.

Doug

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VLOOKUP and IF

Post by HansV »

That's an old Dutch family in the US! Utrecht is about 30 miles east of where I live.
Best wishes,
Hans

decker04
NewLounger
Posts: 11
Joined: 18 Mar 2014, 16:02

Re: VLOOKUP and IF

Post by decker04 »

'small world, yes?

Doug

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: VLOOKUP and IF

Post by Rudi »

If a helper column can be set up in the Pivot Source that extracts the highest value of each MRN, and return zeros for the others
(some like: {=MAX(IF($C$2:$C$5=C2,$F$2:$F$5))} - not quite right as I cannot get zeros for non-highest)
Then the pivot can be use to filter for the highest per group??
1.jpg
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VLOOKUP and IF

Post by HansV »

Rudi's idea is worthwhile. You can add another helper column to determine which is highest:
S0735.png
You can then filter the pivot table on IsHighest = TRUE:
S0736.png
See the attached version.
Doug_FAKE_SET 21Apr14.xlsx
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

decker04
NewLounger
Posts: 11
Joined: 18 Mar 2014, 16:02

Re: VLOOKUP and IF

Post by decker04 »

Hans and Rudi: Thank you very much. Late yesterday I broke down and asked one of my co-workers for suggestions. Stephanie suggested something similar to your solution.

This seems to be working. However, it will require two sets of thirteen VLOOKUPs in the same spreadsheet. That may bog down. But, it is a chance at least.

Thank you both for your help and for your patience.

Sincerely,
Doug in York PA
Doug's Snapshot 22Apr14.JPG
You do not have the required permissions to view the files attached to this post.

decker04
NewLounger
Posts: 11
Joined: 18 Mar 2014, 16:02

Re: VLOOKUP and IF

Post by decker04 »

Doug's Second Screenshot 22Apr14.jpg
Hans and Rudi: Thanks for all of your help. Here is the final result - (above). The names of individuals have been covered up and disguised.

Sincerely,
Doug in York PA :clapping: :clapping:
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VLOOKUP and IF

Post by HansV »

Glad you were able to solve the problem!
Best wishes,
Hans