Date base data sheet generation from master sheet
I have one google sheet with two tabs one the master named "All Week Revenue - MasterSheet" where the importrange data will get updated date wise regularly of our sales team. I need the individual sales count along the total sales volume (TSV) and deposit amount (DP Amount) to a new tab named "DAILY SALES COUNT". based on the dates.
here is the sample link for the google sheet:
I have mentioned how the output should be in "DAILY SALES COUNT" tab.
From master sheet I need only info from cell A, B, D, G & H.
where A & B are name of Manager and sales person, D is TSV, G is DP Amount and H is Dates.
I need the count of TSV column D followed by values of D and G
I tried pivot however is gives me the count of per day sales but the TSV and DP amount is showing only the grand total and not date wise. Tried Vlookup with my little knowledge not of no use.
Here's one approach added to your test sheet:
=let(Σ,'All Week Revenue - MasterSheet'!A2:H12, a,index(Σ,,1),b,index(Σ,,2),d,index(Σ,,4),g,index(Σ,,7),h,index(Σ,,8),Λ,sort(unique(tocol(h,1)),1,),
x,vstack(,{"MANAGER","BDA E-MAIL"},unique(filter({a,b},a<>""))),
reduce(tocol(,1),sequence(rows(x)),lambda(i,c,ifna(vstack(i,let(Δ,chooserows(x,c), hstack(Δ,reduce(torow(,1),Λ,lambda(f,q,hstack(f,if(+Δ="",hstack(text(q,"mm-dd-e"),,),if(+Δ="MANAGER",hstack("Count","TSV","DP"),let(z,join(,Δ),index(let(Ξ,hstack(countifs(h,q,a&b,z),sum(ifna(filter(d,h=q,a&b=z))),sum(ifna(filter(g,h=q,a&b=z)))),if(Ξ=0,,Ξ))))))))))))))))
- Change the range from
'All Week Revenue - MasterSheet'!A2:H12to'All Week Revenue - MasterSheet'!A2:Hwhen you are implementing it in real.
- Conditional formatting if cell past date and another cell empty with arrayformula
- ranking formula start from negative number
- Trouble getting log function to work in a custom function for Google sheets
- Checking for an option in a multi-select dropdown
- ARRAYFORMULA SUM
- Display pasted cells temporarily then return to main page
- How can I search a Google Sheet for the cell that matches the intersection of the first column and top row of another sheet
- Javascript case sensitive
- Can I alter a formula using the cell number instead of its cell value?
- Dynamically referencing worksheets to dynamically fill a table based on a condition
- How do you add UI inside cells in a google spreadsheet using app script?
- How can I force data input into cells in Google Sheets to be in a desired date format?
- How do I find a partial match from a data table to an individual cell?
- Highlighting non-first instance duplicates of words and counting highlighted cells
- Lock google sheets for data entry after a wrong entry
- Schedule View: INDEX MATCH from table and return multiple cells into one
- How to Show/Hide rows in google sheets using a script?
- Popping a Modal Dialog when changing the drop down entry in Google Sheets
- Transfer background color from cell range to drop down options
- How can I match a smart chip using SUMIF?
- List Google drive folder contents to google sheets with only new files
- Trying to figure out why my GoogleSheets Date formula is not working
- Count if name matches and outcome is complete
- Google Sheets Row Number of last row in a defined table
- How can I use INDEX and MATCH along with conditions to filter data into different tables?
- Google Sheets formula to remove all punctuation excluding period (.)
- Formula to find missing serial numbers
- Google Sheet Vlookup Multiple Criteria
- Copy Data from one column and past at the end of another column
- Query formula not grouping properly