Google Spreadsheets - Combine Column Data into One Column

Today I had the need to take a comma separated list from a form and create a column with the unique entries from that list.  Is it doable with Google Spreadsheets?  Most definitely!

Here are the steps:

1. Use the SPLIT function to divide your entries to individual cells.  This will use as many columns as number of entries.  (More info about SPLIT: the first parameter is the cell the data is coming from, the second parameter is what is used to divide the information.  Since I don't want the comma OR the space, my second parameter has both in the quotation marks, so: quote comma space quote .)
=SPLIT(A1, ", ")


2. Fill down as needed

3. Use the UNIQUE function to start creating your new list
=UNIQUE(C1:C)

4. Add addition columns of information with curly brackets & semi-colons
=UNIQUE({C1:C; D1:D; E1:E; F1:F; G1:G})

5. Add the SORT function if you want the list alphabetized
=SORT(UNIQUE({C1:C; D1:D; E1:E; F1:F; G1:G})

That's it! 

Comments

Popular posts from this blog

Google Script - Create a Drop-Down List from Spreadsheet Column

Google Sheets - Flipping Your Rows & Columns

Google Forms - Copy Summary Graph/Diagram to Use Elsewhere

Google Script - Create Calendar Events from Spreadsheet Data

Google - Reset a Form for Reuse with the new School Year