Apr 26 2024 09:42 AM
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 files | Date |
Mcdonalds | 45 | 4/1/2024 |
Burger King | 12 | 4/5/2024 |
57 | $2,565 | |
Client 2 | ||
Property | # of files | Date |
Taco Bell | 21 | 4/1/2024 |
Subway | 14 | 4/9/2024 |
Cookout | 25 | 4/14/2024 |
Wendys | 65 | 4/5/2024 |
125 | $5,625 | |
Client 3 | ||
Property | # of files | Date |
Hardys | 85 | 4/1/2024 |
Carls Jr. | 14 | 4/16/2024 |
Arbys | 13 | 4/5/2024 |
112 | $5,040 |
This is what she would want to be typing as she works:
Client | Property | # of Files | Date |
Client 1 | Burger King | 5 | 4/26/2024 |
Client 3 | Hardys | 4 | 4/27/2024 |
Client 2 | Taco Bell | 85 | 4/27/2024 |
Client 2 | Cookout | 44 | 4/28/2024 |
Client 3 | Arbys | 54 | 4/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!
Apr 26 2024 10:35 AM
Perhaps you can use the attached workbook as starting point.
Apr 26 2024 12:04 PM
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)
)