Concatenating text

  • Thread starter Thread starter tushar_johri
  • Start date Start date
T

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
 
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
 
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....
 
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
 
Back
Top