Concatenate Unique Values

[shdwfx]

I don't know VBA or macros. I need a formula to take this where each value is in a separate column:
ZP ZP Z1 Z1 UNASSIGNED

to this in one cell:
ZP Z1 UNASSIGNED

Any help is greatly appreciated.

[Fotis1991]

The easier way is to use Text To Columns option from Data Tab.

[FDibbins]

Is each of those in its own cell?

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Tony Valko]

I don't think you'll be able to do this with a single practical formula (someone may be able to do it with a monster formula).

A VBA user defined function is the best option for something like this.

You don't really need to understand how the VBA code works, just knowing that it does what you want and how to implement it should be a good place to start.

I can give you step-by-step instructions.

Interested?

[shdwfx]

Attached is an example and it's a bit more complex than I initially asked but if I need to repeat the steps by region (NA, EMEA, APAC being the regions), I've no problem doing that. Columns D - I contain values for the APAC region. I need to concatenate only the unique values in those cells into Column A. Same for EMEA I need to concatenate only the unique values from Columns J-V into Column B. Concatenate only the unique values from Columns W-Z into Column C. So Columns D - Z are the before, Columns A-C are the after.

[shdwfx]

P.S. If you can tell a total newbie how to enter the code, I'm certainly willing to give it a try. My co-worker I believe did this with a formula but he writes monster formulas regularly.

[Tony Valko]

OK, I'm breaking for lunch and will be away for a few hours.

When I return I'll take a look at your file and see what we can come up with.

[AlKey]

in E1

=IF(COUNTIF($A$1:A1,A1)=1,A1,"")&" "&IF(COUNTIF($A$1:B1,B1)=1,B1,"")&" "&IF(COUNTIF($A$1:C1,C1)=1,C1,"")&" "&IF(COUNTIF($A$1:D1,D1)=1,D1,"")&" "&"UNASSIGNED"

	A	B	C	D	E
1	ZP	ZP	Z1	Z1	ZP Z1 UNASSIGNED
2

[AlKey]

Please see attached. Columns A, B and C are now filled with formulas.

[shdwfx]

AlKey: Thanks!!!!!!!!

[AlKey]

You're welcome and thanks for the feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

[AlKey]

Now, if you want a Vb solution you can use this nice UDF

Press Alt+F11 to open VBA editor
Go Insert and select Module
Paste code below into Module and close editor

Use formula:

=TRIM(ConcatUniq(W3:Z3," "))

Please Login or Register  to view this content.

[shdwfx]

Thanks! After I get this report done I will try the VBA. Thank goodness for all of you patient, generous geniuses here!

[Tony Valko]

When I return I'll take a look at your file and see what we can come up with.

Looks like that's not necessary!

[shdwfx]

Looks like that's not necessary!

Yep someone beat you to the punch but thank you very much for the offer!

[Tony Valko]

You're welcome!

Good luck!

[shdwfx]

Ignore this post as I'm not able to delete it.