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

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

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

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

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

Sort by » oldest newest most voted

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

more

Type at C1

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


and fill down

more

thank you JohnSUN

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

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

## Stats

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

Seen: 1,009 times

Last updated: Jun 24 '14