Selecting examples from a table

[EricK]

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.

 

[Normek]

Hi Erick,
Select your header row, Then on the ribbon in 2007 or the menu on any other
version select Data , then Filter (or Autofilter)
Then from the dropdown box on the first coloumn, select your city.

 

[EricK]

That's not quite what I want. I want to produce a table which has as many
rows as there are unique items in column A.

If the data were in access, then an SQL like:

SELECT country, Min(city) AS example_city
FROM data
GROUP BY country

would work, as "Min" works with text fields.

 

[Herbert Seidenberg]

Pivot Table Field > City > Field Settings > Advanced > Top 10 >
Show Top 1 > Using Field: > Sum of XXX
XXX is some other existing or new numerical field.
Sample file at:
http://www.savefile.com/files/1693942

 

[Lars-Åke Aspelin]

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 C22 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

 

[Lars-Åke Aspelin]

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 C22 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

 

[EricK]

Thanks. That's quite ingenious and exactly what I need!