You are here: Home / Lists

How to filter and count unique values among duplicates

October 2, 2008 By admin

If you have a column of data that contains duplicates, then you can use the advanced filter option to identify and copy the unique values to a different location. You can then easily count those values using COUNTA function or the ROWS function.

Perform the following steps to achieve this goal:

1. First make sure that your column has a header row.

2. Select your data range (including the header row).

3. On the Data menu point to Filter, then click Advanced Filter. The Advanced Filter window will open.

4. Click Copy to another location.

5. The List range text box should have your data range. If not then click on it, go to your worksheet and select the range.

6. Click on the Copy to text box. Go to your sheet and click on a blank column (or just a single cell) where you want your unique vales to be copied.

7. Select the Unique records only check box and click OK.

8. To count the unique values just copied, use the COUNTA function or the ROWS function. E.g if the unique values are in the range E1:E6 then in a separate cell enter one of the following formulas:

1. “=COUNTA(E2:E6)”

2. “=ROWS(E2:E6)”

Applies to Excel 2003

Filed Under: Data, Lists Tagged With: Autofilter, Count, Duplicates

How to create a custom list to use in Autofill

April 2, 2008 By admin
  1. In Excel menu click Tools.
  2. In the Tools menu click Options.
  3. Click the Custom Lists tab.
  4. Click the Add button. The cursor will move the List entries edit box.
  5. Enter the data for your list separated by commas. E.g. East, West, North, South.

 

Now if you enter “East” in any cell and drag the fill handle, other cells will automatically be filled with West, North … etc.

Applies to: Excel 2003

 

Filed Under: Data, Lists Tagged With: Autofill, Data, Excel, Excel help, Lists

Automatically fill data in adjacent cells (Autofill)

March 31, 2008 By admin

You can save yourself allot of typing if you know how to make use of autofill feature in Excel.

Suppose you want to enter names of days in cells A1 to A7 (Sun, Mon, …. Sat). Here is what you do:

  1. Enter “Sun” on cell “A1”.
  2. Drag the fill handle across the cells that you want to fill (A2 to A7).

You can also use Autofill to enter your own series of numbers. For example you can fill cells with the numbers 5,10,15,20 … etc as follows:

  1. Select the first cell in the range that you want to fill.
  2. Type the starting value for the series (5 in our example).
  3. Type a value in the next cell to establish a pattern (10).
  4. Select the first two cells that contain the starting values.
  5. Drag the fill handle across the range that you want to fill.

Applies to: Excel 2003

Filed Under: Data, Lists Tagged With: Autofill, Data, Excel, Excel help, Lists