Forum Discussion

gabby895's avatar
gabby895
Copper Contributor
Apr 26, 2024

Have a list automatically sort to group on another page in excel

Hi, I'm looking for a solution for billing for my company. Our current system: My boss writes down her work in a notebook as she completes it (ex. Client 1 Burger King 1 4/1/24). At the end of the month, I take the notebook and sort it all into the proper client group on our cover sheet. The info on the cover sheet pulls to individual invoices that we send to our clients. I am looking to have her keep track on a spreadsheet so I dont have to type everything out for her. She tried to just input them on our cover sheet but she got too nervous about messing something up, plus she found it time consuming to try and find the right client group. She'd prefer just have a spreadsheet open and write things as she goes.

 

This is a simplified version of what my billing looks like:

 

Client 1
Property# of filesDate
Mcdonalds454/1/2024
Burger King124/5/2024
 57$2,565
Client 2
Property# of filesDate
Taco Bell214/1/2024
Subway144/9/2024
Cookout254/14/2024
Wendys654/5/2024
 125$5,625
Client 3
Property# of filesDate
Hardys854/1/2024
Carls Jr.144/16/2024
Arbys134/5/2024
 112$5,040

 

This is what she would want to be typing as she works:

 

ClientProperty# of FilesDate
Client 1Burger King54/26/2024
Client 3Hardys44/27/2024
Client 2Taco Bell854/27/2024
Client 2Cookout444/28/2024
Client 3Arbys544/28/2024

 

My backup plan is just to filter by client, copy and paste to the coversheet. However, we have a few hundred clients so it would still be pretty time consuming. I want to figure out a way to have her information automatically end up in the correct client grouping. I hope I'm being clear enough. I've never posted here before but this is the first time I havent been able to figure something out 🙂

 

Thanks for your help!

  • gabby895 

    It is possible to build the result as a stacked 'table of tables' using 365 but the exercise is not trivial and it depends both on your data structure (where did the dollar sums come from?) and the exact version of Excel.

    =  LET(
        distinctClient,SORT(UNIQUE(Client)),
    
        properties, REDUCE("Property",distinctClient,LAMBDA(tbl,clnt,
          LET(
            pr, FILTER(Property,Client=clnt),
            spc, " ",
            VSTACK(tbl,pr,spc, spc)
          )
        )),
    
        count, REDUCE("Files",distinctClient,LAMBDA(tbl,clnt,
          LET(
            files, FILTER(countOfFiles, Client=clnt),
            subTotal, SUM(files),
            spc, " ",
            VSTACK(tbl,files,subTotal,spc)
          )
        )),
    
        HSTACK(properties, count)
      )

     

Resources