adding blank cells to multiple columns to show similarities

[Guest3731]

Hi - I am hoping I can make my question clear - I have several columns
of alphabetic data (names). What I would like to do, if possible, is
to line up any and all names that are the same between any two or more
columns by adding blank cells as needed, such that it is possible to
see at a glance which names are common to one or more columns.
Something like:

A B C D

bob bob
carol carol
doug
elbert
fatty fatty

Is this doable in some automated way?

Thanks very much -

 

[Bernie Deitrick]

You can do some manual manipulation.

Copy each column of values into one column, then select them and use Data / Filter / Advanced
filter.... Check "Unique values only" and "Copy to other location" and select a cell somewhere to
the right of your table.

Then you should have a list of all the possible names, starting in, let's say, cell M2.

In cell N2, enter the formula

=IF(ISNUMBER(MATCH($M2,A:A,FALSE)),$M2,"")

and copy that down to match your list of unique values, and across for the same number of columns as
your original values. This assumes that your first column of names is column A.

HTH,
Bernie
MS Excel MVP