2

In Google Sheets I have two or more columns of data:

  1. Column 1 is a list of items that may include duplicates.
  2. Column 2 (and any additional columns), contains keys each associated with the item from the same row.

I want to return a two-column array where:

  1. Column 1 is a list of unique items
  2. Column 2 is a comma delimited value made by joining all the unique keys associated with the item from that row.

Example A:  One Key Col

A B C D E F
1 Item Key Item Key
2 foo apple foo apple,orange
3 bar apple bar apple,orange
4 foo orange
5 bar orange
6 bar apple
  1. Data in A:B.
  2. Results in E:F.
  3. E:E contains unique items fromA:A
  4. Keys for bar in F3 contain only one of the duplicates fromB3 and B6

Example B:  Multiple Key Cols

A B C D E F
1 Item Key Key2 Item Key
2 foo apple grapes foo apple - grapes,orange - apple
3 bar apple plum bar apple - plum,orange - test
4 foo orange apple
5 bar orange test
6 bar apple plum
  1. Data in A:C.
  2. Keys includes both Columns B and C.
  3. Keys for foo in F2 contain only one of the duplicates fromB2 and C4

Current Formulas

  1. For the items results column I have:
    =UNIQUE(A1:A)
    
  2. For the keys results column, starting from Row 2, I have:
    =JOIN(",", FILTER(B:B, A:A=E2))
    =JOIN(",", FILTER(B:B, A:A=E3))
    =JOIN(",", FILTER(B:B, A:A=E4))
    =JOIN(",", FILTER(B:B, A:A=E5))
    =JOIN(",", FILTER(B:B, A:A=E6))
    

New Formula Needed

My data is dynamic and I don't want to add a new formula for every new row of data.

I also don't want to have to edit the existing formulas if another keys column gets added or one is removed.

I'm looking for a single formula that will perform all the calculations while ignoring blank rows and skipping duplicates.

I tried the following unsuccessfully:

=ARRAYFORMULA(JOIN(",", FILTER(B:B, A:A=FILTER(E2:E, E2:E<>""))))

Which formula can I use to return the results I described in Google Sheets?

Note that I have a similar Excel specific question on Super User.

9
  • 1
    The source is just external and arrives like that. Regarding the multiple version (second table), in column E there should be just 3 responses, no more. Each of them has the items from column A with their matches from both B and C. Regarding your answer, let's discuss it in the answer's comments, continuing there.
    – LWC
    Apr 6 at 10:55
  • I updated my answer to use CHOOSECOLS for the key array, which will adjust to the number of Key ColumnS, kcs, you include in your range. eg. A1:B=1kcs, A1:D=3kcs, {A1:B;D1:D}=2kcs
    – Blindspots
    Apr 6 at 20:33
  • 1
    Thanks, I've modified the examples because you've changed the principle of x-y,a-b to x,y,a,b.
    – LWC
    Apr 7 at 21:15
  • 1
    No, neither is expected.
    – LWC
    Apr 8 at 19:14
  • 1
    I've updated the formula to join the key columns on a dash.
    – Blindspots
    Apr 8 at 21:25

1 Answer 1

2

Formula

=LET(arr,QUERY(A1:C, "WHERE Col1 <> ''", 1),
   itm,INDEX(arr,,1), kcols,CHOOSECOLS(arr,
     SEQUENCE(1,COLUMNS(arr)-1,2)),
  key,BYROW(kcols, LAMBDA(r, TEXTJOIN("-",1,r))),
   BYROW(UNIQUE(itm), LAMBDA(r, 
     {r, TEXTJOIN(",", 1, UNIQUE(FILTER(key, itm=r)))})))

Explanation

  1. LET is used to store intermediate calculations in named variables.
  2. arr is an array created from the range of items and keys columns after removing rows with no item.
    • Column 1 is always the list of items
    • Column 2 - Column ? are keys columns associated with the item on the same row.
  3. itm stores the items column from arr.
  4. kcols stores the keys column(s).
  5. key stores kcols after first applying TEXTJOIN to each row delimited by a dash - using BYROW.
  6. Lastly another BYROW passes each unique itm, one-by-one into a LAMBDA function that stores the current row in r.
  7. For each r passed in, the LAMBDA's formula returns a two-column array:
    1. The first column is r
    2. The second is a text string made by joining all the values from key delimited with a comma , where itm=r while skipping blanks.
4
  • You've updated your answer to switch from FILTER to QUERY ("This will make it easier for you to add and remove ranges while using the same basic formula."). It looks simpler indeed, but works just the same, i.e. it handles the singular column case and not the one with multiple columns. In the latter case there should be just 3 responses, which are the items from column A with their matches from both B and C in the same response. Just as shown in the question's second table.
    – LWC
    Apr 6 at 10:59
  • Was hard to follow the new requirements as was the original question but I have adjusted the formula to address what you want, and edited the question to make it clearer.
    – Blindspots
    Apr 6 at 21:57
  • Thanks, but you've also edited to change the principle of x-y,a-b to x,y,a,b, so I've had to fix. Can you adjust the formula?
    – LWC
    Apr 7 at 21:17
  • 1
    Thanks for updating and wow!
    – LWC
    Apr 9 at 8:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.