Formula for

[Guest]

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?

 

[Guest]

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.

 

[Guest]

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!

 

[Guest]

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));

 

[Guest]

I'll try that.

Thanks Duke!

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));

 

[Harlan Grove]

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,MATCH(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.