Concatenate unique entries from cells

[leaning]

Hello! From cells A1, A2, A5, A9, A10: concatenate the text in the cell only if the entry doesn't match the entry in any other cell.


So, for:

A1 My, A2 name, A5 is, A9 bob, A10 mailman

Cell B1: =A1&char(10)&A2&char(10)&A5&char(10)&A9&char(10)&A10

My
name
is
bob
mailman

But if A9 = My and A10 = is, the result should be:

My
name
is

so, my formula had to take into account the permutations, and then the formula grew like crazy. (=if(a1<>A2,A1<>A5, A2<>A5, A1<>A9, A9<>a2, etc.

Is there any easy way to do this so that the formula only concatenates the unique entries in these cells?

Thanks!

VR/Lost

[leaning]

Hello!

I found this:

For a formula solution, assuming that A2:A10 contains your data, try the following...

B1: leave empty
B2, copied down:

=IF(OR(COUNTIF($B$1:B1,A2:$A$10)=0),INDEX(A2:$A$10,MATCH(0,COUNTIF($B$1:B1,A2:$A$10),0)),"")
....confirmed with CONTROL+SHIFT+ENTER, not just ENTER

I was just going to =A1, =A2, =A5, etc. to a new column, and then use the range formula above to extract the unique values, but it keeps putting a 0 in the list when I delete data from one of the referenced cells.

Any ideas on how to change the formula to stop putting those zeroes in there?

VR/Lost

[JBeaucaire]

ConcatIf
http://www.excelforum.com/excel-prog...in-cell.html#2 'explanation
http://www.excelforum.com/attachment...if-example.xls 'sample
http://www.excelforum.com/attachment...tif-sample.xls 'sample

[DonkeyOte]

A further alternative:

Please Login or Register  to view this content.

called from cell (B1) along the lines of:

Please Login or Register  to view this content.

[leaning]

Sirs,

I can't see how to apply your codes. If you look at what I attached, I tried to create something which illustrates what I was looking for.

Santa is delivering presents to the people in the yellow cells. As part of his process, he sends Christmas Card reminders (3 copies) to each of those people, and then one reminder always back to the home office at the North Pole.

As you can see from the distribution list, since Grandma will be visited twice (he really likes Grandma), the distribution has her getting 6 reminders vice 3. That is what the code should stop.

HTH!

VR/Lost

[JBeaucaire]

Please install the UDFs that DonkeyOte and I suggested and at least try to implement them in your sheet.

[leaning]

JBeaucaire,

I used your code and tried to apply it. but I can't get the CHAR(10)'s to be treated as commands rather than text.

Your code and explanation is written pretty clear, so I don't see why it isn't working like it should for this.

??

VR/Lost

[leaning]

All,

I got it to work!

Thanks for the help!

VR/Lost

[JBeaucaire]

Good job!

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.