Tuesday, September 24, 2013

Google Spreadsheet Concatenate 2 rows if they have the same value in one column

Question:

( by Paul Enestvedt )

 Hi, I'm trying to merge the text in several rows based on a common ID# in a different field.  For example, I have:

Id           date             service
12345     1/21/2012     testing
23456     4/21/2010     registration
12345     2/15/2012     conference
12345     2/15/2013     practice test
44567     9/14/2009     coaching
12345     8/15/2012     employed
44567     10/13/2010   conference
etc.

I would like:
12345     testing, conference, practice test, employed
23456     registration
44567     coaching, conference

I found a post that demonstrated a similar query for summing, but I want to concatenate.
Anyone ideas?
Thanks,
Paul

Solution:
Have a look at the following screenshot of "Sheet1":


And following screenshot of "Sheet2":

In the above sheet "Sheet2", I have the following formula in Cell A2:
=unique('Sheet1'!A2:A)

the following formula in Cell B2:
=join(", ";transpose(iferror(filter('Sheet1'!C:C;'Sheet1'!A:A=A2))))

 and then you can drag the above formula to Cell B3, B4... and so on... as far as needed...


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private and confidential projects:
http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

6 comments:

  1. GyanSeptember 25, 2013 at 12:25 PM

    This comment has been removed by the author.

    ReplyDelete
  2. GyanSeptember 25, 2013 at 12:32 PM

    Thank You very much for sharing this. Really Helpful.

    ReplyDelete
  3. Gandham Ravi KumarMarch 7, 2014 at 6:52 AM

    I have a data like this
    ACTN4 PPARG Zbtb3 ELF5 IRF2 Irf3 Osr2 STAT1 Pknox1 SPI1 FEV
    ACTN4 Egr1 Rhox11 Spdef SOX9 Irf4 Nkx2-2
    ACTN4 Sox14 ELK1 Gbx2 Gsc TP53 NHLH1 Max Prrx1 Myf6
    ACTN4 Sox12 Zfp691 ESR2 EBF1 Tcfap2c REST REST TP53 ESR1 Myf6
    ACTN4 ELK1 Gm397 Max Rfxdc2 Myf6
    ACTN4 Zfp691 Sp4 Smad3 ESR2 Sox12 Pax5 Mycn ESR1 PLAG1 Spdef
    ACTN4 REST EBF1 REST Duxl Pou6f1 Pou6f1 INSM1 Zfp423 MYC::MAX MAX
    ACTN4 Ddit3::Cebpa SPI1 Duxl Esrra REST EBF1 Tcf7l2 Pax5 SPI1 Tgif2
    ACTN4 SPI1 EBF1 Ddit3::Cebpa Esrra PBX1 Duxl Tgif2 Rfx3 Tcf1
    ACTN4 Egr1 Titf1
    ACTN4 Bapx1 EBF1 Nkx3-1 SPI1 Mycn Myc Cutl1 SPI1 RUNX1 Rfx4
    ACTN4 EBF1 Bapx1 SPI1 Nkx3-1 SPI1 Mycn Rfx4 Nkx2-2 Myc YY1
    ACTN4 NFKB1 NF-kappaB RELA EBF1 Stat3 NFATC2 REL Zfp410 TP53 Bcl6b
    ACTN4 Rfx4 Rfxdc2 EBF1 NFATC2 ELF5 STAT1 Sox12 Rfx3 FEV MZF1
    ACTN4 Zfp281 Zfp740 EWSR1-FLI1 SP1 Zfp410 Gabpa RREB1 MZF1 Sp4 PLAG1
    ACTN4 REST REST EBF1 Zbtb12 Plagl1
    ACTN4 Klf4 STAT1Foxj3 Gata3 Pou2f2 EWSR1-FLI1 Tcf3 Sox17 Pou2f3 Srf
    ACTN4 Zfp281 Zfp740 SP1 EWSR1-FLI1 Gabpa Pax4 RREB1 Zfp410 Tal1::Gata1 SP1
    ACTN4 ESR2 RXR::RAR_DR5 Sp4 Zfp281 NFE2L1::MafG Sox11 ESR1 Irf6 Esrrb Tcfap2c
    ACTN4 Nkx6-3 Sox4 Nkx6-1 REST REST Foxj3 Zfp105 Hlxb9 Sox11 ZEB1
    ACTN4 NFE2L2 Tbp Hoxa3 Nkx2-6 GABPA
    ACTN4 YY1 Gata1 Atf1 Cphx AP1 Jundm2 Foxj3 Gm397 Hbp1 Esx1
    ACTN4 Arid3a Tcf1 Hnf4a Zfp740 Sox15 Hnf4a Rxra ELK4
    ACTN4 BRCA1 Tcf7l2 Tcf3 Tcf7 SOX9 HNF4A Lef1 RUNX1 Hoxb3
    ACTN4 Zic1 Zic2 Zic3 Stat3 Zbtb3 Rfxdc2 Spdef Pou2f3
    ACTN4 NFATC2 AP1 Fos Vsx1 STAT1 Hoxb3 Phox2b Cart1 Otp Hoxc5
    ACTN4 Rfxdc2 Zic2 Zic1 Zic3 Tgif2 Stat3 Zbtb3 Bhlhb2 Sfpi1
    ACTN4 Hic1 YY1 Barhl1 Msx1 Sox4 PBX1 znf143 Zfp410 RREB1 ETS1
    ACTN4 NFATC2 Tcfap2e Sox7 RUNX1 SOX9 Nkx2-2 RXR::RAR_DR5 Lef1 Hltf Sox15
    ACTN4 Mtf1 Zfp105 Sox7 Srf Foxl1 Sox17 Foxj1 Sox8 Sox11 Elf3
    ALDOA PPARG Zbtb3 ELF5 IRF2 Irf3 Osr2 STAT1 Pknox1 SPI1 FEV
    ALDOA Egr1 Rhox11 Spdef SOX9 Irf4 Nkx2-2
    ALDOA Sox14 ELK1 Gbx2 Gsc TP53 NHLH1 Max Prrx1 Myf6
    ALDOA Sox12 Zfp691 ESR2 EBF1 Tcfap2c REST REST TP53 ESR1 Myf6
    ALDOA ELK1 Gm397 Max Rfxdc2 Myf6
    ALDOA Zfp691 Sp4 Smad3 ESR2 Sox12 Pax5 Mycn ESR1 PLAG1 Spdef
    ALDOA REST EBF1 REST Duxl Pou6f1 Pou6f1 INSM1 Zfp423 MYC::MAX MAX
    ALDOA Ddit3::Cebpa SPI1 Duxl Esrra REST EBF1 Tcf7l2 Pax5 SPI1 Tgif2
    ALDOA SPI1 EBF1 Ddit3::Cebpa Esrra PBX1 Duxl Tgif2 Rfx3 Tcf1
    ALDOA Egr1 Titf1
    this kind of data runs in thousands of lines
    I want to concatenate rows if they have the same name in column 1
    i.e in the above example i wish to concatenate rows for ACTN4 and ALDOA of column 1
    please help me out

    ReplyDelete
  4. Ms. SturdivantJune 25, 2015 at 2:13 AM

    Is there a way to filter out blanks?

    ReplyDelete
  5. Ms. SturdivantJune 25, 2015 at 2:33 AM

    Or rather, what if in the columns other than the identifier, there might be identical pieces of data that don't need it to repeat? I only want it to join the data if the values are unique, not duplicates.

    ReplyDelete
  6. Hanging OutOctober 11, 2018 at 10:10 PM

    This is really great, thanks so much for this AMAZING solution. Is there anyway to avoid the drag / duplicating by using an ArrayFormula ( ) around the join, or is that not possible.

    ReplyDelete

Subscribe to: Post Comments (Atom)