Forum Discussion
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 Consider Power Query. See attached.
At the top what you want. The green table below what you get.
- 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.
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.
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.
- thats not working. BUT, maybe i was not clear. look at my 2 newest files. (what i have AND what i want) thanks
