Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting examples from a table
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting examples from a table
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" 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting examples from a table
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. "Normek" wrote: 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" 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting examples from a table
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting examples from a table
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting examples from a table
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting examples from a table
Thanks. That's quite ingenious and exactly what I need!
"Herbert Seidenberg" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting Fields in Pivot Table | Excel Discussion (Misc queries) | |||
formula for selecting cells in one table from values in another | Excel Worksheet Functions | |||
'if' formula selecting a cell from a table | Excel Worksheet Functions | |||
Selecting Specific Data within a Table | Excel Worksheet Functions | |||
Selecting valuse from a table | Excel Discussion (Misc queries) |