Forum Discussion

espo610's avatar
espo610
Copper Contributor
Dec 09, 2021

concatenate date from duplicate rows into a single field

I have a large spreadsheet containing 4 columns of data. the first three are duplicates, but col4 is not. I am trying to concatenate the 4th column into a single row preserving col 1-3.

 

my attachment is just an illustration, i do NOT want it in the same workbook. I know it will be a new workbook.

 

Thank you in advance!

5 Replies

    • espo610's avatar
      espo610
      Copper Contributor
      not familiar with powerquery. also, what i have is the multiple lines of duplicate data and i want a new spreadsheet of combined/concatenated data.
      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor

        espo610 What stops you from learning PQ? You will not regret it. And the trick is that you can start in a clean sheet, connect to the raw data without even opening the file, set-up the query once and refresh whenever the data changes. Your choice.

  • espo610 

    If you have Office 2019 or 2021 or Microsoft 365, enter the following formula in I3:

     

    =TEXTJOIN(", ",TRUE,IF($A$3:$A$17=F3,TEXT($D$3:$D$17,"m/d/yyyy"),""))

     

    If you have Office 2019, confirm with Ctrl+Shift+Enter.

    Then fill down.

    You can then copy the range as values to another workbook.

     

    • espo610's avatar
      espo610
      Copper Contributor
      thats not working. BUT, maybe i was not clear. look at my 2 newest files. (what i have AND what i want) thanks

Resources