Ask Your Question
0

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

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

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

Comments

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 +0100 )edit

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

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

2 Answers

Sort by » oldest newest most voted
0

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

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
0

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

JohnSUN gravatar image

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

Type at C1

=IF(ISERROR(MATCH(A1;A2:A100;0));"";B1&">>"&(INDEX(B2;MATCH(A1;A2:A100;0))))

and fill down

edit flag offensive delete link more

Comments

thank you JohnSUN

timlee111 gravatar imagetimlee111 ( 2014-06-24 16:59:47 +0100 )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 +0100 )edit

Question Tools

1 follower

Stats

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

Seen: 959 times

Last updated: Jun 24 '14