Concatenate multiple cells using another column a reference

[notaguru1]

Hi everyone,

This one is a little tricky, I've been spinning my wheels without much luck.

I have 2 columns of data in columns A and B. Column B has a value in every single row whereas column A only has values in every other row or every 3 or 4 rows, it varies. I'm trying to create a formula where I can concatenate the values in column B ONLY when there is no corresponding cell in column A is blank.

For example, cell A2 has a value, as does cell B2. I want to create a formula in column C where I can concatenate the values starting in cell B3 UNTIL B16. B16 is the last value I want to concatenate because there is a value in A17. I'm using column A as a refernce for blank cells. I want to drag this formula down because this sheet has 1000 rows of data. The next concatenation that would be returned would be starting in cell B18 as that is the next blank corresponding value in column A.

 

[gaz_chops]

Not clear what you want to concatenate? Is it B2&B3&B4 etc? Or A1&B1 then A2&B2 etc?

 

[notaguru1]

I want to concatenate B2, B3, B4 up until there is a value is column A. So if there is a value in cell A6. I want to concatenate cells B2, B3, B4, B5.

Thanks for the help.

 

[gaz_chops]

Like

[TABLE="width: 216"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD] 1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD] 2[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD] 3[/TD]
[TD]321[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD] 4[/TD]
[TD]4321[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD] 5[/TD]
[TD]54321[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD] 6[/TD]
[TD]654321[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] 7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] 8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD] 9[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD] 10[/TD]
[TD]109[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD] 11[/TD]
[TD]11109[/TD]
[/TR]
</tbody>[/TABLE]

Then try
=IF(A2="","",B2&C1)

 

[notaguru1]

No, not quite. Using your example it would look like this:


[TABLE="class: cms_table"]
<TBODY>[TR]
[TD]A
[/TD]
[TD]1
[/TD]
[TD]2345
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]6
[/TD]
[TD]789
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]8
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]9
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]10
[/TD]
[TD]11
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]11
[/TD]
[/TR]
</TBODY>[/TABLE]

So I imagine the formula would look something like this for the first value returned: =IF(A1="","",B2&B3&B4&B5) this would return 2345

But I dont know how to create this formula where it will be true for the entire sheet