Regrouping Delimited Data

Screen Shot 2020-11-10 at 2.47.09 PM.png

I pull data from an internal system and add it to Smartsheet via Data Uploader. When it comes in, it looks like the above, with Biz Entity, Sub Business Entity and Product Family grouped as delimited data. However, I want to regroup it so that each Biz Entity/Sub Business Entity/Product Family group is together in a single cell - like this:

Data Center Networking-NX9K-APIC

This has turned into a nightmare scenario where I am exporting the data from the internal system to Excel, converting the delimited data to columns, trimming the data to remove leading spaces, then regrouping it using TEXTJOIN before running it through Data Uploader. The result is adding dozens of otherwise useless columns to the sheet, and I have to believe there is an easier way to do this, but I'm scratching my head. Any ideas?

Find more posts tagged with

Formulas

Comments

Bee Ng

Hi @Danielle Arteaga ,

It sounds like you may want to take a different approach to solve this problem.

First, I would suggest seeing if you could get the data from the source system in the format that you need it. I know for many systems, there is an option to create reports, like we can in Smartsheet, and specify the columns you want to include, or even prep and merge fields together before exporting from the source system.

If that’s not possible, then a second option may be to do all your pre-processing in Excel. This is the option that I use most. In many cases you can use standard Excel functions to achieve the format of the data you need, and you can exclude unwanted columns in the process, so that you end up with only the one column of data that is needed, in your case.

One of the capabilities in Excel that can help with this pre-processing is “Get & Transform”. It is super powerful and can easily take source data and convert, reformat, merge, filter, etc. the data into the shape you want it to be. Then you can save that process so you can invoke it at any time.

Trying to do this in Smartsheet will be challenging.

Hope this helps.

Bee

Business Manager, AcuWorkflow

https://www.acuworkflow.com/

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.