concat rows

[nmpb]

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;000034001589;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

 

[Pete_UK]

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="",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),""))

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

 

[nmpb]

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]

Okay, well thanks for feeding back.

Pete

 

[nmpb]

I'll re-post this in the programming section

Okay, well thanks for feeding back.

Pete