The first column in my range is a list of customers, columns 2-8 represent
days of week, headers Mon, Tue, Etc. In the days of the week portion of the
range we list a code that representing the Courier Co. that picks up the
order.

ie)
A B C D E F G
1 Customer Mon Tue Wed Thur Fri Sat
2 AAA FX FX UPS
3 ABC FX BAX FX
4 TTT FX UPS
5 XYZ FX FX FX


I want to concatenate in column H all the Courier companies used to ship
orders out but eliminate the duplicates and put a comma in between ea unique
co.

Thanks for your help.
Steven

Hi Steven,
If nobodyelse comes up with a single column solution, and if columns H
to O are available you could have what you are after in column O then
just hide columns H to N this way..

Formula in H2 =COUNTIF($B2:B2,B2)

Formula in I2 =COUNTIF($B2:C2,C2)

Formula in J2 =COUNTIF($B2:C2,D2)

Formula in K2 =COUNTIF($B2:C2,E2)

Formula in L2 =COUNTIF($B2:C2,F2)

Formula in M2 =COUNTIF($B2:C2,G2)

Formula in N2 =IF(H2=1,B2&",","")
&IF(I2=1,C2&",","")&IF(J2=1,D2&",","")&IF(K2=1,E2& ",","")&IF(L2=1,F2&",","")&IF(M2=1,G2&",","")

Formula in O2 =IF(RIGHT(N2,1)=",",LEFT(N2,LEN(N2)-1))

Fill all these formulas down to the bottom of your data then hide
columns H to N

Ken Johnson

Oops, call me a pillock!

Should've been...

Formula in H2 =COUNTIF($B2:B2,B2)

Formula in I2 =COUNTIF($B2:C2,C2)

Formula in J2 =COUNTIF($B2:D2,D2)

Formula in K2 =COUNTIF($B2:E2,E2)

Formula in L2 =COUNTIF($B2:F2,F2)

Formula in M2 =COUNTIF($B2:G2,G2)

That's what I get for taking shortcut.

Ken Johnson

Ken,

your solution did the trick.

Thanks Kindly for the help

Best Regards, ST



"Ken Johnson" wrote:

Oops, call me a pillock!

Should've been...

Formula in H2 =COUNTIF($B2:B2,B2)

Formula in I2 =COUNTIF($B2:C2,C2)

Formula in J2 =COUNTIF($B2:D2,D2)

Formula in K2 =COUNTIF($B2:E2,E2)

Formula in L2 =COUNTIF($B2:F2,F2)

Formula in M2 =COUNTIF($B2:G2,G2)

That's what I get for taking shortcut.

Ken Johnson

You're welcome Steve.
Thanks for the feedback.

Ken Johnson