VLOOKUP and IF
-
- NewLounger
- Posts: 11
- Joined: 18 Mar 2014, 16:02
VLOOKUP and IF
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?
Doug in York PA
PS, Is this a job for Excel, or do I need to dust off my Access books?
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VLOOKUP and IF
Welcome to Eileen's Lounge!
It would help if you could attach a sample workbook (without sensitive data).
It would help if you could attach a sample workbook (without sensitive data).
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 11
- Joined: 18 Mar 2014, 16:02
Re: VLOOKUP and IF
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
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
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VLOOKUP and IF
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.
Picture attachments (.gif, .png, .jpg) will be displayed in the post itself, other attachments as a link for downloading.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 11
- Joined: 18 Mar 2014, 16:02
Re: VLOOKUP and IF
Screenshot is attached.
Doug
Doug
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VLOOKUP and IF
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
Hans
-
- NewLounger
- Posts: 11
- Joined: 18 Mar 2014, 16:02
Re: VLOOKUP and IF
Hans: That would require re-labeling a LOT of data. Let me think about how to do this...
Doug
Doug
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- NewLounger
- Posts: 11
- Joined: 18 Mar 2014, 16:02
Re: VLOOKUP and IF
Hans: A small mockup is attached.
Thanks.
Doug in York PA
Thanks.
Doug in York PA
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VLOOKUP and IF
Thanks, I'll look at it later today (it's almost dinnertime here). Someone else may reply first, of course.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 11
- Joined: 18 Mar 2014, 16:02
Re: VLOOKUP and IF
Thanks, Hans!
PS, Are you in England or in Germany?
Doug
PS, Are you in England or in Germany?
Doug
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- NewLounger
- Posts: 11
- Joined: 18 Mar 2014, 16:02
Re: VLOOKUP and IF
Wow - my mother's maiden name was Dutch. Her ancestors emigrated to New York state from Utrecht around 1710.
Doug
Doug
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VLOOKUP and IF
That's an old Dutch family in the US! Utrecht is about 30 miles east of where I live.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 11
- Joined: 18 Mar 2014, 16:02
Re: VLOOKUP and IF
'small world, yes?
Doug
Doug
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: VLOOKUP and IF
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??
(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??
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VLOOKUP and IF
Rudi's idea is worthwhile. You can add another helper column to determine which is highest:
You can then filter the pivot table on IsHighest = TRUE:
See the attached version.
You can then filter the pivot table on IsHighest = TRUE:
See the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 11
- Joined: 18 Mar 2014, 16:02
Re: VLOOKUP and IF
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
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
You do not have the required permissions to view the files attached to this post.
-
- NewLounger
- Posts: 11
- Joined: 18 Mar 2014, 16:02
Re: VLOOKUP and IF
Sincerely,
Doug in York PA
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands