
Listing Unique Items in a Cell Range
From NeoWiki
(Difference between revisions)
Revision as of 10:50, 23 December 2007 (edit) Jgd (Talk  contribs) (translation of the French page Fr:Supprimer les Entrées Multiples dans une Plage de Cellules) ← Previous diff 
Current revision (04:21, 29 July 2008) (edit) (undo) Sardisson (Talk  contribs) m (one more bit) 

(4 intermediate revisions not shown.)  
Line 1:  Line 1:  
  Let us suppose you have a column in a spreadsheet  +  Let us suppose you have a column in a spreadsheet whose several rows contain the same values. You want to be able to get a list of the unique items and to display the number of times each item appears in the first list. NeoOffice allows you to do that by using the '''VLOOKUP''' function. 
'''Example'''<br>  '''Example'''<br>  
Line 5:  Line 5:  
Apply the following method:  Apply the following method:  
  *Insert two new columns  +  *Insert two new columns to the left of column A (the values originally in column A have now moved to column C). 
  *Select the cell A1  +  *Select the cell A1 and enter the formula 
*::'''=IF(COUNTIF(C$1:C1;C1)>1;0;1)'''  *::'''=IF(COUNTIF(C$1:C1;C1)>1;0;1)'''  
*Press the {{keyReturn}} key.  *Press the {{keyReturn}} key.  
  *Copy this formula downwards (  +  *Copy this formula downwards (see [[:Selecting and Filling a Cell Range]]). 
  *Select the cell B1  +  *Select the cell B1 and enter the formula 
*::'''=SUM(A$1:A1)'''  *::'''=SUM(A$1:A1)'''  
*Press the {{keyReturn}} key.  *Press the {{keyReturn}} key.  
*Copy this formula downwards.  *Copy this formula downwards.  
  *: You get a column containing the value '''1''' in front of the first occurrence of each color, and a column  +  *: You get a column containing the value '''1''' in front of the first occurrence of each color, and a column which contains the number of occurrences of each color. 
  *Create a range which contains the first 14 integers, e.g. E1:E14.  +  *Create a range which contains the first 14 integers, e.g. E1:E14 filled with integers 1 to 14. 
  *Select the cell D1  +  *Select the cell D1 and enter the formula 
*::'''=VLOOKUP(E1;B$1:C$14;2;0)'''  *::'''=VLOOKUP(E1;B$1:C$14;2;0)'''  
*Press the {{keyReturn}} key.  *Press the {{keyReturn}} key.  
*Copy this formula downwards.  *Copy this formula downwards.  
  *: You will notice that as there  +  *: You will notice that as there are only 4 colors, starting from the fifth row you get the error message '''#N/A'''. Thus, we have to slightly modify the formula in order to fix that problem. 
  *Select the cell F1  +  *Select the cell F1 and enter the formula 
*:: '''=IF(ISNA(VLOOKUP(E1;B$1:C$14;2;0));"";VLOOKUP(E1;B$1:C$14;2;0))'''  *:: '''=IF(ISNA(VLOOKUP(E1;B$1:C$14;2;0));"";VLOOKUP(E1;B$1:C$14;2;0))'''  
*Press the {{keyReturn}} key.  *Press the {{keyReturn}} key.  
*Copy this formula downwards.  *Copy this formula downwards.  
  *Select the cell  +  *Select the cell G1 and enter the formula 
*::'''=SUMPRODUCT(C$1:C$14=F1)'''  *::'''=SUMPRODUCT(C$1:C$14=F1)'''  
*Press the {{keyReturn}} key.  *Press the {{keyReturn}} key.  
Line 32:  Line 32:  
[[Image:Listing_Cells.png]]  [[Image:Listing_Cells.png]]  
  {{botlangbarEN[[Fr:Supprimer les Entrées Multiples dans une Plage de CellulesFrançais]]}}  +  {{botlangbarEN[[Fr:Supprimer les Entrées Multiples dans une Plage de CellulesFrançais]] [[It:Elenco_dei_termini_unici_in_un_intervallo_di_celleItaliano]]}} 
[[Category:Tips and Hints]][[Category:NeoOffice]]  [[Category:Tips and Hints]][[Category:NeoOffice]] 
Current revision
Let us suppose you have a column in a spreadsheet whose several rows contain the same values. You want to be able to get a list of the unique items and to display the number of times each item appears in the first list. NeoOffice allows you to do that by using the VLOOKUP function.
Example
Let us suppose the range A1:A14 contains the names of the following colors, purple, red, blue, green, each of them appearing several times.
Apply the following method:
 Insert two new columns to the left of column A (the values originally in column A have now moved to column C).
 Select the cell A1 and enter the formula
 =IF(COUNTIF(C$1:C1;C1)>1;0;1)
 Press the Return key.
 Copy this formula downwards (see Selecting and Filling a Cell Range).
 Select the cell B1 and enter the formula
 =SUM(A$1:A1)
 Press the Return key.
 Copy this formula downwards.
 You get a column containing the value 1 in front of the first occurrence of each color, and a column which contains the number of occurrences of each color.
 Create a range which contains the first 14 integers, e.g. E1:E14 filled with integers 1 to 14.
 Select the cell D1 and enter the formula
 =VLOOKUP(E1;B$1:C$14;2;0)
 Press the Return key.
 Copy this formula downwards.
 You will notice that as there are only 4 colors, starting from the fifth row you get the error message #N/A. Thus, we have to slightly modify the formula in order to fix that problem.
 Select the cell F1 and enter the formula
 =IF(ISNA(VLOOKUP(E1;B$1:C$14;2;0));"";VLOOKUP(E1;B$1:C$14;2;0))
 Press the Return key.
 Copy this formula downwards.
 Select the cell G1 and enter the formula
 =SUMPRODUCT(C$1:C$14=F1)
 Press the Return key.
 Copy this formula downwards.