Data Validation

K

kevindict

I have a worksheet that contains three columns of data. All of the data
are people's names and some are repeated in multiple columns and some
are not. What I'd like to do is use a function to generate a fourth
column that contains a list of individuals from those three columns
without containing any repeats or omitting any individuals. SO.

MARK JONES TIM CAROTHER MARK JONES
ANN MAKER ANN MAKER ALBERT SMITH

Would turn in to:

MARK JONES
ANN MAKER
TIM CAROTHER
ALBERT SMITH

I know how to do this with a single column using a countif function, I
just cant seem to do it with a multiple coleumn range.

Thanks,
Kevin
 
E

Earl Kiosterud

Kevin,

One way would be to put them in one column (paste one under another), sort
it (putting duplicates next to each other), then, presuming the single
column is A and starts in A2, put this in B2 and copy down (temporarily):

=IF(COUNTIF($A$1:A2,A2)=1,A2,#N/A)

Now select column B, Edit - Goto - Special - Formulas - Errors (only --
don't check other three).

Edit - Delete - Entire Row.
 
K

kevindict

Yeah I thought of that, but I want this sheet to update automatically as
it's a report that's sent out weekly and I will be sending this workbook
out to people who are unfamiliar with excel. One thing I thought of is
to have the workbook copy the data down to a safe range automatically.
Such as:

Column B would copy into cells A1-500, Column C would copy into cells
A501-1000, and column D would copy into cells A1001-1500. I was just
wondering if there would be a better way than this, I try to stay away
from "safe" ranges.

Kevin
 
E

Earl Kiosterud

Kevin,

It is probably possible to do it with a formula, but I don't like to think
that hard any more. We could write you a macro that would do it, if you
want to use that approach. It wouldn't have to be in the workbook you send
out, so users wouldn't get the macro warning.
 
Top