Merge extra cells into single record

[nicejacksons]

Hello! Looking for some help organizing a dataset that isn't importing from an Excel file as intended. As you can see in the picture below, the primary fields are spaced out, separated by extra rows made for each "Regulation" data point. For example, all the "Regulation" data points from "FDA Guidance 2001i" to "OECD GLP No. 13iii" should be merged into one multiple select field for the BAV 1.0 record. Similar for BAV 2.0, "21 CFR 320.29" and "FDA Guidance 2001" should not be split into two rows, but merged into the same "Regulation" multiple select field.

Looking for a way to automate or at least quickly go through this data set merging these extra records. Thank you!!

 

[Andy_Lin1]

The easiest solution would be to redo the import. Your screenshot doesn't show if the field that you're trying to combine is set as a multiple select or single select field, so confirm that it is set up as a multiple select field before redoing it.

If you're creating a new table from your import, click the column headers and set the correct field type for each column. It defaults to "Long Text" for text fields.

Otherwise, if it all looks correct, then you might need to do some troubleshooting on the original Excel sheet. How is the data laid out there? Are the different regulation data points on their own row (as in the imported table)? If so, you'll need a helper column that joins all of the regulation data points into a single cell (e.g. =JOIN(",",D1:D3) ; note that there are array formulas that can do this without you having to manually change the data range for each item, but that requires a bit more information about the spreadsheet).