PDA

View Full Version : [SOLVED] Combine rows of data in to one cell



filla_dilla
02-19-2019, 12:29 PM
I have a worksheet that contains a unique ID in column B. In column G there is other associated data. How do I combine the several rows of data in column G into one cell? Example of column B and G:

1 ASDF
1 SDF
1 ADSF
1 ASDF
2 ASDF
2 ASDF
2 DFASDAS
2 ASDF

So I'd want all of the data associated with unique ID 1 in a single cell, but not in one long string.

p45cal
02-19-2019, 03:33 PM
Assuming you have headers in row 1 and the data body starts in row 2, you can have a formula in a spare column (I used column I) in row 2 such as:
=TEXTJOIN(" ",TRUE,IF($B$2:$B$9=B2,$G$2:$G$9,""))
This formula is committed to the sheet with Ctrl+Shift+Enter, not just Enter.
Then copy the formula down.
Then you can copy/paste as Values the results of that formula, in situ, then do a remove duplicates operation using only columns B and I to find those duplicates.
In the attached, I copied Sheet1 and did that (as well as deleting the data in column G).

filla_dilla
02-19-2019, 03:55 PM
Thank you very much! Any way to maintain the data so it's not one long string, e.g. end result in one cell would be:



ASDF


SDF


ADSF


ASDF

Instead of

ASDF SDF ADSF ASDF

p45cal
02-19-2019, 04:05 PM
try:
=TEXTJOIN(CHAR(10),TRUE,IF($B$2:$B$9=B2,$G$2:$G$9,""))

filla_dilla
02-19-2019, 04:25 PM
Thanks again for your help. When I try this i receive a #NAME? error. I've tried what was in the worksheet: =_xlfn.TEXTJOIN(CHAR(10),TRUE,IF($B$2:$B$9=B2,$G$2:$G$9,"")) as well as what you provided above: =TEXTJOIN(CHAR(10),TRUE,IF($B$2:$B$9=B2,$G$2:$G$9,""))

p45cal
02-19-2019, 04:42 PM
It looks like you're working on a Mac or a version of Excel prior to Excel 2016?
It's my bedtime here, so tomorrow sometime I'll cobble together a user-defined function to replace the missing TEXTJOIN function.
I'll probably steal it from https://www.reddit.com/r/excelevator/comments/5movbv/udf_textjoin_delimeter_ignore_blanks_valuerange/ (you could use that yourself?)

filla_dilla
02-19-2019, 05:07 PM
I'm on Excel 2016. I put that function in and it works now. Thank you very much!