Formula - find unique entries over several columns

[moily]

Hi there

I’d first like to start out by thanking anyone who can help me out with this
issue but that, for many of my own reasons which are very valid, I cannot use
a VBA solution – only a formula based solution.

I have seven columns of data. Most entries overlap throughout the columns
but I need to find every unique entry in all of these columns. My results
need to be shown in one neat column with no spaces in between entries. I
have vastly simplified the amount of data for this example – this is usually
over 500 lines for each column and I have about 40 columns, some of the
columns stand alone and there are a few clumps like this one where seven or
three or five columns relate to each other. The stand alone columns are
simple to obtain the unique entries but the complication comes when I need to
extend this over several clumped columns. For those clumped columns most of
the unique entries are found in the first column but there can be a few
others scattered in the other columns. Due to the amount of data and the
size of the spreadsheet I MUST have this in a very neat one column solution.
I’ve given an example below of the column structure and the result I need.
I’ve also given an example of the formulas I currently use for the stand
alone columns. I would really appreciate it if anyone can assist in this!!!

Column structure and desired result example:

Column A Column B Column C Column D Column E
Good Very bad Very bad Very good
Extremely good Very bad Very good Good
Ok Moderate Ok Ok
Bad Bad Very good
Good Good Extremely good Extremely good Ok
Good Ok Bad
Moderate Bad Bad Bad
Ok Ok Very good


Unique entries:
Good
Very bad
Ok
Good
Bad
Very good
Extremely good
Moderate


Stand alone column array formulas (using Ctrl+shift+enter)
{=IF(INDEX(X2:X495,MATCH(0,-ISBLANK(X2:X495),0))=".","EXTRA",INDEX(X2:X495,MATCH(0,-ISBLANK(X2:X495),0)))}
{=IF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI2,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI2,0)),0)),"")=".","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI2,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI2,0)),0)),""))}
{=IF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI3,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI3,0)),0)),"")=".","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI3,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI3,0)),0)),""))}
{=IF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI4,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI4,0)),0)),"")=".","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI4,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI4,0)),0)),""))}
{=IF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI5,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI5,0)),0)),"")=".","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI5,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI5,0)),0)),""))}
{=IF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI6,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI6,0)),0)),"")=".","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI6,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI6,0)),0)),""))}
{=IF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI7,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI7,0)),0)),"")=".","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI7,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI7,0)),0)),""))}

 

[JB]

See example

http://cjoint.com/?fedeYkYN5W

JB
http://boisgontierjacques.free.fr/

 

[moily]

Thanks JB but I already have a few solutions along these lines.
Unfortunately, it's the size of the data that causes trouble with them - I
often have about 8 clumped columns with 500 rows of data in each - your
solution would mean several columns (as there are several bunches of clumped
columns) of 4000 rows of data.

There are other complications to the sheet as well that I haven't gone into
but suffice it to say I really need one neat column solution - can you or
anyone else help?

Cheers,
Ann