James,

There is a very good webpage on the site of Chip Pearson dealing with duplicate
and unique items in lists (http://www.cpearson.com/excel/duplicat.htm) that
might be of help to get some ideas on how to make use of countif, frequency etc.
His website may be of help for several other problems as well.

Hans Pottel
Innogenetics NV
Belgium





jimljrx <[log in to unmask]> on 20/04/2001 16:39:08

Please respond to "(Association of Statistics Specialists Using Microsoft
      Excel)" <[log in to unmask]>

To:   [log in to unmask]
cc:    (bcc: Hans Pottel/INNOGENETICS)
Subject:  Tabulating multicoded responses




Does anyone know a way to tabulate multi-coded data using Excel?

I have a set of variables that I would like to tabulate.

Each contains responses coded from 0 to 9

In some cases the same unit can provide more than one response, i.e. the
variable is multi-coded.

I have coded these with a number made up from the codes given, e.g. 120
means that the unit gave responses 1, 2 and 0.

I now wish to produce tables showing how many times each of the responses
from 0 to 9 were given, i.e.3 entries would be made in the table for a unit
coded 120.

One way of doing this would be to use the FIND function to expand the single
column containing the data to 10 columns with binary responses for the
presence or absence of each code.  However this seems laborious.  Does
anyone have an easier way, preferably one that would work with the pivot
tables that I am using to tabulate those variables that are single coded?

I have tried COUNTIF(     FIND( )) but that does not work.  I thought of
using a series of functions like INT(N/10^(n-1))-10*INT(N/10^n) where N is
the number coded and n is a series of numbers from 1 to 10 all combined
together in an OR statement but that also seems too complicated.

James Rothman
[log in to unmask]