Ask Your Question

How to merge same data in different columns into one row? [closed]

asked 2014-06-24 12:23:36 +0200

timlee111 gravatar image

Dear All, I have a data as below:

       A            B
1    apple         red
2    apple         green
3    lemon         yellow
4    lemon         green

How could I merge data to :

       A            B
1    apple         red>>green
2    lemon         yellow>>green

Thank you~

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-26 09:56:56.277746


Your example is not expressive enough. You have to clarify assurances.

1) Will every specific 'Fruit' occur only in CONTIGUOUS rows?

2) Does every specific 'Fruit' occur exactly twice with different 'Colours'?

3) Will there never occur an 'empty' 'Colour'?

4) Order following occurrence?

Given all of these assurances ther will be a rather simple solution.

Given none it will be really complicated. Limiting conditions and additional clarification about requirements will be needed.

Lupp gravatar imageLupp ( 2014-06-24 13:09:09 +0200 )edit

Thank you Lupp~ 1.yes 2 some will occur 4 to 5 times empty color 4.yes

timlee111 gravatar imagetimlee111 ( 2014-06-24 16:58:35 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2014-06-24 20:22:54 +0200

Lupp gravatar image

OK, regarding timlee111's comment concerning my 2nd query above I think I shouldn't try to suggest a solution by a single (fillable?) formula. I sketched a rather clearly structured (?) solution but I cannot simply tell "enter this, then that and...". As often I use some helper columns. Study the attached example everyone interested. In your success reducing the complexity I am interested in return. ask35953GroupBy1Concatenate2_001.ods

edit flag offensive delete link more

answered 2014-06-24 13:01:31 +0200

JohnSUN gravatar image

updated 2014-06-24 13:02:31 +0200

Type at C1


and fill down

edit flag offensive delete link more


thank you JohnSUN

timlee111 gravatar imagetimlee111 ( 2014-06-24 16:59:47 +0200 )edit

This works for 2 items. What if I have 5, 10 ,20 repeated items? What should I type?

arnaldosfjunior gravatar imagearnaldosfjunior ( 2014-08-10 04:58:34 +0200 )edit

Question Tools

1 follower


Asked: 2014-06-24 12:23:36 +0200

Seen: 1,009 times

Last updated: Jun 24 '14