Listing Unique Items in a Cell Range

From NeoWiki

(Difference between revisions)
Jump to: navigation, search
Revision as of 12:37, 23 December 2007 (edit)
Jgd (Talk | contribs)
(add fixme)
← Previous diff
Revision as of 13:22, 23 December 2007 (edit) (undo)
Valterb (Talk | contribs)
(botlangbar -> IT and celle reference correction (G1 instead of D1 in the last step))
Next diff →
Line 26: Line 26:
*Press the {{key|Return}} key. *Press the {{key|Return}} key.
*Copy this formula downwards. *Copy this formula downwards.
-*Select the cell D1, and enter the formula+*Select the cell G1, and enter the formula
*::'''=SUMPRODUCT(C$1:C$14=F1)''' *::'''=SUMPRODUCT(C$1:C$14=F1)'''
*Press the {{key|Return}} key. *Press the {{key|Return}} key.
Line 33: Line 33:
[[Image:Listing_Cells.png]] [[Image:Listing_Cells.png]]
-{{botlangbarEN|[[Fr:Supprimer les Entrées Multiples dans une Plage de Cellules|Français]]}}+{{botlangbarEN|[[Fr:Supprimer les Entrées Multiples dans une Plage de Cellules|Français]] [[It:Elenco_dei_termini_unici_in_un_intervallo_di_celle|Italiano]]}}
[[Category:Tips and Hints]][[Category:NeoOffice]] [[Category:Tips and Hints]][[Category:NeoOffice]]

Revision as of 13:22, 23 December 2007

This article is not up to date, or it needs structural or stylistic changes to conform with the rest of this wiki. You can help the NeoOffice community by fixing it. If you do not have a NeoWiki account, please follow the steps on the New User page to request one.

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 on the left of column A. The values of the column A have 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 on NeoWiki).
  • 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 wich contains the number of occurrences of each color.
  • Create a range which contains the first 14 integers, e.g. E1:E14.
  • 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 is only 4 colors, starting from the fifth row you get the error message #N/A. Thus, we have to modify slightly 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.

Image:Listing_Cells.png


This article in other languages: Français Italiano
Personal tools