Extracting unique values is a common step in cleaning lists of names, products, categories or ids. Google Sheets can handle exact uniqueness with formulas, and Apps Script can help when you need custom normalisation or review output before keeping a final list.
Use formulas for exact unique values
For exact matches, the UNIQUE function is often the simplest option. For example, =UNIQUE(A2:A) returns distinct values from a range. You can combine it with SORT, FILTER or TRIM to clean input before extracting values.
Use Apps Script for custom normalisation
If you want to treat values as the same after trimming, case changes or simple character cleanup, Apps Script can build a normalised key and keep the first seen value.
function extractUniqueValues() {
var sheet = SpreadsheetApp.getActiveSheet();
var values = sheet.getRange("A2:A").getValues();
var seen = {};
var unique = [];
values.forEach(function (row) {
var raw = String(row[0] || "").trim();
if (!raw) return;
var key = raw.toLowerCase().replace(/\s+/g, " ");
if (seen[key]) return;
seen[key] = true;
unique.push([raw]);
});
sheet.getRange("B2:B").clearContent();
if (unique.length) sheet.getRange(2, 2, unique.length, 1).setValues(unique);
}Write the result to a separate column or sheet so the original data remains available for review.
When values are similar but not identical
Exact and normalised matching will not catch typos or spelling variations. In those cases, consider fuzzy matching and a review step before merging values. Flookup custom functions such as ULIST and DEDUPE can help with similarity-based unique lists and deduplication in Google Sheets. See custom functions for details, or ask the Sheets AI Assistant for a formula suggestion based on your ranges.