Concatenate non-adjacent cells without duplicates

Hello all,

I've been working on a spreadsheet that is used to track the application process of numerous clients. In doing this, I have a row at the top of each client that is used as a sumarry column. Below each cell in the top row, I have up to 30 non-adjacent cells that I'm trying to concatenate in the top cell separated by new lines. I'm also wanting to not display any duplicates, as there are up to only 5 steps that could be shown for each record. I found a UDF that will search an array and return only the unique values, but it accepts the data as an array, and it returns it as an array also. I seem to be having trouble passing the array from the sheet as well as formatting and returning the array with line breaks.

In the worksheet cell:

Please Login or Register  to view this content.

The function ( I did not write this)

Please Login or Register  to view this content.

I've played with this numerous ways but these are the codes that might best describe what I'm trying to do. Really hoping some of you have some ideas for me that don't involve going bald at the age of 26 thanks!


BTW, I'm using Excel2004 on a mac, and I need to try and keep this compatible also with Excel 2007 as that is the version the person I'm working with uses and he also needs access to this sheet.

Perhaps a different UDF would work for you. Can you post up a sample sheet clearly demonstrating sample data and sample desired results. I have a couple in mind, but a sample sheet would make it clear which to use.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

Use the CONCATIF() UDF that you have installed. It's used like a SUMIF().

I used this in H4:

=CONCATIF($D$5:$D$300, "<>"&"", H$5:H$300, CHAR(10), TRUE)

You might want to try CHAR(10)&CHAR(13) rather than probing the type of Operator.

The CHAR(10) is invisible on my Mac. Is the CHAR(13) invisible on a PC?

(I'm glad the UDF worked for you.)

Not really sure how I mark this as SOLVED in the forum,

For that and other useful information, please see the forum rules

Originally Posted by mikerickson

(I'm glad the UDF worked for you.)

Mike, your UDF is at the top of my toolbelt. Thanks again for that!

And see, I remembered the syntax you helped me with last week on this thread. I is a'learnin!

One nice thing is that the criteria syntax carries over to SUMIF,COUNTIF, data base functions (e.g. DSUM) and Advanced Filter.