Concatenating text

[tushar_johri]

Hi,
I need to concatenate the first column of every 3 consecutive rows and put the concatenated text in a separate cell.I want to do it till the end of data the excel file.
For example,I have this data in 3 different rows in a two column table :-
Column1 | Column2
1
2
3
4
5
6

After concatenating,I want the data to appear as follows :-
Column1 | Column2
1 | 123
2 | 456
3 | ...
4 | ...
5 |
6 |

Can anyone out there please help me out with this ?

Thanks
Tushar

[JAVB]

What is in column 2. Is that to be concatenated also? Do you want the data in column one replaced with the concatenated data?

JAVB

[Martin Fishlock]

Tushar,

You can easily use a worksheet formula as:

=INDIRECT("A"&(ROW()-1)*3+1)&INDIRECT("A"&(ROW()-1)*3+2)&INDIRECT("A"&(ROW()-1)*3+3)

Where the first data row is 1, ie no headings

A1 = 1
A2 = 2....
--
HTHs Martin


"tushar_johri" wrote:

>
> Hi,
> I need to concatenate the first column of every 3 consecutive rows
> and put the concatenated text in a separate cell.I want to do it till
> the end of data the excel file.
> For example,I have this data in 3 different rows in a two column table
> :-
> Column1 | Column2
> 1
> 2
> 3
> 4
> 5
> 6
>
> After concatenating,I want the data to appear as follows :-
> Column1 | Column2
> 1 | 123
> 2 | 456
> 3 | ...
> 4 | ...
> 5 |
> 6 |
>
> Can anyone out there please help me out with this ?
>
> Thanks
> Tushar
>
>
> --
> tushar_johri
> ------------------------------------------------------------------------
> tushar_johri's Profile: http://www.excelforum.com/member.php...o&userid=30181
> View this thread: http://www.excelforum.com/showthread...hreadid=499356
>
>

[tushar_johri]

Hi Martin Fishlock,JAVB
Thank you very much for your responses.

JAVB,
Column2 is the column2 where I need to place the concatenated text

Martin Fishlock,
The solution worked fine as I required ! There is one small problem I am facing.It is like this :-
I need to concatenate the rows only if a row starts with some word.Here is an example :-
Column1 | Column2
A:XXX | A:XXXB:YYYC:ZZZ
B:YYY |
C:ZZZ |


In case any row does not have either A, or C as the starting word then I want to combine the row with the earlier row which is having one of A,B,C.Here is the example
Column1 | Column2
A:XXX | A:XXXMMMB:YYYNNNC:ZZZ
MMM |
B:YYY |
NNN |
C:ZZZ |


Can you please advise on how to go about this ?

Thanks
Tushar Johri