Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced use of LARGE function
I've looked through all the posts I could find back to 2005 and couldn't find
the exact answer to my question. Setup: I have created a VB form which populates a hidden data sheet. Column A is account numbers - each unique Column B is a Vendor name NOT UNIQUE Problem: I need to use the LARGE function to find the LAST number given to each of the vendors So what I think I'm looking for is a nested IF statement in a LARGE or the other way around. Not quiet sure though how to formulate this. Thanks in advance I know you all always come up with the answer |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced use of LARGE function
Or maybe do I want a FIND function with a nested LARGE?
"JLeoni" wrote: I've looked through all the posts I could find back to 2005 and couldn't find the exact answer to my question. Setup: I have created a VB form which populates a hidden data sheet. Column A is account numbers - each unique Column B is a Vendor name NOT UNIQUE Problem: I need to use the LARGE function to find the LAST number given to each of the vendors So what I think I'm looking for is a nested IF statement in a LARGE or the other way around. Not quiet sure though how to formulate this. Thanks in advance I know you all always come up with the answer |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced use of LARGE function
Assuming your non-unique is in Cell d1...
=MAX(IF(A1:A126=D1,B1:B126,"")) then press Ctrl+Shift+Enter HTH Die_Another_Day JLeoni wrote: I've looked through all the posts I could find back to 2005 and couldn't find the exact answer to my question. Setup: I have created a VB form which populates a hidden data sheet. Column A is account numbers - each unique Column B is a Vendor name NOT UNIQUE Problem: I need to use the LARGE function to find the LAST number given to each of the vendors So what I think I'm looking for is a nested IF statement in a LARGE or the other way around. Not quiet sure though how to formulate this. Thanks in advance I know you all always come up with the answer |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced use of LARGE function
On Mon, 10 Jul 2006 11:45:02 -0700, JLeoni
wrote: I've looked through all the posts I could find back to 2005 and couldn't find the exact answer to my question. Setup: I have created a VB form which populates a hidden data sheet. Column A is account numbers - each unique Column B is a Vendor name NOT UNIQUE Problem: I need to use the LARGE function to find the LAST number given to each of the vendors So what I think I'm looking for is a nested IF statement in a LARGE or the other way around. Not quiet sure though how to formulate this. Thanks in advance I know you all always come up with the answer One way, and no doubt there are several others is as follows Sort columns A:B with B as the first sort and A as the second - both ascending. Then in C1, assuming data is in A1:B12 enter =INDIRECT("A"&MATCH(B1,$B$1:$B$12)) and copy down the largest account number is repeated for each of the vendors If you want to preserve the original order, then before the sort fill a helper column with 1:12, do the sort including the helper column, range value column B and resprt on the helper column. HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
numerical integration | Excel Discussion (Misc queries) | |||
Custom function | Excel Worksheet Functions | |||
Large function | Excel Worksheet Functions | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
Help on Large Function | Excel Worksheet Functions |