Concatenate Merge & normal cell

[jasontoh]

Hi,

Pls help me to make CONCATENATE formula

A1:D1 is merge cell with content "XX"
A2 = "11", B2 ="12", C2 ="13", D2="14"
How can I make a formula to CONCATENATE row 1 & 2 to get row 3 as:
A3 ="XX11", B3 = "XX12", C3="XX13", D3="XX14"

any help is appreciated.

Jason

 

[Dannyh1]

Hi,

Is it always sets of 4 columns?

 

[jasontoh]

Hi Dannyh1,

No, as shown in post #10, row 1 can have cell merge with different no. of columns

Jason

 

[Dannyh1]

Bit of a cop out, but can you insert a row between row 1 & 2

Then

A2 = A1
B2 = =IF(B1="",A2,B1) Copy accross

A4 = A2&A3 Copy accross

Hide row 2

?

 

[markmzz]

Jasontoh,

Try this:

=INDEX(1:1;1;(4*INT((COLUMN()-1)/4)+1))&A2

Markmzz

 

[jasonb75]

Jasontoh,

Try this:

=INDEX(1:1;1;(4*INT((COLUMN()-1)/4)+1))&A2

Markmzz

If you read the thread properly you could have avoided posting a formula that is of no use.

Try

=LOOKUP(2,1/($A1:A1<>""),$A1:A1)&A2

 

[Dannyh1]

Nice.

 

[jasontoh]

jasonb75,

Great!
Simple & works well!

Thanks
Jason

 

[markmzz]

Jasontoh,

Or try this (use Ctrl+Shift+Enter and not only Enter),

=INDEX(1:1,1,LARGE(COLUMN($A$1:A1)*(--($A$1:A1<>"")),1))&A2

Markmzz

 

[jasonb75]

Jasontoh,

Or try this (use Ctrl+Shift+Enter and not only Enter),

=INDEX(1:1,1,LARGE(COLUMN($A$1:A1)*(--($A$1:A1<>"")),1))&A2

Markmzz

My previous response also applies here.

 

[jasontoh]

markmzz, Your idea does not work per what I want
Maybe you have misunderstood me

I am happy with jasonb75's solution