Grab the file I used in the video from here 👉 pages.xelplus.com/sumproduct-index-file
@asmrindia
11 ай бұрын
Very Very Very Nice and Useful Thanks Mam
@turin2
Жыл бұрын
As a beginner I got so confused how you explained it. I tried the Bill Szysz solution and it just worked, simply. I want to buy that person a beer thank you.
@InamUllah-xc3ru
8 ай бұрын
She is amazing .❤❤❤ I love Leila gharrani ❤❤.
@nateolson1400
7 жыл бұрын
Leila, I have watched hours of Excel Videos and your Index & Match function videos have been just amazing!! Thank you so much!!!!!
@LeilaGharani
7 жыл бұрын
I'm very happy to hear that Nate. You're very welcome.
@raghuv7114
4 жыл бұрын
Hello Nate, A small change in a comment. All videos are simply amazing. Isn't it?
@michaeldiamond2726
7 жыл бұрын
Hi Leila, Thank you for the video and the explanation. I've been so impressed with your body of work that I think I'm going to create a file for you that you could post to your website that will contain a catalog of all your videos with the links to the actual videos and all the formulas that you're covering in each video. ' I think this would be a great formula catalog. I know when I at work I sometimes need to find a formula to do something like this very quickly and I think it would be handy to ExcelIsFun, ExcelJet, Excel Campus, Mr Excel Message board, Oz, Bill Jelen, etc). I really appreciate all the time each of you spend to bring all these great ideas to us. I imagine setting up the actual filming time and preparation is really time consuming. Thank you also for posting those other articles on your website with the other approaches. I'm always amazed as to how you folks are always able to come up with these eye popping solutions. Keep those videos coming! Do you think a masterfile like that would be a good idea for your videos? How many videos do you have to date, do you know?
@LeilaGharani
7 жыл бұрын
Hi Michael - thank you so much for your comment and the kind words. A master file showing different formula solutions to different problems is a great idea. The challenge there (like the custom formatting :)) would be to make it easily searchable and readable..... I think I have around 60 videos or so....
@michaeldiamond2726
7 жыл бұрын
I think this would be a great way of tracking different ideas. I find that each solution might be more appropriate given the task at hand. I'll send you something a couple of examples so we can go from there. The idea isn't to recreate the video but to create a formula recipe file. One of the caveats of the file is that the user would have to know ahead of time what they are looking are and how to modify the formula to fit the problem. It is a challenge - like the custom formatting file has been. But, I'm getting the hang of it
@LeilaGharani
7 жыл бұрын
That sounds great Michael :)
@michaeldiamond2726
7 жыл бұрын
??????? your message didn't come through
@MySpreadsheetLab
7 жыл бұрын
Aggregate function is so versatile! Thanks for the detailed explanation Leila!
@LeilaGharani
7 жыл бұрын
You're welcome Kevin. Agree - Aggregate is awesome :)
@divyaranjith119
8 ай бұрын
undoubtedly you are a great tutor
@deepakagrawal465
7 жыл бұрын
Whoo.... Who said excel is complex ? It's complex if not taught like Leila does. Superb!!! 👍👍👍👍
@LeilaGharani
7 жыл бұрын
Ohhhh...that's really nice. Thanks Deepak :) Glad the explanations are clear.
@tomneumann3989
2 жыл бұрын
Love your channel and your explanations... SOme how I still have one specific problem that I cannot solve
@InamUllah-xc3ru
8 ай бұрын
Leila gharrani i love you madam ,the way you explain things is amazingly nice. ❤❤❤
@LeilaGharani
8 ай бұрын
Thank you! 😃
@OzduSoleilDATA
7 жыл бұрын
Beautiful! Nice, thorough explanation of a complex solution with all the pieces nicely woven together. That alternative use of IFERROR is smart and I like your explanation of why/when it would be preferable to IFERROR. And thanks for the shout-out!
@LeilaGharani
7 жыл бұрын
Glad you like it Oz du Soleil. You're welcome & thanks for stopping by :)
@rudiansyahsyah9738
2 жыл бұрын
classroom speak extention basic symbol room commonecation software year 2022 / 2023 sys data com master extention in speak english 🏛☏☎🌍🌎🌏
@LeeMeihui-y4v
Жыл бұрын
Hi Leila, Love your videos, so neat and easy to understand! Just want to know, how will I be able to populate by supplier and delivery date to get items, quantity, and unit of measurement (uom) to a standard form in a separate sheet from a master ordering list that contains different supplier, different delivery date, different location, different quantity and different UOM with blank cells throughout the sheet. thanks! and God bless your heart!
@julianizdebski5035
Жыл бұрын
Super Awesome Video - Your Stuff is so good! Amazing helps so much its indescribable.
@ap31as1590
4 жыл бұрын
Hi Leila, The video was very helpful. I need a suggestion. What If we want to return the result to a list. I mean If we want use the result as a dropdown list. Thanks In advance
@bw1llis
2 жыл бұрын
Look up spill functions. Leila's probably done a video on them
@suffairshaik6841
3 жыл бұрын
love you😘 Thanks for the videos
@jermainescott7313
4 жыл бұрын
This video was extremely helpful. Thank you
@LeilaGharani
4 жыл бұрын
Glad to help Jermaine.
@cya0216
5 жыл бұрын
Brilliant Solution !!! Is there any alternative function that could replace function aggregate which is not available in Excel 2007?
@SyedMuzammilMahasanShahi
Жыл бұрын
That is awesome. Thanks for the share.
@LeilaGharani
Жыл бұрын
Thanks for watching!
@F002boy
7 жыл бұрын
Thank you so much for posting this video, Leila! This is all I need to do for my project, and it works perfectly!! THANK YOU!!!!!!!!!
@LeilaGharani
7 жыл бұрын
You're very welcome! I'm happy you asked the question, because Billy Szysz's solution also helped me learn something new :)
@MontyBoosh
4 жыл бұрын
This is exactly what I was looking for!
@barttitulaerexcelbart9400
7 жыл бұрын
Good explanation, step by step. Especially the helper column G for checking/audit. One suggestion, one question: Suggestion: you could select all the apps and then, conditional formatting, highlight double (or unique) values. If the file is large, you can filter by colour any column. Question: How would you generate a list of all unique apps in collumn H? Looking forward to your next video....;) maybe with PQ?
@LeilaGharani
7 жыл бұрын
Hi Bart - I like the conditional formatting idea. Thanks for sharing. To generate a unique list of apps, you could normalize the data set like Kevin does here kzitem.info/news/bejne/1mp_yaWZioJieX4 (at 7:15) and then use remove duplicates feature - or are you looking for a formula solution to this?
@FirestormT
3 жыл бұрын
Hi Leila thanx for your great videos. please help me. ı do use 2013 excel and i need textjoin formula. but because of the version differenties im not able to use. . so what can i use ?
@Sal_A
7 жыл бұрын
Excellent approach - I would of probably done the same except add the helper column formula within the INDEX and AGGREGATE formula. Helper columns are expensive real estate on a spreadsheet, so I generally minimize the number of cells to use, which can sometimes make formula look more complex and harder to debug. The cost of real estate vs. ease of understanding formula is sometimes a sacrifice we must consider.
@LeilaGharani
7 жыл бұрын
Thanks Sal for your comment. Agree with cost of real estate vs. ease of understanding :)
@Dev_Bartwal
7 жыл бұрын
Very important topic Thanks Leila
@LeilaGharani
7 жыл бұрын
You're very welcome Dev :)
@BillSzysz1
7 жыл бұрын
Perfect explanation of difficult problem. and great formula :-)) One small note only - You assume that app can occur no more than one time in each column (1 or 0 in column) but what if it is not true? What if an app can occur more than one time in column? (for example "Blend" - two or more times in "Games" column). I think, that TEXTJOIN can manage such cases as described above. Did you try the formula which i wrote in comment section of the previous video? I do not know if it works (excel 2010) but i think, it should. :-)) Thanks for video :-))
@LeilaGharani
7 жыл бұрын
Hi Bill - Thanks. Yes, very true: If the app is repeated in the same column, they would get the header of that column two times. Your TEXTJOIN formula is genius and it works perfectly well! I've included it in the downloadable workbook (I hope you don't mind) - It would be great to have a video on this. Depending on Excel versions and preferences, all the different videos give people more options to choose from. What do you think? Not sure if you also make Excel videos in English. If not, I would be very happy to create one :)
@F002boy
7 жыл бұрын
Thanks Bill!
@BillSzysz1
7 жыл бұрын
Hi Leila :-) My english is too bad to make a video in this language and additionally i have got the polish version of excel so even if I will do it in english all functions would be in polish. But the most important - i do not think i have such good teaching skills as you, Leila. So if you want to make a video about TEXTJOIN solution I will be honored :-)))
@BillSzysz1
7 жыл бұрын
You are welcome, F002boy :-)
@LeilaGharani
7 жыл бұрын
You're too kind! I've seen your videos - even though I don't understand Polish, I can follow them and can tell they are great! Thank you - I'll make a video on your solution :)
@darla78hk
3 жыл бұрын
I think I love you!!! lol Thank you! You are THE BEST!! ♥️💖💜
@myselfmanik8135
5 жыл бұрын
Wow u are a genius... Nice
@davidfernandes8094
6 жыл бұрын
Awesome video, and perfect explanation.
@amauta5
5 жыл бұрын
I've been binging your vids. Recent sub. I have to say that I have a couple certs on excel and I considered myself a pro, but some of the concepts you cover expose details that I had not considered. Amazing channel. Keep it up!
@rajeshdebnath8673
6 жыл бұрын
Thanks for such good tutorials. My issue solved about offset Dynamic Drop Down by following the process u have shown Madam, I have a query regarding naming PDF file. I used to save Excel invoice in PDF format by using Macro but I want when I save the invoice it must be saved in PDF format with name & the name will be like this CustomerName_InvoiceNo_InvoiceDate . In the invoice CustomerName in A7, InvoiceNo in H6, InvoiceDate in I6 Hope you will help me again Thanks Rajesh Debnath Dimapur, Nagaland
@sterne6
5 жыл бұрын
Dear Leila, I have a problem in my Excel that I want to extract from a big data set a unique values (the smallest and the highest) based on a multiple criteria. I have used an IF function and at the end the MAX function, but the problem is that I get duplicates in indexing values and for example 1 may occur a few times. I am getting crazy, searching already for a very long time a solution. I would be endless thankful for your tip what can I do here. I was trying to combine max and countif, but probably did smth wrong that I didn‘t get the correct results... Thanks a lot in advance. Your biggest Excel fan.
@CK-bt4lb
4 жыл бұрын
Hi Leila, can you do another similar video for return headers. The problem I facing is, in many columns I had similar text,then I would like search certain text to find out the column of the text. For example in this video, in the productivity column, there may have more than one Blend, I gonna to search Blend to find out which column it belongs to. Lastly, I would to see one of your video that can lookup entire table for single text or string and get the column the text belongs to.
@mysteryrgo
3 жыл бұрын
Don't know if you found it yet but to get column headers, watch Leilas video about Index/Sumproduct
@VS-rh8rq
7 жыл бұрын
Understood.great stuff.thank you
@LeilaGharani
7 жыл бұрын
You're welcome Vimal.
@chokoylight2610
2 жыл бұрын
Can you check if you can assist me with my table i am working on? Thank you
@michelletan9767
2 жыл бұрын
How do I modify the formula to return row headers, which are the dates in column A
@michaelconnors9906
6 жыл бұрын
Fun with VBA--setup on Sheet(ComplexLookup_MM_Before) in L4 put APP and M4 put division. This macro will grab all the Apps in Colulmns b, c, and d then paste them into the blank cell into a range starting at L5, and then remove the duplicates. Works like the VBA code I posted our host's Excel Complex Lookup Find Header based on Lookup Criteria in Matrix with INDEX & SUMPRODUCT, but adds the penultimate line ActiveSheet.Range("L5", ActiveSheet.Range("L5").End(xlDown)).RemoveDuplicates Columns:=Array(1), Header:=xlNo to remove the duplicates and give a list of unique values. Sub APP_Macro_Unique() Dim flrange As Range, cell As Range, rng As Range Set flrange = Range("A5", Range("A5").End(xlDown)) Set rng = Range("L5", Range("l5").End(xlDown)) With Sheet4 'Range("K5", Range("K5").End(xlDown)).ClearContents Range("L5", Range("L5").End(xlDown)).ClearContents Range("L5", Range("L5").End(xlDown)).ClearFormats For Each cell In flrange If cell.Offset(0, 1) = "" Then 'SKIP Else cell.Offset(0, 1).Copy Destination:=Range("L" & Rows.Count).End(xlUp).Offset(1) End If Next For Each cell In flrange If cell.Offset(0, 2) = "" Then 'SKIP Else cell.Offset(0, 2).Copy Destination:=Range("L" & Rows.Count).End(xlUp).Offset(1) End If Next For Each cell In flrange If cell.Offset(0, 3) = "" Then 'SKIP Else cell.Offset(0, 3).Copy Destination:=Range("L" & Rows.Count).End(xlUp).Offset(1) End If Next End With Range("L5", Range("L5").End(xlDown)).ClearFormats ActiveSheet.Range("L5", ActiveSheet.Range("L5").End(xlDown)).RemoveDuplicates Columns:=Array(1), Header:=xlNo End Sub
@dhannikielnaldoza7180
4 жыл бұрын
Hi, may i ask how aggregate works in numbers? Thanks
@Gopironaldo
6 жыл бұрын
Seriously was searching for ths .. Thank You..! :)
@LeilaGharani
6 жыл бұрын
Glad you found it :)
@MrKamranhaider0
7 жыл бұрын
Respected experts , I have two data set one contains purchase bill detial vendor wise with date whereas in other sheets payment detail against each vendor with date. I want to make aging schedule from this data set .My idea is to check each invoice and clear it with all payments which have been made before next invoice date,so at the end i get invoices detial date wise which have been either partiality or fully pending so from there i just need to subtract current date to calculate aging days. Please make video turtorial on it if possible. Thanks.
@LeilaGharani
7 жыл бұрын
Hi Kamran - best is to post your specific question (best with sample workbook) either here: www.mrexcel.com/forum or here: www.excelforum.com/ - you can post a link to your question here too and if I get a chance I can take a look, otherwise you will get answers from others...
@MrKamranhaider0
7 жыл бұрын
Leila Gharani ok thanks for your reply ,I will send u excel file ,plz provide your e-mail.
@LeilaGharani
7 жыл бұрын
Hi Kamran - the link to the forums is in my comment above. You can attach your spreadsheet when you create a post. You can share the link to the post in the comment here and if I get a chance I will take a look - if not you get answers from other people.
@redesology01
4 жыл бұрын
Pls advise how to fix the showing division not in reverse order, like in the video the division was showing in reserve order for the app.
@mysteryrgo
3 жыл бұрын
Use SMALL for forward. A2 comes before C3 that comes before B8
@mohamedchakroun4973
7 жыл бұрын
thanks leilaaa i like it
@LeilaGharani
7 жыл бұрын
You're welcome Mohamed.
@hosseinhosseinpoor4845
3 жыл бұрын
thanks thanks
@cyrillepatrickbaldo2187
4 жыл бұрын
Hi Leila, I badly need to know how to do this in Google Sheets. Do you have any advice?
@LeilaGharani
4 жыл бұрын
Sorry, I don't use Sheets.
@cyrillepatrickbaldo2187
4 жыл бұрын
@@LeilaGharani Sadness but it's ok. Thanks for responding to my comment
@InsideMyWall
5 жыл бұрын
How can we extract the unique data by creating a drop down date list??
@bishtgirdhar1
5 жыл бұрын
Ma'am, how to use data validation When cells has some text match, not exact match. Pls reply Girdhar Frm India
@nilams1914
5 жыл бұрын
Hi how i lookuo to header.... I want all headers in my front of data.... Example blend next to productivity game _>utility
@MUAISC
5 жыл бұрын
Thank you madam
@bharatdarji1442
4 жыл бұрын
Dear Laila, I like to watch and understand what you show here but it would be more useful if you put subtitles in video. Thanks
@LeilaGharani
4 жыл бұрын
There are English captions available. You just need to turn them on by clicking the CC button in the bottom right hand corner.
@michaelconnors9906
6 жыл бұрын
Thanks to Scott Craner at StackOverflow for help with the .Cells argument. VBA is for Complex_MM_Before. Load it in a Module folder. No set up required. The macro uses the H4 through K4 headings. This VBA code replaces the formula =IF(COLUMNS($I2:J$4)>$G3,"",INDEX($B$4:$D$4,,AGGREGATE(14,4,($B$5:$D$45=$H3)*(COLUMN($B$4:$D$4)-COLUMN($A$4)),COLUMNS($I2:J$4)))) Sub Division_2() Dim rng As Range, cell As Range, rng2 As Range, rng3 As Range, rng4 As Range Dim ThisCell1 As Range, ThisCell2 As Range, ThisCell3 As Range Dim i As Integer, ActiveCell As Range Set rng2 = Range("B5:B" & Cells(Rows.Count, "b").End(xlUp).Row) Set rng3 = Range("c5:c" & Cells(Rows.Count, "c").End(xlUp).Row) Set rng4 = Range("d5:d" & Cells(Rows.Count, "d").End(xlUp).Row) With Sheet4 Range("I5:K" & Cells(Rows.Count, "H").End(xlUp).Row).ClearContents For Each ThisCell1 In Range("H5:H" & Cells(Rows.Count, "H").End(xlUp).Row) 'This is the range of cells to check For Each ThisCell2 In Range("B5:B" & Cells(Rows.Count, "B").End(xlUp).Row) 'This is the range of cells to compare If ThisCell1.Value = ThisCell2.Value Then Cells(ThisCell1.Row, Columns.Count).End(xlToLeft).Offset(, 1) = Range("B4") Exit For End If Next ThisCell2 Next ThisCell1 For Each ThisCell1 In Range("H5:H" & Cells(Rows.Count, "H").End(xlUp).Row) 'This is the range of cells to check For Each ThisCell3 In Range("C5:C" & Cells(Rows.Count, "C").End(xlUp).Row) 'This is the range of cells to compare If ThisCell1.Value = ThisCell3.Value Then Cells(ThisCell1.Row, Columns.Count).End(xlToLeft).Offset(, 1) = Range("c4") Exit For End If Next ThisCell3 Next ThisCell1 For Each ThisCell1 In Range("H5:H" & Cells(Rows.Count, "H").End(xlUp).Row) 'This is the range of cells to check For Each ThisCell3 In Range("D5:D" & Cells(Rows.Count, "d").End(xlUp).Row) 'This is the range of cells to compare If ThisCell1.Value = ThisCell3.Value Then Cells(ThisCell1.Row, Columns.Count).End(xlToLeft).Offset(, 1) = Range("d4") Exit For End If Next ThisCell3 Next ThisCell1 End With End Sub
@tonielam8134
7 жыл бұрын
May I ask what software you used to do video record your screen? thanks in advance.
@LeilaGharani
7 жыл бұрын
I use Camtasia.
@tonielam8134
7 жыл бұрын
Thank you for your feed back much appreciated.
@abdanomer
7 жыл бұрын
Great 👍🏻
@LeilaGharani
7 жыл бұрын
:) Thanks.
@michaelconnors9906
6 жыл бұрын
Instead of using Aggregate, 14 which is the large argument, I would use Aggregate, 15, the small argument. This isn't an aesthetic choice, it's a practical choice. People expect information to be delivered top to bottom and left to right. Using the Large argument delivers the information bottom to top and right to left. Look at the first example, the result in C9 shows first and then result in B8. This is the opposite way that people actually look for information. The formula I would use in I5 is =IF(COLUMNS($I$4:I4)
@LeilaGharani
6 жыл бұрын
Good suggestion! Thanks for your feedback.
@pouyamir63
4 жыл бұрын
Hi Leila and Thank you for your awesome solutions I happen to have a much greater data set as an analysis matrix with non-unique values where I need to return Header and Left column reference of Top nth largest values Lets say for test run a much smaller version of situation such as below A B C D E F G H I ----------------------------------------------------------------------------------- 1| a b c counter/Order Largest Column Row 2| d 18 15 3 1 25 b f 3| e 17 15 25 2 25 c e 4| f 4 25 18 3 18 #NUM! #NUM! 4 18 #NUM! #NUM! 5 17 #NUM! #NUM! Say for cell H2 I tried using less complicated version of your multi-result complex lookup but AFTER 3RD ITTERATION IT SOMEHOW LOOSES COUNT IN AGGREGATE AND RETURNS "#NUM!" a 3x3 matrix Name= Mat_1 ---> ($B$1:$D$4) say cell H2 =INDEX(Mat_1[#Headers],AGGREGATE(15,3,((Mat_1:Mat_1=LARGE(Mat_1:Mat_1,F2))/(Mat_1:Mat_1=LARGE(Mat_1:Mat_1,F2)))*(COLUMN(Mat_1[#Headers])-COLUMN(Mat_1 [[#Headers],[a]])+1),F2)) would you be able to help me to get around this Thanks again
@crazyguycrazy4099
7 жыл бұрын
have some
@nilams1914
5 жыл бұрын
Plz let me knw formulas
@samkab677
6 жыл бұрын
Why large function not small function?
@raghuv7114
Жыл бұрын
To get value from smallest to largest of final array result
@republikadugave420
2 жыл бұрын
If someone sent me this data set i would call them and ask what are they smoking 😁😁😁...
@Fabiosoluzionipc
2 жыл бұрын
Sub title italiano?
@umranshahid
4 жыл бұрын
Dear Liela, thank you very much for providing such a tremendous support working in excel. I am facing some issue and need your support. Can you provide me you email id to send you the file and issue I am facing.
Пікірлер: 106