concat rows
 

Hi
I need to be able to concate all the rows in B until the cell in column A is
not blank. would also want a separator between them.
ie row C1 = 000034001570;000034001571;000034001582;00003400158 9;000034001590
C6 = 000034032303

Is this possible?

A B
1 000034001570 000034001570
2 000034001571
3 000034001582
4 000034001589
5 000034001590
6 000034032303 000034032303
7 000034066598 000034066598
8 000034017214 000034017214
9 000034017215
10 000034019302
11 000034019303

concat rows
 

Put this formula in C1:

=IF(A1="","",IF(B1="","",B1)&IF(A2="",IF(B2="","", ";"&B2),"")&IF
(A3="",IF(OR(B3="",COUNTA(A1:A3)1),"",";"&B3),"") &IF(A4="",IF(OR
(B4="",COUNTA(A1:A4)1),"",";"&B4),"")&IF(A5="",IF (OR(B5="",COUNTA
(A1:A4)1),"",";"&B5),"")&IF(A6="",IF(OR(B6="",COU NTA(A1:A6)
1),"",";"&B6),"")&IF(A7="",IF(OR(B7="",COUNTA(A1: A7)1),"",";"&B7),"")
&IF(A8="",IF(OR(B8="",COUNTA(A1:A8)1),"",";"&B8), ""))

and then copy down as required. It might be easier to see what's
happening if I post it like this:

=IF(A1="","",IF(B1="","",B1)
&IF(A2="",IF(B2="","",";"&B2),"")
&IF(A3="",IF(OR(B3="",COUNTA(A1:A3)1),"",";"&B3), "")
&IF(A4="",IF(OR(B4="",COUNTA(A1:A4)1),"",";"&B4), "")
&IF(A5="",IF(OR(B5="",COUNTA(A1:A4)1),"",";"&B5), "")
&IF(A6="",IF(OR(B6="",COUNTA(A1:A6)1),"",";"&B6), "")
&IF(A7="",IF(OR(B7="",COUNTA(A1:A7)1),"",";"&B7), "")
&IF(A8="",IF(OR(B8="",COUNTA(A1:A8)1),"",";"&B8), ""))

It will concatenate up to 8 cells (i.e. 7 continuous blanks in column
A), but it is relatively easy to expand if you need more.

This is what I got with my test data:

z A A;B;C;D;E
B
C
D
E
y F F
x G G
w H H;I;J
I
J
v K K

Hope this helps.

Pete


On Jan 14, 3:52*pm, nmpb wrote:
Hi
I need to be able to concate all the rows in B until the cell in column A is
not blank. would also want a separator between them.
ie *row C1 = *000034001570;000034001571;000034001582;0000340015 89;000034001590
C6 = 000034032303

Is this possible?

* * * * * *A * * * * * * * * * *B
1 000034001570 *000034001570
2 * * * * * * * * * * * 000034001571
3 * * * * * * * 000034001582
4 * * * * * * * 000034001589
5 * * * * * * * 000034001590
6 000034032303 *000034032303
7 000034066598 *000034066598
8 000034017214 *000034017214
9 * * * * * * * 000034017215
10 * * * * * * *000034019302
11 * * * * * * *000034019303

concat rows
 

Thank you for that, it does work. The issue is that I have a sheet of over
40,000 rows, also I have noticed that I have duplicates in the column B,
which will have to be excluded. I think I am going to need a program to do
it.

"Pete_UK" wrote:

Put this formula in C1:

=IF(A1="","",IF(B1="","",B1)&IF(A2="",IF(B2="","", ";"&B2),"")&IF
(A3="",IF(OR(B3="",COUNTA(A1:A3)1),"",";"&B3),"") &IF(A4="",IF(OR
(B4="",COUNTA(A1:A4)1),"",";"&B4),"")&IF(A5="",IF (OR(B5="",COUNTA
(A1:A4)1),"",";"&B5),"")&IF(A6="",IF(OR(B6="",COU NTA(A1:A6)
1),"",";"&B6),"")&IF(A7="",IF(OR(B7="",COUNTA(A1: A7)1),"",";"&B7),"")
&IF(A8="",IF(OR(B8="",COUNTA(A1:A8)1),"",";"&B8), ""))

and then copy down as required. It might be easier to see what's
happening if I post it like this:

=IF(A1="","",IF(B1="","",B1)
&IF(A2="",IF(B2="","",";"&B2),"")
&IF(A3="",IF(OR(B3="",COUNTA(A1:A3)1),"",";"&B3), "")
&IF(A4="",IF(OR(B4="",COUNTA(A1:A4)1),"",";"&B4), "")
&IF(A5="",IF(OR(B5="",COUNTA(A1:A4)1),"",";"&B5), "")
&IF(A6="",IF(OR(B6="",COUNTA(A1:A6)1),"",";"&B6), "")
&IF(A7="",IF(OR(B7="",COUNTA(A1:A7)1),"",";"&B7), "")
&IF(A8="",IF(OR(B8="",COUNTA(A1:A8)1),"",";"&B8), ""))

It will concatenate up to 8 cells (i.e. 7 continuous blanks in column
A), but it is relatively easy to expand if you need more.

This is what I got with my test data:

z A A;B;C;D;E
B
C
D
E
y F F
x G G
w H H;I;J
I
J
v K K

Hope this helps.

Pete


On Jan 14, 3:52 pm, nmpb wrote:
Hi
I need to be able to concate all the rows in B until the cell in column A is
not blank. would also want a separator between them.
ie row C1 = 000034001570;000034001571;000034001582;00003400158 9;000034001590
C6 = 000034032303

Is this possible?

A B
1 000034001570 000034001570
2 000034001571
3 000034001582
4 000034001589
5 000034001590
6 000034032303 000034032303
7 000034066598 000034066598
8 000034017214 000034017214
9 000034017215
10 000034019302
11 000034019303

concat rows
 

Okay, well thanks for feeding back.

Pete

On Jan 15, 10:27*am, nmpb wrote:
Thank you for that, it does work. *The issue is that I have a sheet of over
40,000 rows, also I have noticed that I have duplicates in the column B,
which will have to be excluded. *I think I am going to need a program to do
it.

concat rows
 

I'll re-post this in the programming section

"Pete_UK" wrote:

Okay, well thanks for feeding back.

Pete

On Jan 15, 10:27 am, nmpb wrote:
Thank you for that, it does work. The issue is that I have a sheet of over
40,000 rows, also I have noticed that I have duplicates in the column B,
which will have to be excluded. I think I am going to need a program to do
it.