Комментарии:
I want that plz make the video on if and or means logical if you will make the video then I will learn new thing
ОтветитьMadam why did you multiply it with rows of a column, pls clarify.
Ответитьhi Leila - Please note that the data range you have referred to in the excel spreadsheet from A5 to A15 and B5 to B15 is static. but the same range in my case is based out of a formula. and the count of the range varies. with new data sourced... I tried to use the exact formula, but my list is as long as 750 to 1250 but this didn't work. I am getting error. and when I tried to check with F9 it shows the same cell address - for instance I have names and their work item number against them. So, if the name is Manish and there are 3 work items... then instead of showing n / n+1/n+2 (assuming all these are listed back-to-back) it is throwing some weird number within the range and giving me the cell reference of the second last cell of the data range for all 3 matches. As I said my data is a result of a formula like UNIQUE (). Thus, please guide. What I need to do differently to get these values. I am using first method.
Ответитьso nice to easyly get multiple result using IF() ....
ОтветитьUse filter function insted
ОтветитьHi Leila, can you do an example to find the Acc and Rej values in the ANSI Table Z1.4 Sampling System, given Normal sampling, Lvl. ii, AQL of 0.25, lot size of 80. I used index and match and sucessfully got the exact intersection but that doesnt satisfy the rule set by the Standard. The standard says to follow the arrow and get the values at the end of it. Pls help. Thanks.
ОтветитьLeila thank you very much for this video, I need to perform something similar. However for my case, I need those apps to be in a drop-down list unlike on different rows.
Would really appreciate your assistance on that.
I wasted 15mins in my life
Ответить@leilagharani
what if the lookup value is not exact match??? any solution for that. and also what if we would like to return or get entire row accumulated with lookup value???
Hi Leila
how do you make values from an array unique without using this dynamic array unique formula
Just found it after watch your INDEX MATCH OFFSET method. Interestingly, INDEX MATCH OFFSET is way easier and simpler
ОтветитьThank you so much for all your support.
Ответитьtextjoin giver error if data is huge.
Pls help
I have easy solution than her
ОтветитьLife saver
ОтветитьI've been looking through old and unhelpful videos to find exactly this solution, This video was extremally helpful and well explained.
ОтветитьHi Leila, I have a spreadsheet with multiple tabs. I have a list tab with several tables on it, I want to know how to create a dropdown list to determine which table to retrieve data from, I then want another dropdown tab to select data from the table specified in the first dropdown list do you have a tutorial that could help me?
ОтветитьI started watching this video with a great amount of interest, bcos I was desparatly looking for one, but ended up feeling Nauseating, very cumbersome..! .....
ОтветитьOk.,
How to calculate the sum of revenue for every individual Division within single cell without using filter command, as filter command available only on excel 2021 or Microsoft 365.
Thank you very helpful
ОтветитьHii
Why should I go through this complex process same thing what you done can also done by simple filters
Won't the Pivot table be useful here? In Pivot table we can get the multiple returns for the Division.
ОтветитьTHANKS LOT OF .
ОтветитьIs this the same with applying filter?
ОтветитьIs possible to use TEXTJOIN with a partial match. I would like to match the first two words in the array. Thank you.
Ответитьhow to return 3 or more text back from sentence? thanks
ОтветитьVery good. It'll probably take a year or two before i understand but it's still fascinating all the same. I know office 2021 has a search function for columns but what about earlier versions? I wanted to rank two column. So rank values in column 1 first but if there are two in a column that are equal then rank on column 2 and finally if the values in column 1 & 2 are equal then rank on column 3.
ОтветитьHi madam,could u help me?consider "x" has two value and The two values should alternate when we type "x".
ОтветитьTake it back, didn't watch through - you are using the error to filter.
ОтветитьSurely you should just use not() so the True become false and vice versa. Then the smallest valus are the ones you want. Still a useful videao- I didn't know about aggregate before... Seen some of you other videoes and am applying in the real worl.
ОтветитьHi Leila, Can I add this TextJoin formula as source for list item in data validation section. I tried many ways but not working. formula inserted as name range and name range inserted as source to list source in data validation using indirect function and without indirect function. But none of them is working. Got an error as "The list must be delimited field or single row or column"
ОтветитьCan i do the same in data validation so that all these will come as selection and user can select
ОтветитьMy excel do not have textjoin formula ?
ОтветитьHi Leila,
Amazing formula for my fashion buying tool. Depending on the selected customer, is shows me all their doors (from a database with all customers and their stores). However some of those doors are not stores but warehouses... so how do I make this formula with an extra condition? Showing only doors labeled as a store (the extra condition in the database). Adding a match to the index formula messes up the formula / or I'm not doing it right. Applying IF just gives me a blank instead of the next store. Thanks!
I am Ajit. Can you help me? I have 1 workbook with many sheets. Among them, there is a sheet with the name Loading through which I have to copy only the data which matches the criteria. According to the criteria data is found through the intersection of the column heading and the value of the rows in the first column. If column heading cells are merged then the data of that many columns intersecting the row has to be taken. That data has to be pasted into a sheet having the name Loading Tree. Now what happens, is that when I have to select the range of cells in the to-sheet where values are to be pasted it shows values from the sheet having the same address in from sheet. Now should I solve it.
ОтветитьHey Leila,
What if I want the results of Method 2 to be listed vertically one answer per cell?
Hi Leila, can you use aggregate with multible criterias? So I need to pull data that matches a category and also that say "yes". How could I do that?
ОтветитьHow can we get the maximum revenue (Amount and Month) both in one go..?
ОтветитьA lot of work for what filter function does
ОтветитьI LOVE YOU LEILA
ОтветитьThanks for this video. Just wondering if you can do the video for google sheet to get the same output? or advise what is the equivalent to AGGRIGATE in google excel sheets?
ОтветитьMan this was the one im looking for!
ОтветитьThanks so much for your video
ОтветитьThank you so much for your explanation. This helps me a lot! Been searching for this past two days and I was stuck before I came here.
ОтветитьFantastic step-by-step, this was so helpful in condensing a huge data sheet I had into a simple to view front page. Thanks so much.
ОтветитьThanks for this. Does anyone have an idea on what versions of Excel Textjoin works? I have 2021, and it doesnt seem to work, but it works on my Work PC with 2019, which feels a bit weird. When I copy the file to my home computer, it briefly says the Dutch version of textjoin (tekst.combineren) and then reverts to =_xlfn.TEXTJOIN, which means unsupported function. Any idea for alternatives in 2021?
ОтветитьExcellent tutorial, been searching for this for long. thank you for your work
ОтветитьHi, I need your expert opinion on filtering a data formula. how to contact you?
ОтветитьHi mam, I would like to suggest that instead of using row function at 'k' of small function, we can use sequence function which automatically expand the array according to the no of values present in the data. 😁
Ответить