match two columns and concatenate them

Question

I have column A and B, i want to concatenate A with B values into c column using formula but need help. The problem is, if column B has more than two colors, then the formula breaks. I'm not quit sure how to fix the formula to support more than 2 colors which will likely happen on my worksheet. Here's the formula:

=IF(AND(A1="---",B1="---"),"---",IF(A1="",CONCATENATE(OFFSET(A1,-1,0)," - ",B1),CONCATENATE(A1," - ",B1)))

Answer 1

Try:

C1: =CONCATENATE(MAX($A$1:A1),"-",B1)

and fill down.

To account for the "---" entries:

C1: =IF(AND(A1="---",B1="---"),"---",CONCATENATE(MAX($A$1:A1),"-",B1))

Edit: Oh, and if the labels in Column A might not be in a nice numerical sequence, or might be text, you can use:

C1: =IF(AND(A1="---",B1="---"),"---",CONCATENATE(LOOKUP(2,1/(LEN($A$1:A1)>0),$A$1:A1),"-",B1))

Answer 2

Try this:

=INDEX(A:A,MATCH(1E+99,$A$1:A1))&"-"&B1