0

Package Payments Sheet

B C
1 Users Total Numbers
2 User 1 2
3 User 2 4
4 User 3 1
5 User 3 2
6 User 3 4
7 User 3 7

Desired Results

A B
1 Users Total Numbers
2 User 1 2
3 User 2 4
4 User 3 1,2,4,7

Here's my current formula:

=ARRAYFORMULA({"Client Payments"; JOIN(",", VLOOKUP(B2, 'Package Payments'!B2:E, 2, FALSE))})

I'm using VLOOKUP to search for a variable, and return values related to that variable in a single cell.

I'm trying to create another formula for my project.

I had thought this code would reiterate and produce the results for all users. I also want to avoid using query as I want to repeat the code throughout the sheet.

4 Answers 4

1

=ArrayFormula(TEXTJOIN(",",1,IF(D1=$A$1:$A$6,$B$1:$B$6,""))) :


answer for this question

Even though this formula contains an ArrayFormula function it still needs to be copied down to E2, E3, etc.

0

Use filter(), like this:

=textjoin( ", ", true, filter('Package Payments'!C$2:C, 'Package Payments'!B$2:B = B2) )

Alternatively, try this pattern to get a multi-column table that shows all numbers for all usernames:

=filter( 
  query( 
    { 
      'Package Payments'!B2:C, 
      countifs('Package Payments'!B2:B, 'Package Payments'!B2:B, row('Package Payments'!B2:B), "<=" & row('Package Payments'!B2:B)) 
    }, 
    "select Col1, max(Col2) 
     where Col1 is not null 
     group by Col1 
     pivot Col3", 
    0 
  ), 
  sequence(counta(unique('Package Payments'!B2:B)) + 1) > 1 
)

To place those results in your sample spreadsheet, looking results up by client name, put this formula in cell 'Package Clients'!J1:

=arrayformula( 
  { 
    "Package Payments"; 
    transpose( 
      query( 
        transpose( 
          iferror( 
            vlookup( 
              B2:B, 
              filter( 
                query( 
                  { 
                    'Package Payments'!B2:C, 
                    countifs('Package Payments'!B2:B, 'Package Payments'!B2:B, row('Package Payments'!B2:B), "<=" & row('Package Payments'!B2:B)) 
                  }, 
                  "select Col1, max(Col2) 
                   where Col1 is not null 
                   group by Col1 
                   pivot Col3", 
                  0 
               ), 
                sequence(counta(unique('Package Payments'!B2:B)) + 1) > 1 
              ), 
              sequence(1, 99, 2), 
              false 
            )  
          ) 
        ), 
        "", 
        9^9  
      ) 
    ) 
  } 
)
4
  • Hi there! I tried the first code you sent: — =textjoin( ", ", true, filter('Package Payments'!C$2:C, 'Package Payments'!B$2:B = B2) ) — It works, but it's only fetching values from a single user. I tried extending the search for the variable by using : 'Package Payments'!B$2:B = B2:B However, it is not working, it is only displaying one value. I want it to display values related to the proeperty. I appreciate you giving the alternative formula. However, I wanted to collate the results to a single row.
    – revilio.
    Feb 26, 2022 at 7:58
  • The second formula gets all the results for a username in the same row. If that is not what you wanted, consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your hand-entered expected results there. Feb 26, 2022 at 9:08
  • Hi Bud! Thanks, here's a link for the Google Sheet File: docs.google.com/spreadsheets/d/… If you'll check on the Package Payment Tab. There are two columns there labelled. Package Payments, and Client Payments. I want Package Payments to have the same Result as Client Payments.
    – revilio.
    Feb 28, 2022 at 0:08
  • Edited the answer. Your sample spreadsheet has more than 30,000 blank rows on most tabs. Delete those superfluous blank rows before applying the array formula. Feb 28, 2022 at 6:58
0

Here is my proposed solution: enter image description here

Formula Used (Improved - to include open range as well):

=ArrayFormula({UNIQUE(FILTER(A1:A,A1:A<>"")),transpose(regexreplace(trim(query(transpose(REGEXEXTRACT(query(A1:A&"~"&B1:B,,3^6),REGEXREPLACE(query(A1:A&"~"&B1:B,,3^6),"("&UNIQUE(FILTER(A1:A,A1:A<>""))&"~)(\d+)","$1($2)"))),,4^6))," ",","))})
0

Formula

=BYROW(TOCOL(UNIQUE(A2:A), 1), LAMBDA(r,
   {r, JOIN(",", FILTER(B2:B, A2:A=r))}))

Sample Calculation

Explanation

  1. UNIQUE and TOCOL are combined to create unique list of users without a blank value.
  2. BYROW passes each row one-by-one into a LAMBDA function
  3. LAMBDA stores the current row's value in the variable r
  4. LAMBDA's formula creates a 2-column array:
    • The first column is the value stored in r
    • The second column's is returned by filtering the Numbers column B2:B for all rows where the Users column A2:A = r, then JOIN is used to concatenate the results.

Functions

BYROWFILTERJOINLAMBDATOCOLUNIQUE

0

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.