CODEWITHSUNDEEP
excel
jackr
jackr

Reputation: 1437

Select a subset of a table for further processing

In Microsoft Excel, how can I compute a range (portion of a column), based on the values in another column of the same table, returning the result in an Array form for further processing by other functions?

In SQL, what I mean is "SELECT field1 FROM table WHERE field2=value".

The selected results will be fed (twice) to FREQUENCY(), to compute the number of distinct entries in "field1". That is: given an existing table like this:

Box          Date
1            07/01/12
13           07/01/12
13           07/01/12
27           07/18/12
13           07/18/12
55           07/18/12

I want to produce a resulting table like this:

Boxes      Date
2          07/01/12
3          07/18/12

Note that "13" is only counted once in the first date ("distinct"), but it's still counted again in the second date.

I already have an expression that does the right thing for the whole of the table, 

=SUM(N(FREQUENCY(Box,Box)>0))

where "Box" is a named range of the first table, consisting of the whole Box column. (Using the same range/array/list as the data and the bins for FREQUENCY is a stupefyingly subtle trick actually contained in the Excel help but -- alas! -- by no means adequately explained.)

But I want (several) subsets, one for each date. I want to expand my "SUM(N(FREQUENCY…" expression to act only on the rows of the first table whose Date column matches the Date column of the row being computed. That is, again resorting to SQL,

SELECT count(DISTINCT t1.Box), t2.Date
FROM `t1` JOIN `t2` ON (Date)
GROUP BY Date

I can even build a pivot table of the interesting values (which gets me counts in its cells), then use a parallel, date-indexed column of

=COUNTIF(…)

to reduce each row of counts down to a single count of uniques for that date. But this requires me to update the pivot table to notice new data in the base table, and then to drag-expand the column of answers to include the new date (or suffer ugly value error markers). So something more automatic, less fussily manual, would be sweet.

Upvotes: 1

Views: 2350

Answers (1)

pnuts
pnuts

Reputation: 59485

I guess not available when you asked, but Excel 2013 has Distinct Count as an option in a PivotTable:

SO12135410 example

Upvotes: 1

Related Questions