Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for
I need help creating a formula in Excel. I have a file that contains products
in column A with their corresponding location in column B. I want to easily determine for each product what the LEAST occurences in location are. For example, A1:A25=apples, A26:A220=paper clips, A221:A222=pens. The corresponding location for each product are listed in column B. Example, B1:B10=Maine,B11=NY,B12:B25=Ohio. From this, it's easy to see that "apples" has only one location occurance located in NY. I'm hoping this makes sense. Is there a formula that would lookup the values in column A and return the product with the least # of occurances in the corresponding range? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for
Unless Harlan weighs in with something I don't see, there's no way of doing
this with a single formula. You'll need some intermediate step(s) Is this a one-time effort, or something that you'll have to do frequently? How often does your data change? The most expedient approach depends on what you want to accomplish. "Doctor T" wrote: I need help creating a formula in Excel. I have a file that contains products in column A with their corresponding location in column B. I want to easily determine for each product what the LEAST occurences in location are. For example, A1:A25=apples, A26:A220=paper clips, A221:A222=pens. The corresponding location for each product are listed in column B. Example, B1:B10=Maine,B11=NY,B12:B25=Ohio. From this, it's easy to see that "apples" has only one location occurance located in NY. I'm hoping this makes sense. Is there a formula that would lookup the values in column A and return the product with the least # of occurances in the corresponding range? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for
This is a project for work so I have to assume I'll be doing this again
somewhere down the road. The file is fairly large (85,000+ records). The data doesn't change only that a new record is posted daily. Any help is appreciated. Thanks! "Duke Carey" wrote: Unless Harlan weighs in with something I don't see, there's no way of doing this with a single formula. You'll need some intermediate step(s) Is this a one-time effort, or something that you'll have to do frequently? How often does your data change? The most expedient approach depends on what you want to accomplish. "Doctor T" wrote: I need help creating a formula in Excel. I have a file that contains products in column A with their corresponding location in column B. I want to easily determine for each product what the LEAST occurences in location are. For example, A1:A25=apples, A26:A220=paper clips, A221:A222=pens. The corresponding location for each product are listed in column B. Example, B1:B10=Maine,B11=NY,B12:B25=Ohio. From this, it's easy to see that "apples" has only one location occurance located in NY. I'm hoping this makes sense. Is there a formula that would lookup the values in column A and return the product with the least # of occurances in the corresponding range? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for
With 85k records, you are far, FAR better off dumping the data into an Access
database and running a quick query against it. Your results will be nearly instantaneous, so long as you have an index on each of the Product and Location columns Here's the query sytax you would use: select top 1 * from ( SELECT Count(Location) AS LocCount, Location FROM ProductLocation WHERE (Product="Apples") GROUP BY Location ORDER by Count(Location)); "Doctor T" wrote: This is a project for work so I have to assume I'll be doing this again somewhere down the road. The file is fairly large (85,000+ records). The data doesn't change only that a new record is posted daily. Any help is appreciated. Thanks! "Duke Carey" wrote: Unless Harlan weighs in with something I don't see, there's no way of doing this with a single formula. You'll need some intermediate step(s) Is this a one-time effort, or something that you'll have to do frequently? How often does your data change? The most expedient approach depends on what you want to accomplish. "Doctor T" wrote: I need help creating a formula in Excel. I have a file that contains products in column A with their corresponding location in column B. I want to easily determine for each product what the LEAST occurences in location are. For example, A1:A25=apples, A26:A220=paper clips, A221:A222=pens. The corresponding location for each product are listed in column B. Example, B1:B10=Maine,B11=NY,B12:B25=Ohio. From this, it's easy to see that "apples" has only one location occurance located in NY. I'm hoping this makes sense. Is there a formula that would lookup the values in column A and return the product with the least # of occurances in the corresponding range? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for
I'll try that.
Thanks Duke! "Duke Carey" wrote: With 85k records, you are far, FAR better off dumping the data into an Access database and running a quick query against it. Your results will be nearly instantaneous, so long as you have an index on each of the Product and Location columns Here's the query sytax you would use: select top 1 * from ( SELECT Count(Location) AS LocCount, Location FROM ProductLocation WHERE (Product="Apples") GROUP BY Location ORDER by Count(Location)); "Doctor T" wrote: This is a project for work so I have to assume I'll be doing this again somewhere down the road. The file is fairly large (85,000+ records). The data doesn't change only that a new record is posted daily. Any help is appreciated. Thanks! "Duke Carey" wrote: Unless Harlan weighs in with something I don't see, there's no way of doing this with a single formula. You'll need some intermediate step(s) Is this a one-time effort, or something that you'll have to do frequently? How often does your data change? The most expedient approach depends on what you want to accomplish. "Doctor T" wrote: I need help creating a formula in Excel. I have a file that contains products in column A with their corresponding location in column B. I want to easily determine for each product what the LEAST occurences in location are. For example, A1:A25=apples, A26:A220=paper clips, A221:A222=pens. The corresponding location for each product are listed in column B. Example, B1:B10=Maine,B11=NY,B12:B25=Ohio. From this, it's easy to see that "apples" has only one location occurance located in NY. I'm hoping this makes sense. Is there a formula that would lookup the values in column A and return the product with the least # of occurances in the corresponding range? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for
"Doctor T" wrote...
I need help creating a formula in Excel. I have a file that contains products in column A with their corresponding location in column B. I want to easily determine for each product what the LEAST occurences in location are. For example, A1:A25=apples, A26:A220=paper clips, A221:A222=pens. The corresponding location for each product are listed in column B. Example, B1:B10=Maine,B11=NY,B12:B25=Ohio. From this, it's easy to see that "apples" has only one location occurance located in NY. .... With the product of interest in cell D2, try the array formula =INDEX(B$2:B$1001,MATCH(MIN(IF(FREQUENCY(IF(A$2:A$ 1001=D2, MATCH(B$2:B$1001,IF(A$2:A$1001=D2,B$2:B$1001),0)), ROW(A$2:A$1001))0,FREQUENCY(IF(A$2:A$1001=D2,MATC H(B$2:B$1001, IF(A$2:A$1001=D2,B$2:B$1001),0)),ROW(A$2:A$1001))) ), FREQUENCY(IF(A$2:A$1001=D2,MATCH(B$2:B$1001,IF(A$2 :A$1001=D2, B$2:B$1001),0)),ROW(A$2:A$1001)),0)) Note: this formula would recalculate VERY SLOWLY for really large ranges. This sort of thing is much better suited to databases. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|