On Mon, 28 Jul 2008 16:41:26 GMT, Lars-Åke Aspelin
wrote:

On Mon, 28 Jul 2008 03:21:02 -0700, EricK
wrote:

Suppose I have a 2-column table. Column A contains the names countries and
column B contains the name of a city in that country. The same country can
appear more than once in column A, but the whole table is unsorted. (So the
first 5 cells in column A might read England, France, Germany, France,
Germany and in column B Liverpool, Paris, Munich, Marseilles, Berlin).

I want to extract from this table a smaller table with each country
appearing once in the first column and the second column containing any
relevant example city in that country. Is there a simple way to do that? I
tried using a pivot table with the "MIN" function, hoping that MIN would pick
the alphabetically first city, but "MIN" only works on numbers.


If your countries are in A1:A10 and your towns are in B1:B10 you can
try the following formula in cell C2 (not in C1):

Note: This is an array formula that has to be entered by
CTRL+SHIFT+ENTER rather than just ENTER

=INDEX($A$1:$A$10,MATCH(0,COUNTIF($C$1:C1,$A$1:$A $10),0))

In cell D2 you put the following formula:
(This is not an array formula)

=VLOOKUP(C2,A$1:B$10,2,FALSE)

Copy cells C2:D2 down as far as needed

The result should be one row per contry and the town for each contry
will be taken from the first occurance of the respective country in
the table. The table with one row per country starts on row 1 rather
than on row 2.

Hope this helps / Lars-Åke

If you want the resulting table to start on row 1 (the same row as the
input table) then you can put the following formula in cell C1:

=A1

and copy the formula in cell D2 to cell D1.

There are a couple of $ more than needed in the formula for cell C2.
Here is a lighter version with the same functionality:

=INDEX(A$1:A$10,MATCH(0,COUNTIF(C$1:C1,A$1:A$10),0 ))

Lars-Åke