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

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

JAV

--
JAV
-----------------------------------------------------------------------
JAVB's Profile: http://www.excelforum.com/member.php...fo&userid=2930
View this thread: http://www.excelforum.com/showthread.php?threadid=49935

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

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


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