Conditional Concate Cell content from different rows

PIN

Hello,

 

Having one case as showing below.

I'd like to concatenate the different rows of "Description" for the same "Case#" into one combined cell which is aligning with "Case#"

e.g. C2 is concatenating of B2, B3, B4 which are all for "Case 1"

      C5 is concatenating of B5 which is for "Case 2"

      C6 is concatenating of B6, B7 which are all for "Case 3"

In the end, will delete redundant rows just leaving one row for one Case# with its descriptions in the 2nd column

 

The possible #of description rows varies for different "Case". There are few thousands of "Case", it's impossible to do it manually 

I'd like to seek some advice to do it in JSL if possible, thank you  

 

brady_brady

Hi Pin,

 

Here is JSL to do this. I've also another way to do it interactively, using tools that automate some of the processes Don describes. Let me know if you need those; they are in the "Data Table Tools" addin on the file exchange.

 

As a side note, step 1 of Don's process you can do by selecting all rows in the column, right-clicking, and selecting "Replace Missing with Previous Value". (Note that this menu option only appears when there are actually missing values, or empty strings, present in the column.)

 

Back to the script:

 

The script below assumes column 1 is named "col1", column 2 is named "col2" and creates a column called "Combined", so change these to your preference.

 

I've attached a table with the script enclosed; try it, I think it will do what you want.

 

Cheers,

Brady

 

Names Default To Here(1);

dt = :col1 << get data table();

r = (dt << get rows where(:col1 == "" ));
for(i=1, i<=nrow(r), i++, dt:col1[r[i]] = dt:col1[r[i]-1]);

dt << new column ("Combined", character);

r = 1;

while (r <= nrow(dt),
	set = dt << get rows where(:col1 == dt:col1[r]);
	dt: Combined[r] = concatitems((dt:col2 << get values)[set], " ");
	r += nrow(set);
);

ForPinCombinedCol.jmp