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

Copper Contributor

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
Burger King124/5/2024
Client 2
Property# of filesDate
Taco Bell214/1/2024
Client 3
Property# of filesDate
Carls Jr.144/16/2024


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!

2 Replies


Perhaps you can use the attached workbook as starting point.


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(

    properties, REDUCE("Property",distinctClient,LAMBDA(tbl,clnt,
        pr, FILTER(Property,Client=clnt),
        spc, " ",
        VSTACK(tbl,pr,spc, spc)

    count, REDUCE("Files",distinctClient,LAMBDA(tbl,clnt,
        files, FILTER(countOfFiles, Client=clnt),
        subTotal, SUM(files),
        spc, " ",

    HSTACK(properties, count)