Pivot Table Yearly Sales Report Issue
I'm currently working on creating a pivot table for a yearly sales report. However, I'm encountering an issue where, after a particular row, the year is not being recognized. This results in an error. Can someone please explain why this might be happening and guide me on how to resolve it? Additionally, is there a way to directly convert the date to year within the pivot table without having to separately convert the date to year first and then use the pivot table?
Grouping
Is there a way to group by just the column with the data in it? I have 20k+ rows of data. I need to label in Col A based upon the group in Col AK. I have already sorted the data so that the top row of each group is the one I need to label. I just need a way to see only the first row of each group. I need to see the rows with pink highlight only.Working with Arrays of Ranges
OK, so strictly speaking an array of ranges is an error in Excel right from the outset. However, I set up an array of tables contained within the named Range 'allocatedRange', here shown with a red outline. I then set up a function that would return the range corresponding to a country SelectDataλ = LAMBDA(country, LET( countries, FILTER(TAKE(allocatedRange, , 1), markers = "Country:"), recordNum, FILTER(SEQUENCE(ROWS(allocatedRange)), markers = "Country:"), recordEnd, VSTACK(DROP(recordNum - 1, 1), ROWS(allocatedRange)), rangeRows, recordEnd - recordNum, countryArrϑ, MAP( recordNum, rangeRows, LAMBDA(start, rows, LAMBDA(TRIMRANGE(TAKE(DROP(allocatedRange, start), rows)))) ), XLOOKUP(country, countries, countryArrϑ)() ) ) The start and row count of each table is determined by using the string "Country:" as a marker and differencing row numbers. A combination of TRIMRANGE/TAKE/DROP picks out each range in turn and assembles them into an array by turning the range references into thunks (TYPE=128). The function SelectDataλ is used to look up any specific country and return the corresponding range. To demonstrate that the function indeed returns ranges, the COUNTIFS function is used to count the number of cities within each country that have a population exceeding 100 000. = LET( countries, FILTER(TAKE(allocatedRange,,1), markers="Country:"), countLarge, MAP(countries, LAMBDA(country, COUNTIFS(TAKE(SelectDataλ(country),,-1), ">1000000") ) ), HSTACK(countries, countLarge) ) The point of this post is to introduce the idea of treating tables as elements of an array, so allowing further tables to be inserted or removed dynamically. TRIMRANGE ensures that each range is sized correctly and MAP is used to analyse each table in turn. Whilst Excel throws hissy fits at being asked to store arrays of arrays, arrays of ranges, or nested arrays, it is perfectly happy to store arrays of functions. When evaluated, each element of the function array is perfectly free to return an array or even a range. The effect is to permit Excel to process 'arrays of Tables' faultlessly.Mail Merge to Email Addresses
Need some help. I am using Excel as a data source and created word document templets for mail merging. With in the data source I have for columns for entering email addresses. Personal Email address Work Email Address Supervisor Email Address Section Email Address Combined Email Address When I need to send an email to an individual that also needs to go out to all the emails, I have joined the email addresses together using the following formula and have the combined email addresses in a column called Combined. =LET(email,TEXTJOIN("; ",TRUE,T3:U3,AS3:AT3),IF(email="","",HYPERLINK("mailto:"&email,email))) This works with no issues and am able to click on a combined email line and it launches the email program and drops the line of EMAIL ADDRESSES into the email TO: location. The issue I need help with, if possible, is the interaction with the combined email and word mail merge. When I try to do the mail merge and select the Combined Email Address column, word gives me an error, "Microsoft Outlook does not recognize " the list of combined email addresses". Is there a way for outlook to recognize the combined email addresses during the merge? Any help would be greatly appreciated. Carl
Power Query Formula to Calculate Current Count From Previous Count per Name using Date
Hi everyone! I have a dataset with the following columns: Point Name, Pump Count, Record Date, and Index. My goal is to create a calculated column in Power Query that computes the following for each Point Name, based on the Record Date: If (Current Pump Count - Previous Pump Count) >= 0, apply: (Current Pump Count - Previous Pump Count) * 0.3 If the result is negative, use this formula instead: ((1,000,000 - Current Pump Count) + Previous Pump Count) * 0.3 Problem: I tried duplicating the Pump Count column and creating a custom column for the previous count using Table.Buffer, but it didn’t align correctly by Point Name and Record Date. Indices may not be sequential, so referencing the immediate index won’t work. I considered grouping by Point Name and sorting by Record Date to dynamically calculate the Previous Pump Count, but was unsure. I tried this formula for Previous Pump Count but it doesn’t align correctly for Point Name or handle skipped indices. My Current Formula: Table.Buffer(#"Duplicated Column3")[Pump Cycle Count]{[Index] - 1} otherwise null Desired Process: Group by Point Name. Sort within each group by Record Date Use the formula to compute the difference: If (Current Pump Count - Previous Pump Count) >= 0, apply: (Current Pump Count - Previous Pump Count) * 0.3. Else, apply: ((1,000,000 - Current Pump Count) + Previous Pump Count) * 0.3. How can I implement this in Power Query, ensuring it dynamically references the Previous Pump Count for the same Point Name, even if indices are skipped or Record Dates are close?I don't understand this logic with StockHistory function...
I don't understand this.... I will show my question step by step: 1. =STOCKHISTORY(E4,TODAY()-10,TODAY(),,0,1) Gives me an array. 2. I use ROWS and INDEX to get the last number of the array. =INDEX(STOCKHISTORY(E4,TODAY()-10,TODAY(),,0,1),ROWS(STOCKHISTORY(E4,TODAY()-10,TODAY(),,0,1))) gives me the last number of the array. 3. I change "E4" to "E4#" =INDEX(STOCKHISTORY(E4#,TODAY()-10,TODAY(),,0,1),ROWS(STOCKHISTORY(E4#,TODAY()-10,TODAY(),,0,1))) Gives me the 0.67....which is the price of "8zz" the last one on E4#. I was expecting to get the last number of each and every StockHistory array of every elements in E4#... Instead, I only get the "first" number of the last element in E4#. I don't understand the logic of this function....Can anybody explain this strange behavior? Thank you very much!
Help Excel
Hello, Here is my problem: I have a list of price (attached in the message). And then a list of customers to invoice. For each customers, I have the Code they belong to and then a number of user which may vary from one customer to another. Which formula could I use to search for the Price depending on the Code and then the range of Users please? Thank you in advance for your help,
VBA requirement
Hi everyone, I’ve written a VBA script to dynamically retrieve and display data based on user interactions in an Excel workbook. The script triggers when I select a cell in Sheet1 (which contains product names) and performs the following tasks: Searches for the selected product in Sheet2, retrieves its category and price. Looks for matches in Sheet3 based on the category or price. Copies matching rows from Sheet3 into Sheet4, grouping results by category, and organizes them in separate column groups (e.g., Columns A-J for one category, K-T for the next, etc.). Additionally, the script clears previous results in Sheet4 and displays a message if no matches are found. What I Need Help With: Optimizing the script to handle larger datasets more efficiently. Suggestions for improving readability and maintainability of the code. Advice on handling edge cases or potential bugs I might have missed. Any recommendations to enhance the functionality or logic. I’d really appreciate your feedback and suggestions! Let me know if you need more details about the logic or the structure of my workbook. Thanks in advance! 😊
