Forum Widgets
Latest Discussions
Filter result to skip selected columns
Hi all Question - when outputting a filter, can selected columns be skipped in the output? Please see my example spreadsheet. I basically want to skip column H and have the total populate in column I instead. Is this possible please? Thanksmatt0020190Jan 19, 2025Brass Contributor37Views0likes2CommentsInsert Options button on Mac
Hi, I want to insert a new column in a table. However, I want that column to be blank. Thus, I must use the "Insert Options" button after I have inserted the column. I cannot find this button / it doesn't show. I have checked so that "Show Insert Options" is turned on in settings. I use Mac. How can I find this button? //FilipFilipDevalliusJan 19, 2025Copper Contributor14Views0likes1CommentColler une valeur sur des données filtrées est désormais également collé dans les lignes masquées.
Bonjour, Je viens de changer d'emploi et dans Excel lorsque je copie des informations de cellules filtrées et que je les colle dans la colonne d'à côté les données se collent sur les lignes masquées et non pas les lignes visibles uniquement. J'ai compris que je peux passer par une fonctionnalité Atteindre/Cellules visibles uniquement pour coller, mais cela prends du temps. Dans mon ancien emploi, mon Excel ne réagissait pas comme ça. Je pense que c'est un sujet de version d'excel ou de paramétrage de base. Quelle version ou quel paramétrage peut me permettre de revenir au fonctionnement que je connais. MerciPaulvJan 19, 2025Copper Contributor9Views0likes1Comment- MITHUNBMJan 19, 2025Copper Contributor10Views0likes1Comment
Need to return col A if another col contains the text in col B (array) anywhere
So, here's my array in columns A and B: Column L has a journal description that COULD contain one of col B descriptions. So, column L will have things like "3M LO(a)N 2024.12" OR "3M LO(a)N 2024.11", etc. But they could also have "TRU UP COLORADO INTEREST #3126" - basically, the description in col B could be anywhere (together or separated) in Col L. What I want to do is, in Column C write a formula that will look at column L and compare it to the array in columns A & B, and then return column A if the value in col B is found. So, for "3M LO(a)N 2024.12", col C would return "1". For "TRU UP COLORADO INTEREST #3126", return "5". The difference being "3M LO(a)N" is found in the same sequence as it is in the array, but "COLORADO #3126", though contained in col L, the two parts are separated. I'd like to leave these descriptions as they are to help in identifying if/when I need to change the array, but if it's too difficult, I could just use "#3126" as the description in col B and add a vehicle description in another column. The array is named "CATEGORIES" to ease formula creation. Thanks for any help in advance. *** apparently the full word LO AN is not allowed in the text. So, LO(a)N it is.RandomPanda1933Jan 18, 2025Copper Contributor45Views0likes1CommentWhat do you think of thunks?
OK, so the most likely response by far is going to be "I don't". However, I tried one of Omid Motamedisedeh's regular challenges and found it a suitable problem for exploring some of the lesser known byways of modern Excel. The challenge is to pick out locally maximum values from a rolling range. What I did was to write a function that used MAP to select one cell at a time, using DROP to remove the initial cells and TAKE to return a range of 5 cells with the active cell in the middle. The direct route to solving the stated problem would be to calculate the maximum value within each range immediately, but I was interested in the more general problem of "could I return the array of ranges in a form that would support further analysis?" As shown, the following formula ROLLINGRANGEλ = LAMBDA(values, n, LET( rows, SEQUENCE(ROWS(values)), MAP(rows, LAMBDA(k, LET( m, QUOTIENT(n, 2), rng, TAKE(DROP(values, k - m - 1), MIN(k + m, n)), rng ) ) ) ) ); gives and array of ranges error, but simply by enclosing the 'rng' variable within a further LAMBDA ... LET( m, QUOTIENT(n, 2), rng, TAKE(DROP(values, k - m - 1), MIN(k + m, n)), LAMBDA(rng) ) will cause Excel to return an array of functions, each one of which would return a range if evaluated. In the attached workbook, a number of formulae are based upon this array of functions = ROLLINGRANGEλ(dataValues, 5) = MAP(ROLLINGRANGEλ(dataValues, 5), LAMBDA(ϑ, ROWS(ϑ()))) = MAP(ROLLINGRANGEλ(dataValues, 5), LAMBDA(ϑ, ISREF(ϑ()))) = MAP(ROLLINGRANGEλ(dataValues, 5), LAMBDA(ϑ, AVERAGE(ϑ()))) = MAP(ROLLINGRANGEλ(dataValues, 5), dataValues, LAMBDA(ϑ,v, MAX(ϑ()))) = LET( rollingMax, MAP(ROLLINGRANGEλ(dataValues, 5), LAMBDA(ϑ, MAX(ϑ()))), FILTER(Data, rollingMax=dataValues) ) The first simply returns #CALC! errors on the worksheet which is the normal response to a Lambda function defined on the grid. The second formulas uses ROWS to show that the ranges are not all the same size, the third shows the returned objects to be range references and not simply arrays of numbers, the forth is a rolling average while the fifth is a rolling MAX. The final formula returns the solution to the problem, being a filtered list. The purpose of this post is to demonstrate that Excel, which started out as a 'simple' spreadsheet program, now contains a very different programming environment that shares a function library and uses the grid for input/output but, other than that, has very little in common with 'normal' spreadsheet practice! A related survey can be found at https://www.linkedin.com/feed/update/urn:li:activity:7285432559902068736/PeterBartholomew1Jan 18, 2025Silver Contributor27Views1like0CommentsIFS or anyother function
Dear Experts, I have a data like below:- So, Column "B" - sfn can go from 0 ~ 1023, and Column-"C", can go from 0~19, Column "G" has 3 values(rnti's) - Now, we have only 2 situations like below in Column"E", where I need the formula:- Either all these 3 rnti's can be Multiplexed in the same sfn.slot So, in below snip all 3 rnti's are FDMed in same sfn.slot - 394.6 and should be continuous, so Column "E" should have fdm-3 But in below instance, in 395.2 we have only 2 rnti's multiplexed(so fdm-2 should be populated) Attached is the spreadsheet. Thanks in Advance, Br, AnupamSolved59Views0likes4CommentsSearching column cells for similar text values and summarising in another column
Hi Experts I hope you can help here. To be honest without Macros not sure this is possible with formulas? I attach an example workbook with my desired results from the data. Basically we have a support ticket system for users and can export the "summary" or "titles" for each ticket. We want to have a formula to analyse the column with all the ticket titles and produce a list of common similar themed topics, and the number of deemed occurrences. Now I know this can be done either looking for an exact text word match but the difficulty comes where I am hoping excel can attempt to categorise similar patterns of words / phrases / text. Any solution via formula or am I expecting too much? Thank you for your help!Solvedmatt0020190Jan 18, 2025Brass Contributor129Views0likes9Comments
Resources
Tags
- Excel42,018 Topics
- Formulas and Functions24,357 Topics
- Macros and VBA6,329 Topics
- office 3655,901 Topics
- Excel on Mac2,606 Topics
- BI & Data Analysis2,315 Topics
- Excel for web1,868 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,602 Topics