Search code examples
google-sheetsconcatenationuniquetransposearray-formulas

Using UNIQUE and TRANSPOSE in a single formula in Google Sheets

I want to create an array of values using the email address as the unique value.

Scenario:

Each eCommerce order create a new row in a spreadsheet:

  1. The email of the user - user account
  2. A unique ID of a product
  3. Another unique ID of the product

In another sheet (or area), I want an array of data to show all product ID information for each user account. So If they purchase a product again, the data array simply updates the email line with the IDs. The IDs should be comma separated.

Thanks to player0 for starting this!

Here's a spreadsheet to play with: https://docs.google.com/spreadsheets/d/1TN9gk4mD9qXBBSd70SVvQlW5aMrQGEzDZe4kX2aGmGg/edit?usp=sharing

Solution

  • use:

    =ARRAYFORMULA(REGEXREPLACE(UNIQUE(ARRAY_CONSTRAIN({A3:A, 
 SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(A3:A=TRANSPOSE(A3:A),   
 TRANSPOSE(B3:B)&",", )),,ROWS(A3:A))))&"|", ",|", ), 
 SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(A3:A=TRANSPOSE(A3:A),   
 TRANSPOSE(C3:C)&",", )),,ROWS(A3:A))))&"|", ",|", )}, COUNTA(A3:A), 3)), 
 ", ", ","))

    0