Forum Discussion
Help with weekly data project
Hi! I work with attendance data every week and don't know much about Excel or how I can make things quicker and easier. I work with attendance data, which comes from our ticketing service in three columns - amount of tickets registered in each order, visitor zip code for those tickets, and the day of the week the tickets were for (see example below). Basically, I need the zip codes to each appear in a separate column the amount of times that a ticket is made with that zip code (so if there are 9 total tickets for the zip code 19107, the new column would have nine cells reading 19107). These new columns would represent the day of the week the ticket occurred (see second image below) From this point, I have shortcuts that make the data easy to work with for the purposes of my reporting, so no recommendations are needed for how to take the data forward from there. I just don't want to have to individually type zip codes for the amount of times they occur. Please let me know what I can do or if further explanation is needed.
Hi, enclosed you will find a solution for your task. This is also suitable for writing a user-defined function as LAMDA. The function fances() is defined in the name manager in the attached example.
=LET(f_amoti,FILTER(A1:A5,C1:C5=E1),f_zipc,FILTER(B1:B5,C1:C5=E1),XLOOKUP(SEQUENCE(SUM(f_amoti)),SCAN(0,f_amoti,LAMBDA(a,b,SUM(a,b))),f_zipc,"",1))=LAMBDA(amoti,zipc,dow,f_day,LET(f_amoti,FILTER(amoti,dow=f_day),f_zipc,FILTER(zipc,dow=f_day),XLOOKUP(SEQUENCE(SUM(f_amoti)),SCAN(0,f_amoti,LAMBDA(a,b,SUM(a,b))),f_zipc,"",1)))=frances(A1:A5,B1:B5,C1:C5,H1)In summary, the formula filters two ranges (A1:A5 and B1:B5) based on a condition (values in C1:C5 equal to E1), generates a cumulative sum of the filtered values in A1:A5, and uses these sums as lookup criteria to find corresponding values from the filtered values in B1:B5. The formula then returns a list of the found values.
fintechzoompro @Our weekly data project involves the systematic collection, analysis, and reporting of key performance metrics to monitor and improve our operational efficiency. Each week, we gather data from various sources, including sales figures, customer feedback, website analytics, and production outputs. This data is then meticulously cleaned and organized to ensure accuracy and consistency.
