topic Merge Rows of Text Data in Discussions

Merge Rows of Text Data

NVarz — Sat, 10 Jun 2023 23:34:40 GMT

Hi,

I have an Excel file with Text Data in it. The format of the data in the Excel file looks something like this:

I want to merge the text in the Document Body Col in a single cell based on the Document Title. For example, instead of having two rows for File Name 1, I want one row for File Name 1 with the Document body concatenated together. This is not possible in Excel due to the character limit being exceed. Is there a way to perform this task in JMP Pro?

Thanks,

-N

Re: Merge Rows of Text Data

jthi — Tue, 03 Aug 2021 20:28:57 GMT

Using Query could work:

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(5),
	Compress File When Saved(1),
	New Column("Title", Character, "Nominal", Set Values({"A", "A", "B", "B", "B"})),
	New Column("Body", Character, "Nominal", Set Values({"A", "B", "C", "D", "E"}))
);

Query(Table(dt, "dt1"), "Select title, GROUP_CONCAT(body, ' ') FROM dt1 group by title");

SQL Functions Available for JMP Queries

You might also be able to do this with some split/stack/combine columns combination.

Edit: Using Transpose:

Result table:

Use Combine Columns with Row 1 - Row 3 selected:

Result:

Delete unnecessary columns:

Re: Merge Rows of Text Data

NVarz — Tue, 03 Aug 2021 21:58:35 GMT

Hi J! Thank you so much! That worked. Appreciate your help :)