how to merge many cells to one cell with keeping all data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Valley - 25 Aug 2006 13:12 GMT

Dear all,

I meet a question about following.
in A column, there are many data(some data are same), and in B column,
there are many relevant data, but they are different, how to merge all
the revelant data to one cell in B column with the same in A column,
thanks in advance!

For example

A B
123 R1
123 R2
123 R3
123 R4
124 L1
124 L2
124 L3
125 C1
125 C2
. .
. .
. .

BR
Valley

Reply to this Message

Naveen - 25 Aug 2006 13:37 GMT

Question not clear !!!

What output you are expecting???

> Dear all,
>
[quoted text clipped - 22 lines]
> BR
> Valley

Reply to this Message

Valley - 25 Aug 2006 14:02 GMT

Dear,

I meet a question about following.
in A column, there are many data(some data are same), and in B column,
there are many relevant data, but they are different, how to merge all
the revelant data to one cell in B column with the same in A column,
thanks in advance!

For example

A B C
123 R1 R1 R2 R3 R4
123 R2
123 R3
123 R4
124 L1 L1 L2 L3
124 L2
124 L3
125 C1 C1 C2
125 C2
. .
. .
. .

but I know to do them with C1=B1&" "&B2&" "&B3&" "&B4 one by one, I
want to know how to do easily.

BR
Valley

Reply to this Message

Richard Buttrey - 25 Aug 2006 16:12 GMT

With the data below starting in row 2 put in C2
=IF(A2=A1,C1&" "&B2,B2)

Now conditionally format C2 and choose the formula is option and
enter
=$A2=$A3

set the font colour to white, or whatever is the colour of your
background and copy C2 down your data

All the cells in column C of course contain data, it's just that you
can't see the other cells.

What you see is the following. It's not quite the same as your exact
requirement which is to have the data on the first row of a 'block' of
similar A values, but you may be able to work with this.

A VBA macro could be written to meet your originally requirement
exactly. If that's what you want just re-post back

HTH

123    R1
123    R2
123    R3
123    R4    R1 R2 R3 R4
124    L1
124    L2
124    L3    L1 L2 L3
125    C1
125    C2    C1 C2

>Dear,
>
[quoted text clipped - 25 lines]
>BR
>Valley

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Reply to this Message

Valley - 25 Aug 2006 18:51 GMT

Thank you very much! this is a good way. :-)

BR
Valley

> With the data below starting in row 2 put in C2
> =IF(A2=A1,C1&" "&B2,B2)
[quoted text clipped - 62 lines]
> Grappenhall, Cheshire, UK
> __________________________

Reply to this Message