Concaténer des cellules avec une condition

[MadameHugo]

Good morning all,

I wanted to concatenate colors (in range B) with respect to each value which is in row A. (see image) . The result is in Range C.

I used concatenation with condition like this: = IF (A2 <> 0; CONCATENATE (B2; ","; B3; ","; B4; ","; B5; ","; B6); "" ) because sometimes there are empty cells in row A.

The problem is that sometimes there is 1 color, sometimes 2 colors, sometimes more! And my formula only works if there are 5 colors. Each time I have to change my formula manually ...

Someone can help me ? Thank you.

 

[Peter_SSs]

sometimes there are empty cells in row A.

1. Note that "A" is a column, not a row. Incorrect terminology will confuse your helpers and possibly lead to a delayed or incorrect solution.
   
   
2. If posting in the standard "Excel Questions" forum, please post in English, including your thread title. Note the sub-heading to the forum

   All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only

   
   
3. For the future, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

With Excel 2010, a worksheet formula solution could look like this. the helper column (D) could be hidden once the formulas are entered.

MadameHugo 2020-03-15 1.xlsm
	A	B	C	D
2	x	a	a,b	a
3		b		a,b
4	x	c	c,d,e	c
5		d		c,d
6		e		c,d,e
7	x	f	f,g	f
8		g		f,g
9	x	h	h,i,j	h
10		i		h,i
11		j		h,i,j
12	x	k	k,l	k
13		l		k,l
14	x	m	m,n,o	m
15		n		m,n
16		o		m,n,o
17	x	p	p	p
18	x	q	q,r,s	q
19		r		q,r
20		s		q,r,s
21
Sheet1

Cell Formulas
Range	Formula
C2:C20	C2	=IF(A2="","",INDEX(D2:D$30,MATCH(TRUE,INDEX(LEN(D2:D$29)>=LEN(D3:D$30),0),0)))
D2:D20	D2	=IF(A2="",D1&","&B2,B2)

 

[jasonb75]

A simplified version of Peter's suggestion without arrays.

Sample Data.xlsm
	A	B	C	D
1	Ref	product	Result	Helper
2	a	a	a,b	a,b
3		b		b
4	a	c	c,d,e,f	c,d,e,f
5		d		d,e,f
6		e		e,f
7		f		f
8	a	g	g	g
9	a	h	h,i,j	h,i,j
10		i		i,j
11		j		j
12	a	k	k,l,m	k,l,m
13		l		l,m
14		m		m
15	a	n	n,o,p,q,r	n,o,p,q,r
16		o		o,p,q,r
17		p		p,q,r
18		q		q,r
19		r		r
20	a	s	s,t,u,v,w	s,t,u,v,w
21		t		t,u,v,w
22		u		u,v,w
23		v		v,w
24		w		w
Sheet17

Cell Formulas
Range	Formula
C2:C24	C2	=IF(A2="","",D2)
D2:D24	D2	=IF(A3<>"",B2,IF(D3<>"",B2&","&D3,B2))

You could also do it without the extra column by only using the formula in column D, then masking the rows where column A is blank with conditional formatting.

 

[Qadah]

Hello MadameHugo,

whats the maximum number of colors?

in Excel 365, there is a formula to do it, but i think you are using Excel 2010, so there isnt an easy formula to do it,

for maximum of 5 colors, you can use this formula (starting C2 for an example), its not pretty and completely dynamic but it doesnt need helper columns

=IF(A2<>"",B2&IF(A3="",","&B3&IF(A4="",","&B4&IF(A5="",","&B5&IF(A6="",","&B6,""),""),""),""),"")

if you need more colors you can expand the internal loop, if not this one is enough

Q