Forum Widgets
Latest Discussions
A LAMBDA Word Search Puzzle
I've created a 15x10 Word Search. I've included a word list (dynamic range) containing words which might be found in the word search. The goal is to then extract those words when they appear in the word search. The catch is the words can appear 1 of 4 ways: horizontal left-to-right, horizontal in reverse, vertical, and vertical in reverse (No diagonals!). (The words currently in the puzzle are in blue for illustrative purposes) When I started this exercise, I thought of a solution involving SCAN and some text accumulation. There are a few issues with this method. SCAN would need to go through the puzzle twice (Can't VSTACK then SCAN) - once going through the range as normal then through the range when flipped (w/CHOOSECOLS and TRANSPOSE). The dimensions of each are not the same and it gets messy. Ultimately, the method I used starts by finding the position of words (Regular or in reverse) for a given row, pulling the words, filtering, splitting (Have to account for potentially multiple words in a line), stacking, and producing a list of words found. That's the short of it. You'll have to see the workbook. I'm interested in if SCAN can potentially make the solution a bit simpler.SolvedPatrick2788May 15, 2025Silver Contributor20KViews1like29CommentsSpill formula with cell reference from lambda map
Hi, Hopefully someone can give me any advice on the following. I want to combine a spilled formula array with some logic to determine the label of each cell. That determination is done where the formula looks at the value of the cell itself and the adjacent cell. In orange I tried to combine the spilled formula but it returns blanks. Formula is as follows: =LET(tbl;XLOOKUP(A2#&"|"&B1#;tbl_Data[Nummer]&"|"&tbl_Data[PostingDate];tbl_Data[Status];;0); MAP(tbl;LAMBDA(a; LET(b;INDIRECT(ADDRESS(ROW(a);COLUMN(a))); x;AND(OFFSET(b;;0)="Uitgebracht";ISERROR(OFFSET(b;;1))); y;AND(OFFSET(b;;1)="Opdracht";OFFSET(b;;0)="Uitgebracht"); z;AND(ISERROR(OFFSET(b;;0));ISERROR(OFFSET(b;;1))); IFERROR(IFS(x;"Vervallen";y;"Nieuw";z;"");""))))) Anybody any idea what goes wrong? Many thanks, MichielMichielS340May 15, 2025Copper Contributor56Views0likes2CommentsArray formula map lambda indirect address
Hi, The orange area contains a spilled array formula where I am trying to loop through a spilled array and for each cell I want to check the value and the value of last period and on basis of an if formula provide a certain label. Some how my formula give all blanks (orange part of the attached). I am not sure if the indirect address of the map formula works like this: =LET(tbl;XLOOKUP(A2#&"|"&B1#;tbl_Data[Nummer]&"|"&tbl_Data[PostingDate];tbl_Data[Status];;0); MAP(tbl;LAMBDA(a; LET(b;INDIRECT(ADDRESS(ROW(a);COLUMN(a))); x;AND(OFFSET(b;;0)="Uitgebracht";ISERROR(OFFSET(b;;1))); y;AND(OFFSET(b;;1)="Opdracht";OFFSET(b;;0)="Uitgebracht"); z;AND(ISERROR(OFFSET(b;;0));ISERROR(OFFSET(b;;1))); IFERROR(IFS(x;"Vervallen";y;"Nieuw";z;"");""))))) Somebody any suggestion on this? Many thanks,MichielS340May 15, 2025Copper Contributor22Views0likes1CommentLambda map formula and spilled range
Hi, In the attached file I try to have one formula that takes a spilled range and based upon the value of a cell in the spilled range (compared to the cell one column to the left) I want to return a specific label (result in the yellow areas, the orange area tries to do the same but then in one spilled formula). Somehow the results remain blanks. Anyone any advice on the map formula that compares the cells? Many thanks, MichielMichielS340May 15, 2025Copper Contributor18Views0likes1CommentUsing Filters to Auto-Update Complex Formulas
Hi, This is my first post here. Hoping someone here may be able to assist me. I have the below spreadsheet. There is currently 500 rows of data. At the Top is a Summary Grid with formulas summarizing the data below (Rating, Employee Level, etc. ) I'm also showing an example of one of the formulas in Cell F3. The other cells contain similar formulas. Ask: How do I get the Summary Grid to only display the data based on the rows visible, which is determined by Column T filter selections. For example, if the user selects "Level 4 Manager 2" and this manager has 10 employees, how do I get the Summary Grid to display just the data based on those 10 employees (versus the total 500). I've seen videos/sites that use the Subtotal or Aggregate formula, but it appears those can only be applied to specific functions, not a more complex formula. Is this possible?ebellezaMay 15, 2025Occasional Reader103Views0likes5CommentsMacro Error for Pivot Table
I can create a Pivot Table for the data I download and it works fine. However, when I made a Macro to do the same thing, I get an error. The error is: Run-time error '5': The following is a screenshot of the highlighted error in the Macro. I am now using Excel 2013, however I made an initial Macro using an older version of Excel and am now trying to make a pivot table using 2013 and installing it near the start of the existing Macro I have. How do I correct for this error?WayneEKMay 15, 2025Copper Contributor24KViews0likes9CommentsWhy isn't my boxplot displaying the data I provided?
I tried a different method of grouping my data together to make my boxplot but there's nothing in the chart. Why isn't it showing anything and how do I fix it? Please help as soon as possiblesknnakjsdicjMay 14, 2025Occasional Reader8Views0likes0CommentsComplicated formula help for a budget spreedsheet
Hello everyone, Normally I can find what I'm looking for and apply it to my project, but this one is a bit complicated, if it can even be done. I'll try to explain it as best as I can. I've got a calendar type layout on one tab(Forcast Template), with bills listed on anoter tab(All Bills). What I'd like to get it to do, is automatically fill in each bill for the corresponding day. The complications are as follows: some days have multiple bills due, and some bills only apply to certain months. I'm familiar with if/then functions as well as xlookup, but combining the two for this application is proving to be a tad more complicated. Again, that's if it can be done. Not sure how to attach the file for viewing, but I'll post screenshots so hopefully you can tell what I'm trying to do. Let me know if I need to clarify anything. Thanks!ComicazyMay 14, 2025Occasional Reader119Views0likes3CommentsVlookup or any better function
Dear Experts, Greetings! I have a data like below:- Sheet 1 & 2, and have to fetch the data from 2 -> 1. If , I use Vlookup with True(approximate match) then it populates many times , but I want to populate the exact time from sheet-2 and put it in sheet-1 when the RRC(Column F) triggered example shown as 1st line from Sheet2 What's the best way to achieve this, we work in terms of ms( milliseconds ) so can't do any Approximations. Thanks in Advance, Br, AnupamSolvedanupambit1797May 14, 2025Steel Contributor55Views0likes9Comments
Resources
Tags
- excel42,690 Topics
- Formulas and Functions24,768 Topics
- Macros and VBA6,420 Topics
- office 3656,050 Topics
- Excel on Mac2,658 Topics
- BI & Data Analysis2,380 Topics
- Excel for web1,927 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,640 Topics