Only keep cells with values

G_SAND

Hi.

I'm sure someone can help me with this one. I am at a certain point in my data prep and now have a spreadsheet that looks similar to this:

Header	A	B	C	D	E
GS	GW				RR
GS		JP
GS		KB	SO
GS					PC
JJ	CL
JJ		MW
JJ	BG			FL

And I want it to look like this:

Header	A	B	C	D	E
GS	GW	JP	SO		RR
GS		KB			PC
JJ	CL	MW		FL
JJ	BG

The spreadsheet in question has ~150 different headers and columns A through to Z.

I hope there is a simple solution.

Thanks

nick_ceneviva

The attached Workflow gets the desired output you called out in your question.  It should be flexible enough that you won't have to change too much to handle many more columns.

Step by Step Walk through of workflow:

- The Summarize tool groups by the header value and then concatenates all of your other columns.  You would need to configure this tool to make sure it is summarizing all of the additional columns (Should be the only change).

- Using the header as your key, transpose all of the concatenated fields, which will allow you to handle all of the columns at once

- using the Regex parse tool, you can tokenize all of the values into separate columns.  this will need to be configure in such a way that you have enough columns for the number of rows per header.

- Use another transpose tool, to transpose the newly created columns from the Regex tool.  This will use the header and the name field from the first transpose as the key fields.

- The summarize tool groups by the Name and header field and concatenates all of the values.  At this point we are basically back to the where we were after the first transpose tool, but all of the extra commas are removed.

- Using the text to column tools, parse the concatenated values field by the columns.  Configure the tool to be split to rows so that you get separate rows for where there are multiple values.

- Use the multi row formula tool to create an index for where there are multiple rows per header and name field.  This will make it so the cross tab tool does concatenate the values back together

- Use the cross tab tool with the grouper fields being the header and the newly created index.  The label field will be the name and the values field will be the value.  Select the concatenation aggregation, but nothing will be concatenated

- The last two tools are for some final cleanup to rename the columns and drop the unnecessary columns.  By using the dynamic rename, you won't need to rename each individual column.

Hope that helps!

OnlyKeepCellsWithValues.yxmd